SQL 2008 retrieving attribute values from an XML column

Question

Specifically, given the following XML content in a column called metadata, how do I retrieve the value of nodes with a specific name attribute?

In this case I'm after the value associated with the int16_t tag and a name of 'filterIndex'

<root version="1">
  <class derived_type="FilterInfo" name="FilterInfo">
    <string name="filterName" length="3" value="mt8" />
    <string name="filterText" length="3" value="SVR" />
    <string name="filterTable" length="11" value="CML_5_1_mt8" />
    <bool name="userDefined" value="true" />
    <bool name="multiResp" value="false" />
    <enumeration name="filterType" value="1" />
    <enumeration name="filterCategory" value="1" />
    <vector name="options" count="1">
      <class derived_type="OptionInfo" name="V0">
        <int16_t name="optionIndex" value="1" />
        <string name="optionName" length="3" value="mt8" />
        <string name="optionText" length="3" value="SVR" />
        <string name="optionTable" length="11" value="CML_5_1_mt8" />
      </class>
    </vector>
    <GUID name="primaryKey" value="fe66b60f-468a-4f0e-be80-0055e20baa35" />
    <int16_t name="filterIndex" value="1080" />
  </class>
</root>

Thx++

Jerry.

score 5 · Accepted Answer · answered Mar 07 '11 at 15:42

5

Something like this??

SELECT
    metadata.value('(/root/class/int16_t/@value)[1]', 'int') AS 'int16_value'
FROM
    dbo.YourTable

answered Mar 07 '11 at 15:42

marc_s

732,580
175
1,330
1,459

That's convenient because there's only one element with the tagname `int16_t` in that query path. What about if you wanted the `string` element whose `name` attribute == filterName? – Matt Sach Dec 03 '12 at 11:25

SQL 2008 retrieving attribute values from an XML column

1 Answers1