Hello,
I inherited of a piece of SQL extracting information from a XMLTYPE column and I have a problem with the order of the items returned.
My XML looks like this:
<Recommendation><Line>...</Line><Line>...</Line> etc. </Recommendation>
This represent a comment and is divided into multiple lines (a limitation of the source system).
I want to read the content of each line and save it in a VARCHAR2 field but I want to keep the original order of the <Line> elements to have a message with a meaning in the end, so next to the text I want to store a "Line number" column.
To do this work I inherited something like this:
SELECT
a.id,
EXTRACT(VALUE(rec), 'Line') as line,
ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY null) as line_number
FROM table_with_the_id_to_process loads,
table_with_my_XML a,
TABLE(XMLSEQUENCE(EXTRACT(a.xmltext, '//Recommendation/Line'))) rec
WHERE loads.id = a.id
It worked fine with 2-3 rows during tests, but when running on real data (250-300 rows with XML fields) the "line_number" become a little random: it always start at 1 and go till the number of <Line> element, but the order is like random, so my comments doesn't have a logic meaning if I retrieve them ordered by "line_number".
Any idea on how to get the <Line> position number or something else to calculate my "line_number" column?
I maybe missed it in the doc and Google just because using bad wording for the search, so I'm open to any suggestion or idea.
Thanks