How to get a particular attribute from XML element in SQL Server
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
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 vwLogTableASSELECT c.p.value('@name', 'varchar(10)') name, c.p.value('@value', 'varchar(10)') valueFROM 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'