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?

T I · Accepted Answer · 2013-10-28 14:07:19Z

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)

answered Oct 28, 2013 at 14:07

T I

9,9634 gold badges31 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Parse XML using SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related