SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Nesting SQL to XML Grouping issue


Nesting SQL to XML Grouping issue

Author
Message
James1234uk
James1234uk
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 22
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
Arthur Olcot
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3186 Visits: 1779
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
James1234uk
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 22
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
mister.magoo
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4112 Visits: 7865
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
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Eirikur Eiriksson
    Eirikur Eiriksson
    SSCoach
    SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

    Group: General Forum Members
    Points: 15046 Visits: 18597
    Did you get this resolved? It only takes a minor change from previously posted code
    Cool

    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>


    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search