CODEWITHSUNDEEP
sqlxmlt-sql
mike
mike

Reputation: 11

T-SQL, get value from xml in a column

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

Upvotes: 1

Views: 223

Answers (2)

Richard Deeming
Richard Deeming

Reputation: 31198

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

Upvotes: 2

Austin
Austin

Reputation: 2265

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

Upvotes: 1

Related Questions