franko_camron franko_camron - 1 year ago 61
SQL Question

sql server getting specific data from XML column

HI friends 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:


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
passing the value of the

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

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

select p.serie,,
pref.value('(col())[1]', 'varchar(max)') as MenuName,
from prodInfo p
p.[left-menu].nodes('menu/e') as names (pref)
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!

Answer Source

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
 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 @xml.nodes('/menu') AS A(Grp)
                Cross Apply A.Grp.nodes('e') AS B(B)
                --Where  B.value('IDElement[1]','varchar(100)') = @Fetch
             ) B


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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download