on 2022 Mar 21 10:49 AM
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
Request clarification before answering.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks I knew that was a simple thing. It Was just the order by. With the good order by it works well.
User | Count |
---|---|
81 | |
30 | |
10 | |
8 | |
8 | |
7 | |
6 | |
6 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.