Flattening XML Data in SQL Server by Simon J Ince

Simon Ince's Blog

http://blogs.msdn.com/b/simonince/archive/2009/04/24/flattening-xml-data-in-sql-server.aspx?PageIndex=2#comments


In applying to my case here:


SELECT
pay.value('(RecipientId/text())[1]', 'int') as RecipientID,
pay.value('(BenefitAmount/text())[1]', 'money') as BenefitAmount,
per.value('(RecipientFirstName/text())[1]', 'varchar(50)') as FirstName,
per.value('(RecipientLastName/text())[1]', 'varchar(50)') as FirstName
FROM
XmlSourceTable CROSS APPLY
xmlColumn.nodes('//PaymentCalcNotice') AS R(pay) cross apply
xmlColumn.nodes('//RecipientInfo') as P(per)

• XmlSourceTable is the table containing xml columns
• xmlColumn is an xml column in the table XmlSourceTable
• PaymentCalcNotice and RecipientInfo are two nodes in the xml data (xmlColumn, showing above)
• RecipientId and BenefitAmount are two elements under PaymentCalcNoticenode node
• RecipientFirstName and RecipientLastName are two elements under RecipientInfo node

Later on 5/22/2012:  look at this too
http://www.sqlservercentral.com/blogs/rocks/2012/05/16/shredding-a-simple-xml-structure-to-a-single-row-flat-table/

SQL Server Rocks!

 

--Declare variables and populate with example data
DECLARE @Cols VARCHAR(MAX)
DECLARE @Xml AS XML = CAST('
<book id="bk101">
 <author>Gambardella, Matthew</author>
 <title>XML Developer''s Guide</title>
 <genre>Computer</genre>
 <price>44.95</price>
 <publish_date>2000-10-01</publish_date>
 <description>An in-depth look at creating applications with XML.</description>
</book>' AS XML)
DECLARE @SQL NVARCHAR(MAX)

--Get a list of the columns from the shreded xml document using XMLTable
SELECT @Cols = ISNULL(@Cols, '') + QUOTENAME(XPath) + ','
FROM dbo.XMLTable(@Xml)
WHERE Value IS NOT NULL

--trim the trailing comma
SET @Cols = LEFT(@Cols, LEN(@Cols) -1)

--Generate dynamic SQL statement to pivot the rows from XMLTable function in a single row table
SELECT @SQL = 'SELECT ' + @Cols + '
FROM 
(
 SELECT XPath, Value 
 FROM dbo.XMLTable(@Xml)
 WHERE Value IS NOT NULL
) x
PIVOT
(MAX(Value) FOR xpath IN (' + @Cols +')) y'

--Execute the statement
EXEC sys.sp_executesql @SQL, N'@Xml XML', @Xml = @Xml