Parse XML using SQL Server

Question

In a nvarchar field Description I have the following data:

<p>Hello hi and a bunch of non-xml characters &nbsp; etc...</p>
<ul class='abc'>
  <li><img src='1.jpg' /></li>
  <li><img src='2.jpg' /></li>
</ul>

I want to query this "xml" and get the following result:

<img src='1.jpg' />
<img src='2.jpg' />

Therefore I write the following query:

SELECT
    p.value('(.)[1]', 'nvarchar(100)')
FROM
(
    SELECT
        CAST(SUBSTRING(Description, CHARINDEX('<ul class=''abc''>', Description), LEN(Description)) AS XML) AS Xml
    FROM Table
        WHERE Description LIKE '%<ul class=''abc''>%'
) AS Result CROSS APPLY Xml.nodes('/ul/li') t(p)

But all my results are NULL. It seems that I'm doing something wrong in my XML selector... what am I doing wrong?

Answer 1

You need to use query instead of value

DECLARE @html NVARCHAR(MAX) = N'
<p>Hello hi and a bunch of non-xml characters &nbsp; etc...</p>
<ul class=''abc''>
  <li><img src=''1.jpg'' /></li>
  <li><img src=''2.jpg'' /></li>
</ul>'

SELECT
    p.query('.')
FROM
(
    SELECT CAST(SUBSTRING(@html, CHARINDEX('<ul class=''abc''>', @html), LEN(@html)) AS XML) AS XML
) AS Result 
CROSS APPLY xml.nodes('/ul/li/img') t(p)