Getting value from XML/Node out of SQL table

• RedBirdOBX

  SSCommitted

  Points: 1836

  More actions

  May 16, 2014 at 12:38 pm

  #307466

  Hello all. I have a table which has a field with a data type of Xml. A sample record might contain something like this:

  <?xml version="1.0" encoding="utf-16"?>

  <order>

  <Product Index="14c9643f-63d9-40c5" ProductID="13" Name="............>

  <Attribute Name="Paper" OptCatId="1" Value="72" ....../>

  <Attribute Name="Ink" OptCatId="2" Value="6" ......../>

  <Attribute Name="Bleed" OptCatId="3" Value="15" ......./>

  <Attribute Name="Number of Drops" OptCatId="0" Value="1" ...../>

  I need to grab the Node/Attribute with the name of "Number of Drops" and get the value of "Value". So in this case, I simply want to get "1".

  How could I do this?

  SELECT "value of Number of Drops"

  FROM myTable

  WHERE ID = ....

  Thanks!!

• Eirikur Eiriksson

  SSC Guru

  Points: 182901

  More actions

  May 16, 2014 at 12:50 pm

  #1714306

  Take a look at the XML forum, the answer is there.

  BTW, it is more work to create a sample data than actually write the answer.

  😎