T-SQL, get value from xml in a column

Question

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

Richard Deeming · Accepted Answer · 2021-12-09 15:34:13Z

2

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

edited Dec 9, 2021 at 15:34

answered Dec 9, 2021 at 15:29

Richard Deeming

31.5k11 gold badges86 silver badges167 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Richard Deeming Over a year ago

@Austin Good spot! Fixed.

Charlieface Over a year ago

/criteria/factor will be faster than //factor

user12571241 · Accepted Answer · 2021-12-09 15:29:36Z

1

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

answered Dec 9, 2021 at 15:29

user12571241

Collectives™ on Stack Overflow

T-SQL, get value from xml in a column

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related