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') |