SQL Query Help using XML
-
Looking for SQL Query help on following.
Have a key value pair, as below.
declare @t table (id int, varchar(10), value varchar(10))
insert @t values (1, 'ColA', 'ABC123')
insert @t values (1, 'colB', 'DEF456')
insert @t values (2, 'colA', '2ColABC')
insert @t values (2, 'colC', '2ColDef')
insert @t values (2, 'colE', '2Colxyz')
Need to bundle the Key-value combination into XML for each ID row. The expected results are
1, '<AdditionalCols><Col ColName="colA">ABC123</Col><Col ColName="colB">DEF456</Col></AdditionalCols>'
2, '<AdditionalCols><Col ColName="colA">2ColABC</Col><Col ColName="colC">2ColDef</Col><Col ColName="colE">2Colxyz</Col></AdditionalCols>'
Tried few combinations using FOR XML, but could not get the desired result at each row level.
-
SELECT a.id,
(SELECT b. AS "@ColName",
b.value AS "text()"
FROM @t b
WHERE b.id=a.id
FOR XML PATH('Col'),ROOT('AdditionalCols'),TYPE) AS keyvalues
FROM @t a
GROUP BY a.id
ORDER BY a.id;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thank you very much. The solution worked, and this was exactly what I was looking for..!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply