Nesting SQL to XML Grouping issue

• James1234uk

  SSC Journeyman

  Points: 94

  More actions

  July 7, 2014 at 9:04 am

  #99919

  I have an issue with the formatting when outputting SQL to XML.

  The following works as expected:-

  SELECT

  AC.ACCOUNT AS 'Acct',

  (SELECT ITEMNO AS 'ITEM'

  FROM ITEMS AS ITEMS1 WITH(NOEXPAND)

  WHERE ITEMS1.CODE = AC.CODE

  FOR XML PATH ('Items'), TYPE)

  FROM

  AC WITH(NOLOCK)

  INNER JOIN

  ITEMS ON AC.CODE = ITEMS.CODE

  FOR XML PATH (''), ROOT ('Accts'), ELEMENTS

  Which outputs the following XML:

  <Accts>

  <Acct>94615130</Acct>

  <Items>

  <ITEM>1001</ITEM>

  </Items>

  <Items>

  <ITEM>1050</ITEM>

  </Items>

  <Items>

  <ITEM>1051</ITEM>

  </Items>

  <Items>

  <ITEM>1054</ITEM>

  </Items>

  <Items>

  <ITEM>1055</ITEM>

  </Items>

  ....

  </Accts>

  But when I add an extra clause in the where of the subquery like this:

  SELECT

  AC.ACCOUNT AS 'Acct',

  (SELECT ITEMNO AS 'ITEM'

  FROM ITEMS AS ITEMS1 WITH(NOEXPAND)

  WHERE ITEMS1.CODE = AC.CODE

  AND ITEMS1.ITEMNO = ITEMS.ITEMNO

  FOR XML PATH ('Items'), TYPE)

  FROM

  AC WITH(NOLOCK)

  INNER JOIN

  ITEMS ON AC.CODE = ITEMS.CODE

  FOR XML PATH (''), ROOT ('Accts'), ELEMENTS

  I get this xml output:

  <Accts>

  <Acct>94615130</Acct>

  <Items>

  <ITEM>28</ITEM>

  </Items>

  <Acct>94615130</Acct>

  <Items>

  <ITEM>36</ITEM>

  </Items>

  <Acct>94615130</Acct>

  <Items>

  <ITEM>114</ITEM>

  </Items>

  <Acct>94615130</Acct>

  <Items>

  <ITEM>161</ITEM>

  </Items>

  ....

  </Accts>

  which is not what I want. I need all items to be grouped under the account number like in the first example rather than repeating the account number for every item.

  I am using Microsoft SQL Server 2008, any help is welcomed.

• Arthur Olcot

  SSCertifiable

  Points: 6639

  More actions

  July 7, 2014 at 12:55 pm

  #1727530

  Hi, the reason why the xml is changing when you add the additional predicate is because the sub query is only returning a single record (item) for each account rather than a batch of items per acct. You can see this easier by commenting out the outer for xml clause and see what is returned.

  it may be me, but I didn't really get how you needed the query to function exactly so I've had to make some assumptions to the intentions of your query and the actual structure you need the xml to look like.

  I've come up with the below (converting your query to use sys.tables/sys.columns to mimic acct/item) which I think may get you closer to a better xml structure:

  SELECT

  AC.name AS 'Acct',

  (SELECT column_id AS 'data()'

  FROM sys.columns AS ITEMS1

  WHERE ITEMS1.object_id = AC.object_id

  FOR XML PATH ('Item'), ROOT('Items'), TYPE)

  FROM

  sys.tables AC WITH(NOLOCK)

  FOR XML PATH ('Account'), ROOT ('Accts'), ELEMENTS

  hope this helps.. if not, then if you can describe how you need the final xml to look like then that will help with an answer.

• James1234uk

  SSC Journeyman

  Points: 94

  More actions

  July 7, 2014 at 1:57 pm

  #1727562

  Hi, thanks for the reply. I need the output to be like below:-

  <Accts>

  <Acct>94615130</Acct>

  <Items>

  <ITEM>1001</ITEM>

  </Items>

  <Items>

  <ITEM>1050</ITEM>

  </Items>

  <Items>

  <ITEM>1051</ITEM>

  </Items>

  <Items>

  <ITEM>1054</ITEM>

  </Items>

  <Items>

  <ITEM>1055</ITEM>

  </Items>

  ....

  </Accts>

  But I need the where clause in the sub query to be like Item1.Code = Item.code AND Item1.ItemNo = Item.ItemNo as doing it on Code or account number alone is not unique enough as this will be applied to a much larger query where there will be many different unique items linked to a account or code number.

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  July 7, 2014 at 4:17 pm

  #1727603

  If you can post some sample data, you will get a working solution, but at the moment we have nothing to work with.

  Please read the second article found in my signature:

  "Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden"

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]