Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Query Help using XML Expand / Collapse
Author
Message
Posted Friday, March 15, 2013 10:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 10:05 AM
Points: 177, Visits: 355
Looking for SQL Query help on following.

Have a key value pair, as below.


declare @t table (id int, [key] 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.
Post #1431619
Posted Friday, March 15, 2013 10:38 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:40 PM
Points: 1,683, Visits: 19,609

SELECT a.id,
(SELECT b.[key] 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;



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1431634
Posted Friday, March 15, 2013 11:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 10:05 AM
Points: 177, Visits: 355
Thank you very much. The solution worked, and this was exactly what I was looking for..!!
Post #1431650
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse