How do I pick a value from XML column in a table with a condition. XML form contains different page

I have a xml data column. The XML data contains a form with different page headers. Each page has radio type values. I need to pick the value from one page and its values.

I need the value 'id' from Page topic='Outreach' under that, Question value="Type of Outreach", in that i need the id="1" value and also i need the id value from Question value="Reason for Assessment".

Below is the sample xml data.

<Test name="Assessment " header="" logoimg="" background="" icon="" firstPage="1" allow_abort="1" is_um="0" is_hidden="0" do_only="1" status="1" locale_id="0" version="74" category_id="150011" maxQuestionID="37" id="10234" force_start="0" user_name=" ggg bbb">
  <PAGE topic="Outreach " progress="5" background="" headerid="" footerid="" flush_concepts="1" lastPage="false" id="1">
    <BRANCH_CONDITION>
      <NEXT_PAGE id="2" />
    </BRANCH_CONDITION>
    <QUESTION value="Type of Outreach " defaultvalue="" style="" tooltip="" read_only="0" usability="5" prefer_concept="0" type="radio" validation="0" id="1" pagename="Outreach ">
      <OPTIONS>
        <OPTION value="Outbound Telephone " id="1" url="" score="0" />
        <OPTION value="Inbound Telephone" id="2" url="" score="0" />
        <OPTION value="In Person" id="3" url="" score="0" />
      </OPTIONS>
    </QUESTION>
    <QUESTION value="Reason for Assessment " defaultvalue="" style="" tooltip="" read_only="0" usability="5" prefer_concept="0" type="radio" validation="0" id="2" pagename="Outreach ">
      <OPTIONS>
        <OPTION value="New" id="1" url="" score="0" />
        <OPTION value="Annual" id="2" url="" score="0" />
        <OPTION value="Triggering Event " id="3" url="" score="0" />
        <OPTION value="LOC Review" id="4" url="" score="0" />
      </OPTIONS>
    </QUESTION>

I tried the following sql.

 SELECT TABLE.META_DATA.value('(/QUESTION//id/node())[1]','int') as outReach
        ,TABLE.META_DATA.value('(/QUESTION//id/node())[1]','int') as ReasonForAssessment
 FROM   Table

Below is my expected result

+----------+---------------------+
| Outreach | ReasonForAssessment |
+----------+---------------------+
|     1    |          2          |

Solution

Several issues:

QUESTION is not the root of the XML so you must use // or walk down tree from root: /Test/PAGE/QUESTION/.
Adjust XPath to search by attribute with @ prefix and not node search as id is an attribute (not child node) of QUESTION.
Adjust node index, [#], to return the corresponding @id attribute value.

Adjusted SQL:

SELECT TABLE.META_DATA.value('(//QUESTION/@id)[1]','int') as outReach
       , TABLE.META_DATA.value('(//QUESTION/@id)[2]','int') as ReasonForAssessment
FROM Table;

-- outReach ReasonForAssessment
--        1                   2

Alternatively use bracketed conditionals for columns:

SELECT TABLE.META_DATA.value('(/Test/PAGE/QUESTION[@value=''Type of Outreach '']/@id)[1]','int') as outReach
        , TABLE.META_DATA.value('(/Test/PAGE/QUESTION[@value=''Reason for Assessment '']/@id)[1]','int') as ReasonForAssessment
FROM Table;

-- outReach ReasonForAssessment
--        1                   2