Select XML nodes as rows
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 XMLDECLARE @x XMLSELECT @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) -- RESULTSSELECT [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 1Joel | Street 2 | City 2 | State 2 | Zip Code 2Kim | Street 3 | City 3 | State 3 | Zip Code 3
Here's how I do it generically:
I shred the source XML via a call such as
DECLARE @xmlEntityList xmlSET @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 xmlSET @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)ASBEGIN -- 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
In case this is useful to anyone else out there looking for a "generic" solution, I created a CLR procedure that can take an Xml fragment as above and "shred" it into a tabular resultset, without you providing any additional information about the names or types of the columns, or customizing your call in any way for the given Xml fragment:
http://architectshack.com/ClrXmlShredder.ashx
There are of course some restrictions (the xml must be "tabular" in nature like this sample, the first row needs to contain all the elements/columns that will be supported, etc) - but I do hope it's a few steps ahead of what's available built-in.