How to parse XML ordred list into table view

Question

How to parse XML ordered list like this

<ol>
  <li>value1</li>
  <li>value2</li>
  <li>value3</li>
</ol>

into table like this (like it's visible in html):

Nr          Value
----------- ------
1           value1
2           value2
3           value3

Here is the code for XML string:

declare @ol XML= '<ol><li>'+REPLACE('value1,value2,value3', ',', '</li><li>')+'</li></ol>' 
select  @ol

NB! Is it possible to parse "numbering" from XML without creating something like identity column?

Small update: Following solutions provide right answer for simple example above:

But is it possible to get solution for this more tricky example:

DECLARE @ol XML 
SET @ol= 
'<ol type="i" start="3">
  <li>value1</li>
  <li>value2</li>
  <li>value3</li>
</ol>';

Estimated result:

Nr   Value
---- -------
iii  value1
iv   value2
v    value3

?

What have you tried so far? Have you had a look at `XQUERY`? There's plenty of examples on SO on how to read the `xml` datatype in SQL Server; what answers didn't you understand so we can explain them to you. Thanks, — Thom A, Oct 23 '19 at 10:55
Is the order important e.g. is it OK to have `1, value3` in result instead of `1, value1`? — Salman A, Oct 23 '19 at 12:03

score 2 · Answer 1 · edited Oct 23 '19 at 14:25

2

Finding the nodes is easy, finding their relative position is tricky. Here is one solution by using what is called Node Comparison operation via << operator and count function:

DECLARE @ol XML = '<ol>
  <li>value1</li>
  <li>value2</li>
  <li>value3</li>
</ol>';
SELECT li.value('.', 'NVARCHAR(100)') AS value
     , li.value('let $n := . return count(../*[. << $n]) + 1', 'int') AS pos
FROM @ol.nodes('/ol/li') AS x(li)

edited Oct 23 '19 at 14:25

Yitzhak Khabinsky

18,471
2
15
21

answered Oct 23 '19 at 12:17

Salman A

262,204
82
430
521

Thank you guys, for this "crazy" example. :) Your solution works for any element. Is it possible for code to take into account that it's a specific elements `ol` `li` and read some "metadata"? – Denis Oct 24 '19 at 07:53
Not sure what you mean. If you're trying to match a specific `ol` e.g. `
– Salman A Oct 24 '19 at 08:23
I updated the post: put the link to your solution in my question and add one more tricky question/example. Do you have a solution for it? – Denis Oct 24 '19 at 08:30
1

@Denis the `type` and `start` attributes are only understood and rendered by a browser. XML does not know what these HTML attributes mean. It is possible to read the start attribute and add it to each number (1 becomes 3, 2 becomes 4) but I am not sure how to convert numbers to roman numerals. And then you have `type=a` as well. – Salman A Oct 24 '19 at 08:37
ok, let's view from other angle: is it possible to organize numbering in XML without using `
– Denis Oct 24 '19 at 09:00

THE LIFE-TIME LEARNER · Answer 2 · 2019-10-24T08:15:44.297

0

DECLARE @ol XML 
SET @ol= '<ol>
  <li>value1</li>
  <li>value2</li>
  <li>value3</li>
</ol>';

SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Nr,
li.value('.', 'NVARCHAR(100)') AS Value
FROM @ol.nodes('/ol/li') AS x(li)

Try this Simple Query...here...Getting Row Number ()...so we get Nr as Number...1,2,3..so on...and xml value into table....

You will get this type of Output...

Output :

Nr          Value
----------- ------
1           value1
2           value2
3           value3

After Edit Your Question...the Solution Should be Like this.....

DECLARE @ol XML 
SET @ol= 
'<ol type="i" start="3">
  <li>value1</li>
  <li>value2</li>
  <li>value3</li>
</ol>';

SELECT 2 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Nr,
li.value('.', 'NVARCHAR(100)') AS Value
FROM @ol.nodes('/ol/li') AS x(li)

Output :

Nr          Value
----------- ------
3           value1
4           value2
5           value3

here adding +2 value to RowNumber() so....we can get the value 2,3,4...and so on..

edited Oct 24 '19 at 08:15

answered Oct 23 '19 at 13:10

THE LIFE-TIME LEARNER

1,476
1
8
18

Are you sure if it's the deterministic solution? For example, Are you sure that for value1 I always get Nr 1, not something else (especially in big lists)? – Denis Oct 24 '19 at 07:46
1

No...No....it will generate the row number....1,2,3...and must be go on...not depends on value1 or value2 – THE LIFE-TIME LEARNER Oct 24 '19 at 08:10
Thank you. I updated the post: put the link to your solution in my question and add one more tricky question/example. Do you have a solution for it? – Denis Oct 24 '19 at 08:16
1

`ROW_NUMBER() OVER (ORDER BY (SELECT NULL))` will generate an arbitrary row number. You have no guarantee that `Value1` will always get 1 as it's row number. read [The “Natural order” misconception](https://zoharpeled.wordpress.com/2019/09/08/the-natural-order-misconception/) – Zohar Peled Oct 24 '19 at 08:20
@akhileshsingh unfortunately as @Zohar Peled mentioned above it's not safe to use `ROW_NUMBER()` for this purpose. – Denis Oct 24 '19 at 08:27

How to parse XML ordred list into table view

2 Answers2

Linked