FOR XML EXPLICIT driving me nuts!

[J4amieC]

Ive done this so many times, I just cant seem to get my head around what's wrong! I have a table with data which looks like this Columns (in order): siteNo, cardSchemeCode, currencyCode, saleAmount, saleTotal, refundAmount, refundTotal

0000001 DEL GBP 12 240.05 0 0.00
0000001 JCB GBP 1 20.00 0 0.00
CU1000000001 DEL GBP 10 166.51 0 0.00

I am trying to get Xml that looks like:

<Root>
<Site siteNo="0000001">
<Currency currencyCode="GBP">
</Site>
<Site siteNo="CU1000000001">
<Currency currencyCode="GBP">
</Site>
</Root>

Instead I get:

<Root>
<Site siteNo="0000001" />
<Site siteNo="CU1000000001">
<Currency currencyCode="GBP" />
<Currency currencyCode="GBP" />
</Site>
</Root>

Here is my SQL:

SELECT 1 TAG,
NULL Parent,
Site.siteNo 'Site!1!siteNo',
NULL 'Currency!2!currencyCode'
FROM records [Site]
GROUP BY siteNo
UNION
SELECT 2 TAG,
1 Parent,
Site.siteNo,
Currency.currencyCode
FROM records [Currency]
INNER JOIN records [Site]
ON Currency.siteNo = Site.siteNo
GROUP BY Site.siteNo, Currency.currencyCode
FOR XML EXPLICIT, ROOT('Root')

Any help?

[Wendelius]

Hi, Your query is otherwise correct, but you must add the ORDER BY clause since the FOR XML processes the row (almost) without no logic in the order they are in the result set. So the query would be:

...
ORDER BY 'Site!1!siteNo',
'Currency!2!currencyCode'
FOR XML EXPLICIT, ROOT('Root')

Mika

The need to optimize rises from a bad design.My articles[^]