Select For XML Explicit

[former_SQLA_member1694891]

Hi,

I try to make a select with an output in XML. For this I use the "FOR XML EXPLICIT" option of the SELECT statement. I build my select based on the sap documentation, but my xml and the documentation xml don't have the same structure, and I don't figure out why, I must miss something but I don't arrive to put my finger on.

Here my select statement :

SELECT
    1           AS tag,
    NULL        AS parent,
    PlvID       AS [Ligne!1!LigneID],
    PlvNumLig   AS [Ligne!1!NumeroLigne],
    PlvLib      AS [Ligne!1!LibLigne],
    NULL        AS [Article!2!CodeArticle],
    NULL        AS [Article!2!LibArticle]
FROM PLV
Where PlvID IN(228, 229)
UNION all
SELECT
    2,
    1,
    PlvID,
    NULL,
    NULL,
    ArtCode,
    ArtLib
FROM PLV P JOIN ART A on P.PlvGArtID = A.ArtID
WHERE P.PlvID IN(228, 229)
ORDER BY 1, 2
FOR XML EXPLICIT

And the XML I get:

<Ligne LigneID="228" NumeroLigne="1" LibLigne="Baton de ski">
<Ligne LigneID="229" NumeroLigne="2" LibLigne="Combinaison de plongée">
    <Article CodeArticle="ART005" LibArticle="Baton de ski">
    <Article CodeArticle="ART014" LibArticle="Combinaison de plongée">
</Ligne>

And here the XML I want/hope :

<Ligne LigneID="228" NumeroLigne="1" LibLigne="Baton de ski">
    <Article CodeArticle="ART005" LibArticle="Baton de ski">
</Ligne>
<Ligne LigneID="229" NumeroLigne="2" LibLigne="Combinaison de plongée">
    <Article CodeArticle="ART014" LibArticle="Combinaison de plongée">
</Ligne>

Thanks in advance

[VolkerBarth · Accepted solution]

I'm not very familiar with FOR XML EXPLICIT, but it seems that your final ORDER BY is wrong. The result set must be ordered that child items are sorted after the according parent item, such as

ORDER BY 3, tag, parent, [, ...]

You can check the correctness of the sorting by omitting the FOR XML EXPLICIT clause.

[former_SQLA_member1694891 · Accepted solution]

Thanks I knew that was a simple thing. It Was just the order by. With the good order by it works well.