Wednesday, June 11, 2014

FOR XML PATH Pivot Columns as Key Value Pairs defeating the "must not come after a non-attribute-centric sibling in XML hierarchy in FOR XML PATH" error

Yesterday I had been working in SQL to create an xml document from a query I needed to make key value pairs out of my column list using one attribute as the key and the element content as the value. Pretty common pattern in XML right? I am aware of and have used the PIVOT key word however using it to describe what I am doing here seemed like the best choice of overloaded term. After several of failed attempts and some googling I threw the following query below into the management studio and finally got the output I had been looking for. The solution? simply add a '' between your elements telling sql server that you have a whitespace value between the two elements.

SELECT TOP 1 v.VampireName AS [to/@name]
,v.VampireEmail AS [to/text()]
,(
SELECT 'ID' AS [var/@name]
,t.Id AS [var/text()]
,''
,'Stuff' AS [var/@name]
,t.Campers AS [var/text()]
,''
,'NAME' AS [var/@name]
,v.VampireName AS [var/text()]
FOR XML PATH('global_merge_vars')
,TYPE
)
FROM dbo.Tinkerbell t
LEFT JOIN dbo.Puppies p ON p.Id = t.PuppyID
LEFT JOIN dbo.Grover g ON g.Id = p.GroverID
LEFT JOIN dbo.Dalmations d ON d.Id = p.DalmationID
LEFT JOIN dbo.Vampires v ON v.Id = t.Vampire
FOR XML PATH('message')