Print XML attribute in SQL Server

Question

I am trying to get value of an attribute from XML in SQL table

<?xml version="1.0" encoding="utf-8"?>
<container>
  <Property Name="paramA" Vocabulary="someVocu">
  <Property Name="paramB" Value="valueA" />
  <Property Name="paramC" Value="valueB" />
  </Property>
  <Property Name="paramA" Vocabulary="anotherVocu">
  <Property Name="paramB" Value="valueY" />
  <Property Name="paramC" Value="valueZ" />
  </Property>
</container>


select x.XmlCol.value('(Property[@Name="paramB"]/@Value)[1]', 'varchar(50)')    from tempTbl CROSS APPLY rawxml.nodes('/container') AS x(XmlCol)

I am trying to print "valueA" and "valueY" I am getting a NULL.

How can I do this?

Thanks

Answer 1

I cannot tell you what in particular is wrong with your statement since I am still learning how to query XML too. I was able to come up with some SQL which I think should work for by referencing this question...

How to query for Xml values and attributes from table in SQL Server?

Here it is.

CREATE TABLE tempTbl
(
    id INT,
    data XML
)

INSERT INTO dbo.tempTbl
        (id, data)
SELECT 1, '<?xml version="1.0" encoding="utf-8"?>
<container>
  <Property Name="paramA" Vocabulary="someVocu">
  <Property Name="paramB" Value="valueA" />
  <Property Name="paramC" Value="valueB" />
  </Property>
  <Property Name="paramA" Vocabulary="anotherVocu">
  <Property Name="paramB" Value="valueY" />
  <Property Name="paramC" Value="valueZ" />
  </Property>
</container>'

SELECT 
x.XmlCol.value('@Value', 'varchar(25)') AS Value
FROM tempTbl AS t
CROSS APPLY t.data.nodes('/container/Property/Property') AS x(XmlCol)
WHERE x.XmlCol.value('@Name', 'varchar(25)') = 'paramB'

To learn more about querying XML I am working through Stairway to XML.

Noel