Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Nesting SQL to XML Grouping issue Expand / Collapse
Author
Message
Posted Monday, July 7, 2014 9:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 2:27 AM
Points: 2, Visits: 19
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.
Post #1589943
Posted Monday, July 7, 2014 12:55 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:25 AM
Points: 2,557, Visits: 1,616
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.
Post #1590059
Posted Monday, July 7, 2014 1:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 2:27 AM
Points: 2, Visits: 19
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.
Post #1590094
Posted Monday, July 7, 2014 4:17 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:19 PM
Points: 1,786, Visits: 5,682
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


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1590143
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse