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>....
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>....
which is not what I am wanting, I am wanting 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.
No comments:
Post a Comment