Nesting SQL to XML Grouping issue

  • 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.

  • 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.

  • 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.

  • 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]

  • Did you get this resolved? It only takes a minor change from previously posted code

    😎

    SELECT

    AC.name AS 'Acct',

    (SELECT column_id AS 'Item/data()'

    FROM sys.columns AS ITEMS1

    WHERE ITEMS1.object_id = AC.object_id

    FOR XML PATH ('Items'), TYPE)

    FROM

    sys.tables AC WITH(NOLOCK)

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

    Results

    <Accts>

    <Account>

    <Acct>#A161554C</Acct>

    <Items>

    <Item>1</Item>

    </Items>

    <Items>

    <Item>2</Item>

    </Items>

    <Items>

    <Item>3</Item>

    </Items>

    <Items>

    <Item>4</Item>

    </Items>

    <Items>

    <Item>5</Item>

    </Items>

    <Items>

    <Item>6</Item>

    </Items>

    <Items>

    <Item>7</Item>

    </Items>

    <Items>

    <Item>8</Item>

    </Items>

    </Account>

  • Viewing 5 posts - 1 through 4 (of 4 total)

    You must be logged in to reply to this topic. Login to reply