Getting specific data from XML column

Question

I have the following table in SQL Server 2014:

Table [Product]

Serie,  Int (primary key column)
Name, varchar(100)
LeftMenu, xml

Here is an example of the XML in the LeftMenu column each row could have:

<menu>
<e>
<col>1</col>
<url>
/products/pressure-relief/pressure-relief-valves/general-info
</url>
<IDElement>General-Info
</IDElement>
</e>
<e>
<col>2</col>
<url>
/products/pressure-relief/pressure-relief-valves/parts
</url>
<IDElement>parts
</IDElement>
</e>
</menu>

The expected result is like the following

Serie | col | name
-------------------
1000  |  1  | parts

From a given Serie (the primary key), I want to get the value of the node <col> passing the value of the <IDElement> tag.

It is like searching inside each <IDElement> in the XML and return the value of the <col> tag that matches that group of elements.

I am trying the following but somehow it is not working:

select p.serie, p.name,
pref.value('(col())[1]', 'varchar(max)') as MenuName,
from prodInfo p 
p.[left-menu].nodes('menu/e') as names  (pref)
WHERE  
pre.value('(IDElement())[1]', 'varchar(max)') == @IDElement
AND p.serie =@serie

Could you please tell me what is wrong?

Other option my partner suggested is to do it as in old times and create a new table instead of using XML, any suggestions?

Thank you!

OK thank you, I've updated the question with the expected result — franko_camron
– franko_camron, Commented Oct 10, 2016 at 14:58

John Cappelletti · Accepted Answer · 2016-10-10 17:11:24Z

2

I commented the WHERE to illustrate PARTS is Col 2, OR I completely misunderstood your requirements

Declare @YourTable table (Series int,Name varchar(100),LeftMenu xml)
Insert Into @YourTable values
(1000,'Some Series Name','<menu><e><col>1</col><url>/products/pressure-relief/pressure-relief-valves/general-info</url><IDElement>General-Info</IDElement></e><e><col>2</col><url>/products/pressure-relief/pressure-relief-valves/parts</url><IDElement>parts</IDElement></e></menu>')

Declare @Fetch varchar(100) = 'Parts'

Select A.Series
      ,B.*
 From  @YourTable A
 Cross Apply (
                Select  Col  = B.value('col[1]','int') 
                       ,Name = B.value('IDElement[1]','varchar(100)') 
                       ,URL  = B.value('url[1]','varchar(100)') 
                From LeftMenu.nodes('/menu') AS A(Grp)
                Cross Apply A.Grp.nodes('e') AS B(B)
                --Where  B.value('IDElement[1]','varchar(100)') = @Fetch
             ) B

Returns

Series  Col Name            URL
1000    1   General-Info    /products/pressure-relief/pressure-relief-valves/general-info
1000    2   parts           /products/pressure-relief/pressure-relief-valves/parts

edited Oct 10, 2016 at 17:11

answered Oct 10, 2016 at 15:18

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

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

5 Comments

franko_camron Over a year ago

the example return no values, I had to declare thexml variable, DECLARE @XML xml

John Cappelletti Over a year ago

@franko_camron Sorry, I made an edit it should be From LeftMenu.nodes('/menu') AS A(Grp)

John Cappelletti Over a year ago

@franko_camron Updated. Thanks for the catch.

franko_camron Over a year ago

Thank you @john Cappelletti, it throws an error Invalid column name 'LeftMenu', guess because it is out of scope

John Cappelletti Over a year ago

@franko_camron Runs clean here - new window and all. What is the field name which contains your XML data

Collectives™ on Stack Overflow

Getting specific data from XML column

Table [Product]

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Table [Product]

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related