XML Question (?)

• btio_3000

  SSCrazy

  Points: 2662

  More actions

  April 18, 2013 at 6:52 pm

  #292302

  Hi,

  Let's say I have the following table with just 1 record:

  SELECT Field1, Field2, Field3 FROM tbl

  returns 11, 22, 33

  What would be a query to return something like:

  <CustomInformation>

  <ColumnValue name="Field1">11</ColumnValue>

  <ColumnValue name="Field2">22</ColumnValue>

  <ColumnValue name="Field3">33</ColumnValue>

  </CustomInformation>

  ?

  Thanks!

• Mark Cowne

  One Orange Chip

  Points: 26954

  More actions

  April 19, 2013 at 2:55 am

  #1608117

  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)

  FROM tbl

  FOR XML PATH('CustomInformation')

  ____________________________________________________

  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

• btio_3000

  SSCrazy

  Points: 2662

  More actions

  April 19, 2013 at 11:27 am

  #1608339

  Beautiful, thanks so much!

• btio_3000

  SSCrazy

  Points: 2662

  More actions

  April 30, 2013 at 6:19 pm

  #1611441

  Journey goes on...

  Now, what if I have two records and need something like

  <instances>

  <instance instanceCode1="Rec1_Code1" instanceCode2="Rec1_Code2">

  ....<CustomInformation>

  ........<ColumnValue name="Field1">11</ColumnValue>

  ........<ColumnValue name="Field2">22</ColumnValue>

  ........<ColumnValue name="Field3">33</ColumnValue>

  ....</CustomInformation>

  </instance>

  <instance instanceCode1="Rec2_Code1" instanceCode2="Rec2_Code2">

  ....<CustomInformation>

  ........<ColumnValue name="Field1">1111</ColumnValue>

  ........<ColumnValue name="Field2">2222</ColumnValue>

  ........<ColumnValue name="Field3">3333</ColumnValue>

  ....</CustomInformation>

  </instance>

  </instances>

  ?

  I figured the external <instances>, the internal <instance>.... Totally stuck

  Thanks in advance as always

• Mark Cowne

  One Orange Chip

  Points: 26954

  More actions

  May 1, 2013 at 2:28 am

  #1611490

  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

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

• btio_3000

  SSCrazy

  Points: 2662

  More actions

  May 1, 2013 at 12:46 pm

  #1611684

  Ok, getting there:-)

  works fine, thank you once again