Handling multiple childs for the same element generated in XML from SQL using "for XML clause"

Question

I want to generate a XML file using a specific query. The main issue is that when I generate the XML, the output would look like this:

<nsSAFT:Account xmlns:nsSAFT="uri">
  <nsSAFT:Produs>
    <nsSAFT:CodProdus>0200943</nsSAFT:CodProdus>
    <nsSAFT:Denumire>SPRAY SPECIAL EFECT 151 SILVER METAL</nsSAFT:Denumire>
    <nsSAFT:Miscari>
      <nsSAFT:Cantitate>              1.00</nsSAFT:Cantitate>
    </nsSAFT:Miscari>
  </nsSAFT:Produs>
</nsSAFT:Account>
<nsSAFT:Account xmlns:nsSAFT="uri">
  <nsSAFT:Produs>
    <nsSAFT:CodProdus>0200943</nsSAFT:CodProdus>
    <nsSAFT:Denumire>SPRAY SPECIAL EFECT 151 SILVER METAL</nsSAFT:Denumire>
    <nsSAFT:Miscari>
      <nsSAFT:Cantitate>              2.00</nsSAFT:Cantitate>
    </nsSAFT:Miscari>
  </nsSAFT:Produs>
</nsSAFT:Account>

The main problem is that I want to have multiple children on the same product. My expected output would look like this:

<nsSAFT:Account xmlns:nsSAFT="uri">
  <nsSAFT:Produs>
    <nsSAFT:CodProdus>0200943</nsSAFT:CodProdus>
    <nsSAFT:Denumire>SPRAY SPECIAL EFECT 151 SILVER METAL</nsSAFT:Denumire>
    <nsSAFT:Miscari>
      <nsSAFT:Cantitate>              1.00</nsSAFT:Cantitate>
    </nsSAFT:Miscari>
    <nsSAFT:Miscari>
          <nsSAFT:Cantitate>              2.00</nsSAFT:Cantitate>
    </nsSAFT:Miscari>    
  </nsSAFT:Produs>
</nsSAFT:Account>

The SQL query I used for generating the first output mentioned by me looks like this:

WITH XMLNAMESPACES ('uri' as nsSAFT)
    SELECT
        RTRIM(P.codProdus) AS 'nsSAFT:Produs/nsSAFT:CodProdus',
        RTRIM(P.Denumire) AS 'nsSAFT:Produs/nsSAFT:Denumire',
        STR(M.Cantitate, 18, 2) AS 'nsSAFT:Produs/nsSAFT:Miscari/nsSAFT:Cantitate'
    FROM
        Miscari M 
    INNER JOIN  
        ProdusGestiune PG ON M.idProdusGestiune = PG.idProdusGestiune 
    INNER JOIN
        Produs P ON PG.idProdus = P.idProdus 
    FOR XML PATH ('nsSAFT:Account'), ELEMENTS ;

The data sample would look like this:

CodProdus	Denumire	Cantitate
0200943	SPRAY SPECIAL EFECT 151 SILVER METAL	1.00
0200943	SPRAY SPECIAL EFECT 151 SILVER METAL	2.00
0200943	SPRAY SPECIAL EFECT 151 SILVER METAL	5.00
0200947	SPRAY SPECIAL USE 230 PENETRATING OIL	6.00

I use the following tables: "Produs": | CodProdus | Denumire | |:---- |:------:| | 0200943 | SPRAY SPECIAL EFECT 151 SILVER METAL |
| 0200954 | SPRAY ACRILIC MAT 9005 400ML |
| 0200955 | SPRAY ACRILIC MAT 9016 400ML |
| 0200960 | SPRAY ACRILIC RAL 3000 400ML |

"Miscari": | Cantitate| |:---- |:------:| | 14.000000 |
| 12.000000 |
| 5.000000 |

I tried to use "select distinct", but the SSMS returns me an error. I also tried multiple queries using "union all" and I met some errors too.

Answer 1

You're probably wanting a subquery to generate correlated Cantitate subelements, such as with the following:

WITH XMLNAMESPACES ('uri' as nsSAFT)
  SELECT
    RTRIM(P.codProdus) AS [nsSAFT:CodProdus],
    RTRIM(P.Denumire) AS [nsSAFT:Denumire],
    (
      SELECT
        STR(M.Cantitate, 18, 2) AS [nsSAFT:Cantitate]
      FROM
        ProdusGestiune PG
      INNER JOIN  
        Miscari M ON M.idProdusGestiune = PG.idProdusGestiune
      WHERE
        PG.idProdus = P.idProdus
      FOR XML PATH('nsSAFT:Miscari'), TYPE
    )
  FROM
    Produs P
  --WHERE codProdus='0200943'
  FOR XML PATH('nsSAFT:Produs'), ROOT('nsSAFT:Account'), ELEMENTS;