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

asked 21 Mar, 10:49

Ben8sens's gravatar image

Ben8sens
136101219
accept rate: 37%

edited 21 Mar, 11:39

Vlad's gravatar image

Vlad
2.5k91127


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.

permanent link

answered 21 Mar, 11:01

Volker%20Barth's gravatar image

Volker Barth
38.5k349525795
accept rate: 34%

edited 21 Mar, 11:10

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, 04:31) Ben8sens
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×62
×24

question asked: 21 Mar, 10:49

question was seen: 147 times

last updated: 31 Mar, 04:31