T-SQL, get value from xml in a column-CodePudding

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

CodePudding user response：

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].

CodePudding user response：

This should work:

SELECT
    xmlCol.value('(//factor[@name="Rivers"]/value/@value)[1]', 'int') As RiverID
FROM
    MyTable

value() Method (xml Data Type) - SQL Server | Microsoft Docs
XQuery Language Reference (SQL Server) - SQL Server | Microsoft Docs