40

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
My Other Me
  • 5,007
  • 6
  • 41
  • 48

2 Answers2

75

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • I have dynamic nodes in the field like By using the your give query I am able to read according to index. Means When I'll give index as a 1, it will display 2 and when i'll give index 2, it will display 5 but I requires both nodes. so would you please suggest me how to do it ! – pixelbyaj Sep 13 '13 at 07:34
  • I am having trouble using this for a boolean. I tried using 'boolean' and 'System.Boolean' as second params and received an error. If i do varchar it returns all nulls. My xml line looks like: – Zac Jan 06 '15 at 16:32
  • 1
    @Zeb: SQL Server doesn't have a `boolean` type - it has `BIT` - or you might just need to read it as an `INT` and then process it further – marc_s Jan 06 '15 at 16:45
  • what if , we are not sure about path of attribute in xml. but sure about xml tag.? means i have xml column but each xml is different and i am sure about my xml contains tags then how can we take the value of it..? – Rahul Chaudhari Jul 23 '21 at 09:28
11

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'
  • Why does xmlCol.value('(/container/param[@name="paramB"]/@value)[1]', 'varchar(50)') not work for me but just .value('@value', 'vharchar(50)') does work? Hmmmmm. – AndyClaw Aug 28 '13 at 18:58