How to filter XML data based on an attribute using XQuery

Question

Given the below XML structure, I need to filter out all the question nodes whose <questionSubType/> value is equal to ABC, and whose <option subType=""/> attribute is equal to 001:

<questions>
  <question>
    <text>Some text</text>
    <questionType></questionType>
    <questionSubType>ABC</questionSubType>
    <options>
      <option subType="001">
        <text>Y</text>
        <mappedCodes>
          <code>1</code>
        </mappedCodes>
      </option>
      <option subType="001">
        <text>N</text>
        <mappedCodes>
          <code>2</code>
        </mappedCodes>
      </option>
      <option subType="002">
        <text>Y</text>
        <mappedCodes>
          <code>1</code>
        </mappedCodes>
      </option>
    </options>
  </question>
  <question>
    <text>Some more text</text>
    <questionType></questionType>
    <questionSubType>DEF</questionSubType>    
    <options>
      <option subType="001">
        <text>Single</text>
        <mappedCodes>
          <code>PL0157</code>
        </mappedCodes>
      </option>
      <option subType="001">
        <text>Married</text>
        <mappedCodes>
          <code>PD0241</code>
        </mappedCodes>
      </option>
      <option subType="002">
        <text>Single</text>
        <mappedCodes>
          <code>PL1157</code>
        </mappedCodes>
      </option>
      <option subType="002">
        <text>Married</text>
        <mappedCodes>
          <code>PD1241</code>
        </mappedCodes>
      </option>
    </options>
  </question>
  <question>
    <text>Some last text</text>
    <questionType></questionType>
    <questionSubType>ABC</questionSubType>
    <options>
      <option subType="001">
        <text>T</text>
        <mappedCodes>
          <code>2</code>
        </mappedCodes>
      </option>
      <option subType="002">
        <text>V</text>
        <mappedCodes>
          <code>2</code>
        </mappedCodes>
      </option>
    </options>
  </question>
 </questions>

I have attempted the following, but this only filters the XML based on the <questionSubType/> value as I am unsure how to proceed with querying the <option/> nodes:

        DECLARE
            @subType varchar(5) = '001'
          , @questionSubType varchar(5) = 'ABC'
        SET @XmlOutput = (
            SELECT
                1 as Tag 
              , null as Parent
              , CONVERT(nvarchar(max), F.N.query('./*')) as [question!1!!XML]
            FROM [MyTable] T
                CROSS APPLY T.[Configuration].nodes('//question') F(N)
            WHERE
                F.N.value('(//questionSubType/text())[1]', 'varchar(100)') = @questionSubType
            FOR XML EXPLICIT, ROOT('questions')
        )

        SELECT @XmlOutput as [Configuration]

So at the end, my output should look like this:

<questions>
  <question>
    <text>Some text</text>
    <questionType></questionType>
    <questionSubType>ABC</questionSubType>
    <options>
      <option subType="001">
        <text>Y</text>
        <mappedCodes>
          <code>1</code>
        </mappedCodes>
      </option>
      <option subType="001">
        <text>N</text>
        <mappedCodes>
          <code>2</code>
        </mappedCodes>
      </option>
    </options>
  </question>
  <question>
    <text>Some last text</text>
    <questionType></questionType>
    <questionSubType>ABC</questionSubType>
    <options>
      <option subType="001">
        <text>T</text>
        <mappedCodes>
          <code>2</code>
        </mappedCodes>
      </option>
    </options>
  </question>
 </questions>

Any help would be greatly appreciated.

Answer 1

Here is XQuery to your rescue:

DECLARE @xml XML=
N'<questions>
  <question>
    <text>Some text</text>
    <questionType></questionType>
    <questionSubType>ABC</questionSubType>
    <options>
      <option subType="001">
        <text>Y</text>
        <mappedCodes>
          <code>1</code>
        </mappedCodes>
      </option>
      <option subType="001">
        <text>N</text>
        <mappedCodes>
          <code>2</code>
        </mappedCodes>
      </option>
      <option subType="002">
        <text>Y</text>
        <mappedCodes>
          <code>1</code>
        </mappedCodes>
      </option>
    </options>
  </question>
  <question>
    <text>Some more text</text>
    <questionType></questionType>
    <questionSubType>DEF</questionSubType>    
    <options>
      <option subType="001">
        <text>Single</text>
        <mappedCodes>
          <code>PL0157</code>
        </mappedCodes>
      </option>
      <option subType="001">
        <text>Married</text>
        <mappedCodes>
          <code>PD0241</code>
        </mappedCodes>
      </option>
      <option subType="002">
        <text>Single</text>
        <mappedCodes>
          <code>PL1157</code>
        </mappedCodes>
      </option>
      <option subType="002">
        <text>Married</text>
        <mappedCodes>
          <code>PD1241</code>
        </mappedCodes>
      </option>
    </options>
  </question>
  <question>
    <text>Some last text</text>
    <questionType></questionType>
    <questionSubType>ABC</questionSubType>
    <options>
      <option subType="001">
        <text>T</text>
        <mappedCodes>
          <code>2</code>
        </mappedCodes>
      </option>
      <option subType="002">
        <text>V</text>
        <mappedCodes>
          <code>2</code>
        </mappedCodes>
      </option>
    </options>
  </question>
 </questions>';

--declare your variables

    DECLARE @subType varchar(5) = '001'
           ,@questionSubType varchar(5) = 'ABC';

--The XQuery will run through your XML and add all questions with the given type, then all there internal nodes except <options>. This last node is added again with a filter predicate:

 SELECT @xml.query
 ('<questions>
   {
    for $q in /questions/question[(questionSubType/text())[1]=sql:variable("@questionSubType")]
    return 
        <question>
        {
        $q/*[local-name()!="options"]
        }
        {
        $q/options/option[@subType=sql:variable("@subType")]
        }
        </question>
   } 
   </questions> 
 ');