CODEWITHSUNDEEP
sqlsql-serverxmlxpathxquery
Colin Edgar
Colin Edgar

Reputation: 33

SQL Select: Take value from XML field

I'm struggling to pull a value out of an XML field using SQL Select Statement.

The XML field is Info, the table is results.

<Screens>
  <Results>
    <Result ID="54722094-8b36-4a01-b089-3ecabebbf962" DataResponse="" 
          DataAction="" DataValue="2" DataScore="0" />
  </Results>
</Screens>

I need to pull the attribute DataValue. For the record above, I'd be looking for 2.

Any help would be greatly appreciated.

Upvotes: 3

Views: 111

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

With SQL Server it was this:

DECLARE @xml XML=
'<Screens>
  <Results>
    <Result ID="54722094-8b36-4a01-b089-3ecabebbf962" DataResponse="" 
          DataAction="" DataValue="2" DataScore="0" />
  </Results>
</Screens>';

SELECT @xml.value('(/Screens/Results/Result/@DataValue)[1]','int') AS DataValue

EDIT: Data comes from a table

Just replace my @tbl with your actual table

--test scenario with two data rows, one has DataValue=2 the other =99
DECLARE @tbl TABLE(ID INT IDENTITY,info XML);
INSERT INTO @tbl(info) VALUES
 (
    '<Screens>
      <Results>
        <Result ID="54722094-8b36-4a01-b089-3ecabebbf962" DataResponse="" 
              DataAction="" DataValue="2" DataScore="0" />
      </Results>
    </Screens>'
 )
,(
    '<Screens>
      <Results>
        <Result ID="54722094-8b36-4a01-b089-3ecabebbf962" DataResponse="" 
              DataAction="" DataValue="99" DataScore="0" />
      </Results>
    </Screens>'
 );

--this is the query
SELECT info.value('(/Screens/Results/Result/@DataValue)[1]','int') AS DataValue
FROM @tbl

The result is 2 and 99

Upvotes: 2

Related Questions