Select XML nodes as rows
60,982
Solution 1
Here is your solution:
/* TEST TABLE */
DECLARE @PEOPLE AS TABLE ([Name] VARCHAR(20), [Address] XML )
INSERT INTO @PEOPLE SELECT
'Joel',
'<address>
<street>Street 1</street>
<city>City 1</city>
<state>State 1</state>
<zipcode>Zip Code 1</zipcode>
</address>
<address>
<street>Street 2</street>
<city>City 2</city>
<state>State 2</state>
<zipcode>Zip Code 2</zipcode>
</address>'
UNION ALL SELECT
'Kim',
'<address>
<street>Street 3</street>
<city>City 3</city>
<state>State 3</state>
<zipcode>Zip Code 3</zipcode>
</address>'
SELECT * FROM @PEOPLE
-- BUILD XML
DECLARE @x XML
SELECT @x =
( SELECT
[Name]
, [Address].query('
for $a in //address
return <address
street="{$a/street}"
city="{$a/city}"
state="{$a/state}"
zipcode="{$a/zipcode}"
/>
')
FROM @PEOPLE AS people
FOR XML AUTO
)
-- RESULTS
SELECT [Name] = T.Item.value('../@Name', 'varchar(20)'),
street = T.Item.value('@street' , 'varchar(20)'),
city = T.Item.value('@city' , 'varchar(20)'),
state = T.Item.value('@state' , 'varchar(20)'),
zipcode = T.Item.value('@zipcode', 'varchar(20)')
FROM @x.nodes('//people/address') AS T(Item)
/* OUTPUT*/
Name | street | city | state | zipcode
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Joel | Street 1 | City 1 | State 1 | Zip Code 1
Joel | Street 2 | City 2 | State 2 | Zip Code 2
Kim | Street 3 | City 3 | State 3 | Zip Code 3
Solution 2
Here's how I do it generically:
I shred the source XML via a call such as
DECLARE @xmlEntityList xml
SET @xmlEntityList =
'
<ArbitrarilyNamedXmlListElement>
<ArbitrarilyNamedXmlItemElement><SomeVeryImportantInteger>1</SomeVeryImportantInteger></ArbitrarilyNamedXmlItemElement>
<ArbitrarilyNamedXmlItemElement><SomeVeryImportantInteger>2</SomeVeryImportantInteger></ArbitrarilyNamedXmlItemElement>
<ArbitrarilyNamedXmlItemElement><SomeVeryImportantInteger>3</SomeVeryImportantInteger></ArbitrarilyNamedXmlItemElement>
</ArbitrarilyNamedXmlListElement>
'
DECLARE @tblEntityList TABLE(
SomeVeryImportantInteger int
)
INSERT @tblEntityList(SomeVeryImportantInteger)
SELECT
XmlItem.query('//SomeVeryImportantInteger[1]').value('.','int') as SomeVeryImportantInteger
FROM
[dbo].[tvfShredGetOneColumnedTableOfXmlItems] (@xmlEntityList)
by utilizing the scalar-valued function
/* Example Inputs */
/*
DECLARE @xmlListFormat xml
SET @xmlListFormat =
'
<ArbitrarilyNamedXmlListElement>
<ArbitrarilyNamedXmlItemElement>004421UB7</ArbitrarilyNamedXmlItemElement>
<ArbitrarilyNamedXmlItemElement>59020UH24</ArbitrarilyNamedXmlItemElement>
<ArbitrarilyNamedXmlItemElement>542514NA8</ArbitrarilyNamedXmlItemElement>
</ArbitrarilyNamedXmlListElement>
'
declare @tblResults TABLE
(
XmlItem xml
)
*/
-- =============================================
-- Author: 6eorge Jetson
-- Create date: 01/02/3003
-- Description: Shreds a list of XML items conforming to
-- the expected generic @xmlListFormat
-- =============================================
CREATE FUNCTION [dbo].[tvfShredGetOneColumnedTableOfXmlItems]
(
-- Add the parameters for the function here
@xmlListFormat xml
)
RETURNS
@tblResults TABLE
(
-- Add the column definitions for the TABLE variable here
XmlItem xml
)
AS
BEGIN
-- Fill the table variable with the rows for your result set
INSERT @tblResults
SELECT
tblShredded.colXmlItem.query('.') as XmlItem
FROM
@xmlListFormat.nodes('/child::*/child::*') as tblShredded(colXmlItem)
RETURN
END
--SELECT * FROM @tblResults
Author by
Admin
Updated on July 09, 2022Comments
-
Admin almost 2 years
I am selecting from a table that has an XML column using T-SQL. I would like to select a certain type of node and have a row created for each one.
For instance, suppose I am selecting from a people table. This table has an XML column for addresses. The XML is formated similar to the following:
<address> <street>Street 1</street> <city>City 1</city> <state>State 1</state> <zipcode>Zip Code 1</zipcode> </address> <address> <street>Street 2</street> <city>City 2</city> <state>State 2</state> <zipcode>Zip Code 2</zipcode> </address>
How can I get results like this:
Name City State
Joe Baker Seattle WA
Joe Baker Tacoma WA
Fred Jones Vancouver BC