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?
This question is related to
sql-server
xml
xpath
The answer is
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)
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:
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)
/* 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
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
Source: Stackoverflow.com