Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to get a particular attribute from XML element in SQL Server

I have something like the following XML in a column of a table:

<?xml version="1.0" encoding="utf-8"?> <container>   <param name="paramA" value="valueA" />   <param name="paramB" value="valueB" />   ... </container> 

I am trying to get the valueB part out of the XML via TSQL

So far I am getting the right node, but now I can not figure out how to get the attribute.

select xmlCol.query('/container/param[@name="paramB"]') from LogTable 

I figure I could just add /@value to the end, but then SQL tells me attributes have to be part of a node. I can find a lot of examples for selecting the child nodes attributes, but nothing on the sibling atributes (if that is the right term).

Any help would be appreciated.

like image 813
My Other Me Avatar asked Oct 13 '09 15:10

My Other Me


1 Answers

Try using the .value function instead of .query:

SELECT    xmlCol.value('(/container/param[@name="paramB"]/@value)[1]', 'varchar(50)')  FROM     LogTable 

The XPath expression could potentially return a list of nodes, therefore you need to add a [1] to that potential list to tell SQL Server to use the first of those entries (and yes - that list is 1-based - not 0-based). As second parameter, you need to specify what type the value should be converted to - just guessing here.

Marc

like image 139
marc_s Avatar answered Oct 01 '22 19:10

marc_s