Select values from XML field in SQL Server 2008

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?

497970 次浏览
小开
最佳答案

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]
小开

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.

小开

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
小开

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
小开 
SELECT
cast(xmlField as xml).value('(/person//firstName/node())[1]', 'nvarchar(max)') as FirstName,
cast(xmlField as xml).value('(/person//lastName/node())[1]', 'nvarchar(max)') as LastName
FROM [myTable]
小开

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:

enter image description here

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)

enter image description here

小开

If you are able to wrap your XML in a root element - say then the following is your solution:

DECLARE @PersonsXml XML = '<persons><person><firstName>Jon</firstName><lastName>Johnson</lastName></person>
<person><firstName>Kathy</firstName><lastName>Carter</lastName></person>
<person><firstName>Bob</firstName><lastName>Burns</lastName></person></persons>'


SELECT  b.value('(./firstName/text())[1]','nvarchar(max)') as FirstName, b.value('(./lastName/text())[1]','nvarchar(max)') as LastName
FROM @PersonsXml.nodes('/persons/person') AS a(b)

enter image description here

小开

/* This example uses an XML variable with a schema */

IF EXISTS (SELECT * FROM sys.xml_schema_collections
WHERE name = 'OrderingAfternoonTea')
BEGIN
DROP XML SCHEMA COLLECTION dbo.OrderingAfternoonTea
END
GO


CREATE XML SCHEMA COLLECTION dbo.OrderingAfternoonTea AS
N'<?xml version="1.0" encoding="UTF-16" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://Tfor2.com/schemas/actions/orderAfternoonTea"
xmlns="http://Tfor2.com/schemas/actions/orderAfternoonTea"
xmlns:TFor2="http://Tfor2.com/schemas/actions/orderAfternoonTea"
elementFormDefault="qualified"
version="0.10"
>
<xsd:complexType name="AfternoonTeaOrderType">
<xsd:sequence>
<xsd:element name="potsOfTea" type="xsd:int"/>
<xsd:element name="cakes" type="xsd:int"/>
<xsd:element name="fruitedSconesWithCream" type="xsd:int"/>
<xsd:element name="jams" type="xsd:string"/>
</xsd:sequence>
<xsd:attribute name="schemaVersion" type="xsd:long" use="required"/>
</xsd:complexType>


<xsd:element name="afternoonTeaOrder"
type="TFor2:AfternoonTeaOrderType"/>


</xsd:schema>' ;
GO


DECLARE @potsOfTea int;
DECLARE @cakes int;
DECLARE @fruitedSconesWithCream int;
DECLARE @jams nvarchar(128);


DECLARE @RequestMsg NVARCHAR(2048);
DECLARE @RequestXml XML(dbo.OrderingAfternoonTea);


set @potsOfTea = 5;
set @cakes = 7;
set @fruitedSconesWithCream = 25;
set @jams = N'medlar jelly, quince and mulberry';


SELECT @RequestMsg = N'<?xml version="1.0" encoding="utf-16" ?>
<TFor2:afternoonTeaOrder schemaVersion="10"
xmlns:TFor2="http://Tfor2.com/schemas/actions/orderAfternoonTea">
<TFor2:potsOfTea>' + CAST(@potsOfTea as NVARCHAR(20))
+ '</TFor2:potsOfTea>
<TFor2:cakes>' + CAST(@cakes as NVARCHAR(20)) + '</TFor2:cakes>
<TFor2:fruitedSconesWithCream>'
+ CAST(@fruitedSconesWithCream as NVARCHAR(20))
+ '</TFor2:fruitedSconesWithCream>
<TFor2:jams>' + @jams + '</TFor2:jams>
</TFor2:afternoonTeaOrder>';


SELECT @RequestXml  = CAST(CAST(@RequestMsg AS VARBINARY(MAX)) AS XML) ;


with xmlnamespaces('http://Tfor2.com/schemas/actions/orderAfternoonTea'
as tea)
select
cast( x.Rec.value('.[1]/@schemaVersion','nvarchar(20)') as bigint )
as schemaVersion,
cast( x.Rec.query('./tea:potsOfTea')
.value('.','nvarchar(20)') as bigint ) as potsOfTea,
cast( x.Rec.query('./tea:cakes')
.value('.','nvarchar(20)') as bigint )  as cakes,
cast( x.Rec.query('./tea:fruitedSconesWithCream')
.value('.','nvarchar(20)') as bigint )
as fruitedSconesWithCream,
x.Rec.query('./tea:jams').value('.','nvarchar(50)')  as jams
from @RequestXml.nodes('/tea:afternoonTeaOrder')  as x(Rec);


select @RequestXml.query('/*')
小开

MSSQL uses regular XPath rules as follows:

  • nodename Selects all nodes with the name "nodename"
  • / Selects from the root node
  • // Selects nodes in the document from the current node that match the selection no matter where they are
  • . Selects the current node
  • .. Selects the parent of the current node
  • @ Selects attributes

W3Schools

小开

you can use this sample from here:

DECLARE @myDoc XML
DECLARE @ProdID INT
SET @myDoc = '<Root>
<ProductDescription ProductID="1" ProductName="Road Bike">
<Features>
<Warranty>1 year parts and labor</Warranty>
<Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
</Features>
</ProductDescription>
</Root>'
  

SET @ProdID =  @myDoc.value('(/Root/ProductDescription/@ProductID)[1]', 'int' )
SELECT @ProdID
小开

In my case, xml shredding works good: https://dzone.com/articles/dude-lets-xml-shred

XML:

DECLARE @x XML
SET @x = '
<Library>
<Books>
<Book Type="Paperback">
<Author>Robert Jordan</Author>
<Id>26</Id>
<PublicationDate>01/15/1990</PublicationDate>
<Series>
<Name>The Wheel of Time</Name>
<Number>1</Number>
</Series>
<Title Chapters="53">The Eye of the World</Title>
</Book>
<Book Type="Hardback">
<Author>Robert Jordan</Author>
<Id>87</Id>
<PublicationDate>09/15/1992</PublicationDate>
<Series>
<Name>The Wheel of Time</Name>
<Number>4</Number>
</Series>
<Title Chapters="58">The Shadow Rising</Title>
</Book>
<Book Type="eBook">
<Author>Robert Jordan</Author>
<Id>43</Id>
<PublicationDate>05/15/1996</PublicationDate>
<Series>
<Name>The Wheel of Time</Name>
<Number>7</Number>
</Series>
<Title Chapters="41">A Crown of Swords</Title>
</Book>
</Books>
<Id>51</Id>
<Name>We Have Books... Read Them or Else!</Name>
</Library>'

Query:

SELECT Books.Book.value('(./Id)[1]', 'INT') AS Id,
Books.Book.value('(./Title)[1]', 'VARCHAR(MAX)') AS Title,
Books.Book.value('(./Author)[1]', 'VARCHAR(MAX)') AS Author,
Books.Book.value('(./PublicationDate)[1]', 'DATE') AS PublicationDate,
Books.Book.value('(./Series/Name)[1]', 'VARCHAR(MAX)') AS SeriesName,
Books.Book.value('(./Series/Number)[1]', 'VARCHAR(MAX)') AS SeriesNumber,
Books.Book.value('@Type', 'VARCHAR(MAX)') AS BookType,
Books.Book.value('(./Title/@Chapters)[1]', 'INT') AS Chapters
FROM @x.nodes('(/Library/Books/Book)') AS Books(Book)

Output

小开

you can write very easily, Please find the following details

DECLARE @xmlData XML='
<person><firstName>Jon</firstName><lastName>Johnson</lastName></person>
<person><firstName>Kathy</firstName><lastName>Carter</lastName></person>
<person><firstName>Bob</firstName><lastName>Burns</lastName></person>'


SELECT
Col.value('(firstName)[1]','NVARCHAR(50)') AS [FirstName],
Col.value('(lastName)[1]','NVARCHAR(50)') AS [LastName]
FROM @xmlData.nodes('person') AS tbl(Col)

Click to see the result


提交答案