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
SELECT xmlCol.value('(/container/param[@name="paramB"]/@value)', 'varchar(50)') FROM LogTable
The XPath expression could potentially return a list of nodes, therefore you need to add a
 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.
Depending on the the actual structure of your xml, it may be useful to put a view over it to make it easier to consume using 'regular' sql eg
CREATE VIEW vwLogTable AS SELECT c.p.value('@name', 'varchar(10)') name, c.p.value('@value', 'varchar(10)') value FROM LogTable CROSS APPLY x.nodes('/container/param') c(p) GO -- now you can get all values for paramB as... SELECT value FROM vwLogTable WHERE name = 'paramB'