SQL Server 2014 - FOR XML AUTO avoid automatic node nesting

Question

I'm trying to build some query to export data in XML and I build this query:

select
    [invoice].*,
    [rows].*,
    [payment].payerID,
    [items].picture
from InvoicesHeader [invoice]
join InvoicesRows   [rows]      on  [rows].invoiceID=[invoice].invoiceID 
join Payments       [payments]  on  [payments].paymentID=[invoice].paymentID
join Items          [items]     on  [items].itemID=[rows].itemID
FOR XML Auto, ROOT ('invoices'), ELEMENTS

and I got something like this as result

<invoices>    
    <invoice>
        <ID>82</ID>
        <DocType>R</DocType>
        <DocYear>2017</DocYear>
        <DocNumber>71</DocNumber>
        <IssueDate>2017-07-17T15:17:30.237</IssueDate>
        <OrderID>235489738019</OrderID>
        ...
        <payments>
            <payerID>3234423f33</payerID>
            <rows>
                <ID>163</ID>
                <ItemID>235489738019</ItemID>
                <Quantity>2</Quantity>
                <Price>1</Price>
                <VATCode>22</VATCode>
                <Color>-</Color>
                <Size></Size>
                <SerialNumber></SerialNumber>
                <items>
                    <picture>http://nl.imgbb.com/AAOSwOdpXyB4I.JPG</picture>
                </items>
            </rows>
            ....

        </payments>
    </invoice>
</invoices>

while I would like to have something like this where

[rows] is childnode of invoice and not of payments

<invoices>    
    <invoice>
        <ID>82</ID>
        <DocType>R</DocType>
        <DocYear>2017</DocYear>
        <DocNumber>71</DocNumber>
        <IssueDate>2017-07-17T15:17:30.237</IssueDate>
        <OrderID>235489738019</OrderID>
        ...
        <payments>
            <payerID>3234423f33</payerID>
        </payments>
        <rows>
            <ID>163</ID>
            <ItemID>235489738019</ItemID>
            <Quantity>2</Quantity>
            <Price>1</Price>
            <VATCode>22</VATCode>
            <Color>-</Color>
            <Size></Size>
            <SerialNumber></SerialNumber>
            <items>
                <picture>http://nl.imgbb.com/AAOSwOdpXyB4I.JPG</picture>
            </items>
        </rows>
            ....
    </invoice>
</invoices>

seen some solution where there are many

FOR XML AUTO

put all together, but the data here comes from connected table, would be a pity to re-query 2-3 times same values

how can achieve it?

Thanks

Answer 1

well, found that have to use FOR XML PATH instead and add the other table as subquery with each FOR XML PATH as follows:

select
[invoice].*,
p.payerID,
(select r.* from InvoiceRows r where r.invoiceID=i.invoiceID for XML PATH ('rows'), type)
from InvoicesHeader i
join payment        p on i.paymentID=p.paymentID
FOR XML PATH('invoice'), ROOT ('invoices'), ELEMENTS

Answer 2

Try changing the select order around to this;

select
    [invoice].*,
    [payment].payerID,
    [items].picture,
    [rows].*

from InvoicesHeader [invoice]
join InvoicesRows   [rows]      on  [rows].invoiceID=[invoice].invoiceID 
join Payments       [payments]  on  [payments].paymentID=[invoice].paymentID
join Items          [items]     on  [items].itemID=[rows].itemID
FOR XML Auto, ROOT ('invoices'), ELEMENTS