Constructing XML data

• BrainDonor

  SSCoach

  Points: 19262

  More actions

  February 27, 2012 at 7:59 am

  #251438

  Although I have extracted XML data on occassions, I have never constructed more than very basic XML data and am struggling with the task below.

  I need to produce an XML record that has a 'header' section, providing interface information for a target system, followed by different nodes dependent upon the data extracted for this.

  As a simplified example:

  DECLARE @Version INT;

  DECLARE @Identifier INT;

  SET @Version = 123;

  SET @Identifier = 456;

  CREATE TABLE #XML_Demo(

  Att_IDVARCHAR(10),

  Att_RefVARCHAR(10),

  Att_AnswerVARCHAR(10),

  Att_Ref2VARCHAR(10),

  Att_SubINT

  );

  INSERT INTO #XML_Demo(Att_ID, Att_Ref, Att_Answer, Att_Ref2, Att_Sub)

  VALUES

  ('', '', '567', '1.2.3' , NULL),

  ('', '', '890', '4.5.6' , NULL),

  ('123', '12', '345', '6.7.8' , 1),

  ('123', '12', '567', '1.2.3' , 2),

  ('124', '34', '567', '4.5.6' , 3),

  ('', '', '567', '1.2.3' , 4),

  ('', '', '789', '4.5.6' , 5);

  select * from #XML_Demo;

  I should be able to produce the following XML data:

  <?xml version="1.0" encoding="utf-8" ?>

  - <MainData>

  - <MainDataHeader>

  <TheVersion>123</TheVersion>

  <TheIdentifier>456</TheIdentifier>

  </MainDataHeader>

  - <NullSubData>

  <OtherAnswer NullField1="567" NullField2="1.2.3" />

  <OtherAnswer NullField1="890" NullField2="4.5.6" />

  </NullSubData>

  - <OtherData>

  - <Other ID="123" Ref="12">

  <OtherAnswer NullField1="345" NullField2="6.7.8" />

  <OtherAnswer NullField1="567" NullField2="1.2.3" />

  </Other>

  - <Other ID="124" Ref="34">

  <OtherAnswer NullField1="567" NullField2="4.5.6" />

  </Other>

  - <Other ID="" Ref="">

  <OtherAnswer NullField1="567" NullField2="1.2.3" />

  <OtherAnswer NullField1="789" NullField2="4.5.6" />

  </Other>

  </OtherData>

  </MainData>

  Where 'Att_Sub' is NULL, the data should be recorded under <NullSubData>, any other value for 'Att_Sub' is grouped under '<OtherData><Other>'.

  With my limited XML experience I manage to produce various unsightly versions, but nowhere near what I'm after.

  Any assistance with this would be appreciated.

  Steve Hall
  Linkedin
  Blog Site

• Mark Cowne

  One Orange Chip

  Points: 26979

  More actions

  February 27, 2012 at 9:38 am

  #1452158

  You have to use lots of sub queries. The encoding header will have to be manually prepended.

  SELECT (SELECT @Version AS "TheVersion",

  @Identifier AS "TheIdentifier"

  FOR XML PATH('MainDataHeader'),TYPE),

  (SELECT Att_Answer AS "@NullField1",

  Att_Ref2 AS "@NullField2"

  FROM #XML_Demo

  WHERE Att_Sub IS NULL

  FOR XML PATH('OtherAnswer'),ROOT('NullSubData'),TYPE),

  (SELECT t1.Att_ID AS "@ID",

  t1.Att_Ref AS "@Ref",

  (SELECT t2.Att_Answer AS "@NullField1",

  t2.Att_Ref2 AS "@NullField2"

  FROM #XML_Demo t2

  WHERE t2.Att_ID=t1.Att_ID

  AND t2.Att_Ref=t1.Att_Ref

  AND t2.Att_Sub IS NOT NULL

  ORDER BY t2.Att_Sub

  FOR XML PATH('OtherAnswer'),TYPE)

  FROM #XML_Demo t1

  WHERE t1.Att_Sub IS NOT NULL

  GROUP BY t1.Att_ID,t1.Att_Ref

  ORDER BY MAX(t1.Att_Sub)

  FOR XML PATH('Other'),ROOT('OtherData'),TYPE)

  FOR XML PATH('MainData')

  ____________________________________________________

  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

• BrainDonor

  SSCoach

  Points: 19262

  More actions

  February 27, 2012 at 9:47 am

  #1452164

  Wonderful, thank you very much for your time and assistance.

  I had just found the section within my copy of "Programming Microsoft SQL Server 2008", and although I could see it consisted of subqueries I just couldn't get my head around it correctly. I was getting a bit tired of seeing "invalid syntax near ','" and suchlike.

  Thank you.

  Steve Hall
  Linkedin
  Blog Site