T-SQL, get value from xml in a column

user_17637002 0
Viewed 77

I have a table with an XML column (xmlCol) and I am trying to query a value from it.

Here's the xml.

<criteria>
  <factor name="Rivers" title="Rivers">
    <value dataType="Int32" value="1743" description="Wilson0" />
  </factor>
  <factor name="OptionalAffProperties" title="Include properties">
    <value dataType="String" value="FishingModel" description="Fishing Model" />
  </factor>
</criteria>

Here is a select to get the column. select xmlCol from MyTable

I am trying to return the value 1743 to a column called RiverID.

Mike

xml tsql sql
2 Answers
user_1243860

To get the attribute value, you can query it like so:

select xmlCol.value('(/criteria/factor/value/@value)[1]', 'int') RiverID
from MyTable

You provide the xml path to the record you are looking for: (/criteria/factor/value

And then the attribute you need: /@value)[1].

user_125712410
Related