snippetsqlModerate

How To Read HTML code as XML and get the output like the sample in sql?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

thesamplereadsqlandoutputxmllikegethow

Problem

I have HTML code stored in the data base, and I want to read it as XML.

My codes:

http://rextester.com/RMEHO89992

This is an example of the HTML code I have:

and this is an example of the output I need:

Category         Selection        Value                    
---------        ---------        ------------             
A                Ab               AD                  
A                Ag               AL                       
B                Bb               BD                   
B                Bg               BL

I need to get the value inside the ` tag as a Category, the first

 tag as Selection, and the rest of the values as a concatenated string.

I've tried the following query:

SELECT 
    (  isnull(t.v.value('(h4/span/span[1]/text())[1]','nvarchar(max)'),'') 
     + isnull(t.v.value('(h4/span/text())[1]','nvarchar(max)'),'')
     + isnull(t.v.value('(h4/span/span[2]/text())[2]','nvarchar(max)'),'')
    ) AS [Category],
    (  isnull(c.g.value('(span[1]/text())[1]','nvarchar(max)'),'')
     + isnull(c.g.value('(span[1]/span/text())[1]','nvarchar(max)'),'')
     + isnull(c.g.value('(span[1]/text())[2]','nvarchar(max)'),'')
    ) AS [Selection],
    (  isnull(c.g.value('(span[2]/text())[1]','nvarchar(max)'),'')
     + isnull(c.g.value('(span[2]/span/text())[1]','nvarchar(max)'),'')
     + isnull(c.g.value('(span[2]/text())[2]','nvarchar(max)'),'')
    ) AS [Value]
FROM @htmlXML.nodes('div/section') as t(v)
CROSS APPLY t.v.nodes('./ul/li') AS c(g)


and :

``
SELECT
t.v.value('.','nvarchar(max)')
,
--( is

Solution

I am trying to establish communication between nodesh4 and ul.

You can use the > operator to check if a node is before or after another node in document order. Combine that with a predicate on position, [1], to get the first occurrence also in document order.

select H4.X.value('(span/text())[1]', 'varchar(10)') as Section,
       UL.X.query('.') as UL
from @X.nodes('/div/section/h4') as H4(X)
  cross apply H4.X.nodes('(let $h4 := . (: Save current h4 node :)
                           return /div/section/ul[$h4 

rextester:

 are called Node Order Comparison Operators

If you have an XML fragment like this:

1
2
3
4
5



you can get all nodes before the first occurrence of

 with this query:

select @X.query('/*[.

Result:

1
2

/* will give you all root nodes. What is enclosed in [] is a predicate. . is the current node and /N3[1] is the first N3 node in document order at the root level. So from each root node you get the nodes that precede N3.

Here is almost the same query, only you get the nodes that follow the first N3 node:

select @X.query('/*[. >> /N3[1]]');

4
5

To only get the first node after the first N3 node, you add the predicate [1]:

select @X.query('/*[. >> /N3[1]][1]');

Context

StackExchange Database Administrators Q#162514, answer score: 14

Revisions (0)

No revisions yet.