Select values from XML field in SQL Server 2008

Question

Just looking at my XML field, my rows look like this:

<person><firstName>Jon</firstName><lastName>Johnson</lastName></person>
<person><firstName>Kathy</firstName><lastName>Carter</lastName></person>
<person><firstName>Bob</firstName><lastName>Burns</lastName></person>

Note that these are three rows in my table.

I'd like to return a SQL result as a table as in

Jon  | Johnson
Kathy| Carter
Bob  | Burns

What query will accomplish this?

Solution 1

Given that the XML field is named 'xmlField'...

SELECT 
[xmlField].value('(/person//firstName/node())[1]', 'nvarchar(max)') as FirstName,
[xmlField].value('(/person//lastName/node())[1]', 'nvarchar(max)') as LastName
FROM [myTable]

Solution 2

Considering that XML data comes from a table 'table' and is stored in a column 'field': use the XML methods, extract values with xml.value(), project nodes with xml.nodes(), use CROSS APPLY to join:

SELECT 
    p.value('(./firstName)[1]', 'VARCHAR(8000)') AS firstName,
    p.value('(./lastName)[1]', 'VARCHAR(8000)') AS lastName
FROM table 
    CROSS APPLY field.nodes('/person') t(p)

You can ditch the nodes() and cross apply if each field contains exactly one element 'person'. If the XML is a variable you select FROM @variable.nodes(...) and you don't need the cross apply.

Solution 3

This post was helpful to solve my problem which has a little different XML format... my XML contains a list of keys like the following example and I store the XML in the SourceKeys column in a table named DeleteBatch:

<k>1</k>
<k>2</k>
<k>3</k>

Create the table and populate it with some data:

CREATE TABLE dbo.DeleteBatch (
    ExecutionKey INT PRIMARY KEY,
    SourceKeys XML)

INSERT INTO dbo.DeleteBatch ( ExecutionKey, SourceKeys )
SELECT 1, 
    (CAST('<k>1</k><k>2</k><k>3</k>' AS XML))

INSERT INTO dbo.DeleteBatch ( ExecutionKey, SourceKeys )
SELECT 2, 
    (CAST('<k>100</k><k>101</k>' AS XML))

Here's my SQL to select the keys from the XML:

SELECT ExecutionKey, p.value('.', 'int') AS [Key]
FROM dbo.DeleteBatch
    CROSS APPLY SourceKeys.nodes('/k') t(p)

Here's the query results...

ExecutionKey    Key
1   1
1   2
1   3
2   100
2   101

Solution 4

This may answer your question:

select cast(xmlField as xml) xmlField into tmp from (
select '<person><firstName>Jon</firstName><lastName>Johnson</lastName></person>' xmlField
union select '<person><firstName>Kathy</firstName><lastName>Carter</lastName></person>'
union select '<person><firstName>Bob</firstName><lastName>Burns</lastName></person>'
) tb

SELECT
    xmlField.value('(person/firstName)[1]', 'nvarchar(max)') as FirstName
    ,xmlField.value('(person/lastName)[1]', 'nvarchar(max)') as LastName
FROM tmp

drop table tmp

Solution 5

Blimey. This was a really useful thread to discover.

I still found some of these suggestions confusing. Whenever I used value with [1] in the string, it would only retrieved the first value. And some suggestions recommended using cross apply which (in my tests) just brought back far too much data.

So, here's my simple example of how you'd create an xml object, then read out its values into a table.

DECLARE @str nvarchar(2000)

SET @str = ''
SET @str = @str + '<users>'
SET @str = @str + '  <user>'
SET @str = @str + '     <firstName>Mike</firstName>'
SET @str = @str + '     <lastName>Gledhill</lastName>'
SET @str = @str + '     <age>31</age>'
SET @str = @str + '  </user>'
SET @str = @str + '  <user>'
SET @str = @str + '     <firstName>Mark</firstName>'
SET @str = @str + '     <lastName>Stevens</lastName>'
SET @str = @str + '     <age>42</age>'
SET @str = @str + '  </user>'
SET @str = @str + '  <user>'
SET @str = @str + '     <firstName>Sarah</firstName>'
SET @str = @str + '     <lastName>Brown</lastName>'
SET @str = @str + '     <age>23</age>'
SET @str = @str + '  </user>'
SET @str = @str + '</users>'

DECLARE @xml xml
SELECT @xml = CAST(CAST(@str AS VARBINARY(MAX)) AS XML) 

--  Iterate through each of the "users\user" records in our XML
SELECT 
    x.Rec.query('./firstName').value('.', 'nvarchar(2000)') AS 'FirstName',
    x.Rec.query('./lastName').value('.', 'nvarchar(2000)') AS 'LastName',
    x.Rec.query('./age').value('.', 'int') AS 'Age'
FROM @xml.nodes('/users/user') as x(Rec)

And here's the output:

It's bizarre syntax, but with a decent example, it's easy enough to add to your own SQL Server functions.

Speaking of which, here's the correct answer to this question.

Assuming your have your xml data in an @xml variable of type xml (as demonstrated in my example above), here's how you would return the three rows of data from the xml quoted in the question:

SELECT 
    x.Rec.query('./firstName').value('.', 'nvarchar(2000)') AS 'FirstName',
    x.Rec.query('./lastName').value('.', 'nvarchar(2000)') AS 'LastName'
FROM @xml.nodes('/person') as x(Rec)

View more solutions

Comments

[Remus Rusanu]

You must use .nodes() and cross apply if xmlField contains more than one <person> elements.

[redcalx]

I wonder how efficient this method is and whether there's a better way. The CROSS APPLY combiend with XPath results seems like it might result in quite a resource hungry query.

[Remus Rusanu]

@thelocster: this is no different from ordinary data access. Techniques for improving XML performance are well documented. msdn.microsoft.com/en-us/library/ms345118%28SQL.90%29.aspx

[Tom Wayson]

keep in mind that if your XML has xmlns namespaces defined, you'll need to define those in the XQuery (XPath) expression above. See stackoverflow.com/a/1302150/656010 for an example.

[dan richardson]

Slightly different to what I was needing, but this was a perfect solution to a problem I was having which was multiple rows with an XML column - I wanted to loop through rows and pull out the data fields from within the XML column and put them in an insert statement. So 5 rows, each for 3 columns of data in the XML field = 15 inserts, perfect.

[RASMiranda]

SQL Server 2008 R2 Express, returned me this error with your solution: The XQuery syntax '/function()' is not supported.; On the other hand @Remus Rusanu seems to do it :)

[Mike Gledhill]

Bizarre. This has been voted up 102 times, but this answer only returns data from the first XML record. And it refers to some [myTable] table... where did that come from ?!

[JonathanPeel]

I have tried this so many times and never had it working. My XML is <BAM><Type>Electrical</Type><BaIds><a:int>7330</a:int></BaId‌​s></BAM>, my select is select e.MessageData.value('(/BAM/Type)[1]', 'varchar(100)'). I have also tried select e.MessageData.value('(/BAM/Type/node())[1]', 'varchar(100)'), and '(//Type/node())[1]', '(./Type)[1]', and every other combination I can think of. All I ever get is NULL.

[fbehrens]

If the xml has given a namespace xmlns="http://bayerbbs.com/AMUNI" you have to map it to deafutl namespace WITH XMLNAMESPACES(DEFAULT 'http://bayerbbs.com/AMUNI') SELECT ... see stackoverflow.com/questions/5467387/… `

[Paul]

@MikeGledhill it returns values from multiple XML records fine for me. Also the only name to the table the OP gives is "my table" :)

[Davos]

I don't see how this is the correct answer. The OP is asking for querying a column from a table which is of type XML, and in that case you have to either use [1] , the index ordinal to force it to return 1 row, or you have to cross apply the column with nodes() to get a structure that can have xpath run against it. Your code doesn't translate to that scenario without a lot of modifications. You're using a variable, not a table column. You're also overusing query() function which returns xml. e.g. you could have just x.Rec.value('(./firstName)[1]', 'nvarchar(2000)') AS FirstName

[Davos]

@MikeGledhill The original question says "these are three rows in my table" so [myTable] refers to whatever that table is called. If you are querying some single XML variable,then [1] will only give you one row. The syntax is not straight forward, but for a table [myTable] that has a column [xmlField] of type xml, this is one of the two correct answers, the other being Remus Rusanu's.

[fededim]

Best and simplest solution

[aruno]

As @Tom said the namespace is very important. Even if you do SELECT TOP 1 without the correct namespace it'll take FOREVER!