RE: XML Question (?) – SQLServerCentral

One Orange Chip

Points: 26944

May 1, 2013 at 2:28 am

Not sure of your table structure, but this give the correct results

DECLARE @tbl TABLE(Code1 VARCHAR(10), Code2 VARCHAR(10), Field1 INT, Field2 INT, Field3 INT)

INSERT INTO @tbl(Code1,Code2,Field1,Field2,Field3)

VALUES ('Rec1_Code1','Rec1_Code2',11,22,33),

('Rec2_Code1','Rec2_Code2',1111,2222,3333);

SELECT

Code1 AS "@instanceCode1",

Code2 AS "@instanceCode2",

(SELECT

(SELECT 'Field1' AS "@name", Field1 AS "text()" FOR XML PATH('ColumnValue'),TYPE),

(SELECT 'Field2' AS "@name", Field2 AS "text()" FOR XML PATH('ColumnValue'),TYPE),

(SELECT 'Field3' AS "@name", Field3 AS "text()" FOR XML PATH('ColumnValue'),TYPE)

FOR XML PATH('CustomInformation'),TYPE)

FROM @tbl

FOR XML PATH('instance'),Root('instances');

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum