jessy summer jessy summer - 1 month ago 16
SQL Question

get recordset from xml like html

Microsoft SQL Server. I have an xml:

<div class="list">
<ul>
<li class="nr">5859. </li>
<li class="task">task</li>
<li class="var">Norman</li>
<li class="var">Dave</li>
</ul>




What kind select method should I use to extract result get values in one row, like:
nr, task, var1, var2

Answer

You say: I have an xml:

No! You have not! You have HTML!

Many people think, they are the same, at least they look so similar, but HTML is not XML. In your example the missing closing <div>-tag would break any XML based approach...

Taken just the list-definition you might use XML approach:

DECLARE @Xml XMl=
 '<ul>
    <li class="nr">5859. </li>
    <li class="task">task</li>
    <li class="var">Norman</li>
    <li class="var">Dave</li>
</ul>' 

SELECT li.value('@class','nvarchar(max)') AS NodeClass
      ,li.value('.','nvarchar(max)') AS NodeValue
FROM @xml.nodes('/ul/li') AS A(li)

But I'd really strongly advise not to parse HTML with SQL-Server...

If you can be sure, that the given HTML is XHTML, it is something different. XHTML is HTML with strict compliance of XML standards. In this case, and only in this case!, this is a good approach...