Exporting a SQL table to a XML file with a predetermined schema

Question

I am trying to obtain a XML file from mssql table. The query I'm using does not get quite the results I'm expecting.

First I created and add some rows to the table like follows:

CREATE TABLE NiceTable (ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, DOCNUM varchar(50),CARDNAME varchar(50),ITEMCODE varchar(50),QUANTITY int)
INSERT INTO NiceTable (DOCNUM,CARDNAME,ITEMCODE,QUANTITY) VALUES ('100','Customer1','Item A',1);
INSERT INTO NiceTable (DOCNUM,CARDNAME,ITEMCODE,QUANTITY) VALUES('100','Customer1','Item B',1);
INSERT INTO NiceTable (DOCNUM,CARDNAME,ITEMCODE,QUANTITY) VALUES('200','Customer2','Item C',2);

Now I'm looking to obtain something like this:

<Facturi>
    <Factura>
        <Antet>
            <DOCNUM>100</DOCNUM>
            <CARDNAME>Customer 1</CARDNAME>
        </Antet>
        <Detalii>
        <Continut>
            <Linie>
                <ITEMCODE>Item A</ITEMCODE>
                <QUANTITY>1</QUANTITY>
            </Linie>
            <Linie>
                <ITEMCODE>Item B</ITEMCODE>
                <QUANTITY>1</QUANTITY>
            </Linie>
        </Continut>
        </Detalii>            
    </Factura>
        <Factura>
        <Antet>
            <DOCNUM>200</DOCNUM>
            <CARDNAME>Customer 2</CARDNAME>
        </Antet>
        <Detalii>
        <Continut>
            <Linie>
                <ITEMCODE>Item c</ITEMCODE>
                <QUANTITY>2</QUANTITY>
            </Linie>
        </Continut>
        </Detalii>            
    </Factura>
</Facturi>

For this result I'm using the following query:

SELECT
(SELECT
(SELECT DISTINCT T1.DOCNUM AS [DOCNUM], T1.CARDNAME AS [CARDNAME] FROM NiceTAble AS T1
WHERE T1.DOCNUM=T0.DOCNUM
FOR XML PATH('Antet'), TYPE)
),      
(
(SELECT 
(SELECT
(SELECT T1.ITEMCODE AS [ITEMCODE], T1.QUANTITY AS [QUANTITY] FROM NiceTAble AS t1
WHERE t1.DOCNUM=T0.DOCNUM
FOR XML PATH('Linie'),TYPE,root('Continut')
)
FOR XML PATH('Detalii'),TYPE
)))
FROM NiceTAble AS T0
FOR XML PATH('Factura'), TYPE , root('Facturi')

I don't understand what I'm doing wrong, but in the results, the DOCNUM and CUSTOMER will be repeated twice and I only need it once like in my example.

Answer 1

You can use xml aggregation

SELECT 
  (SELECT T0.DOCNUM, T0.CARDNAME 
   FOR XML PATH(''), TYPE
  ) Antet,
  (SELECT T1.ITEMCODE AS [ITEMCODE], T1.QUANTITY AS [QUANTITY] 
   FROM NiceTAble AS T1
   WHERE T1.DOCNUM=T0.DOCNUM
   FOR XML PATH('Linie'), TYPE, ROOT('Continut')
  ) Detalii 
FROM NiceTAble AS T0
GROUP BY T0.DOCNUM, T0.CARDNAME
FOR XML PATH('Factura'), TYPE, ROOT('Facturi')