|
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 |
|
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. 1
Thanks I knew that was a simple thing. It Was just the order by. With the good order by it works well.
(31 Mar '22, 04:31)
Ben8sens
|
