Find Customers Who Bought "A" and "B" But Not "C" (SQL Spackle) By Jeff Moden

Find Customers Who Bought "A" and "B" But Not "C" (SQL Spackle)

By Jeff Moden,

http://www.sqlservercentral.com/articles/T-SQL/88244/

Basics of XML and SQL Server, Part 3: Transform and Shred XML in SSIS By Stan Kulp, 2012/03/21

Basics of XML and SQL Server, Part 3: Transform and Shred XML in SSIS

By Stan Kulp, 2012/03/21

http://www.sqlservercentral.com/articles/XML/87637/

Creating a Map Report in SSRS - SQL Server 2008 R2 By Arshad Ali

Creating a Map Report in SSRS - SQL Server 2008 R2

By Arshad Ali

http://www.databasejournal.com/features/mssql/creating-map-report-in-ssrs-sql-server-2008-r2.html

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