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:

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.

CodePudding user response：

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;