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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With