代码之家 › 专栏 › 技术社区 › Rudolf Lamprecht

如何使用XQuery基于属性过滤XML数据

flwor sql-server-2014-express xquery xpath tsql

Rudolf Lamprecht · 技术社区 · 6 年前

鉴于下面的XML结构,我需要过滤掉所有 question <questionSubType/> 值等于 ABC ,以及谁的 <option subType=""/> 属性等于 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>

我尝试了以下操作,但这只根据 <问题子类型/> 值,因为我不确定如何继续查询 <option/> 节点:

        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]

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

任何帮助都将不胜感激。

1 回复 | 直到 6 年前

Gottfried Lesigang 6 年前

这是 XQuery 为了拯救你:

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 @subType varchar(5) = '001'
           ,@questionSubType varchar(5) = 'ABC';

--那个将遍历XML并添加具有给定类型的所有问题,然后添加除 <options> . 最后一个节点将使用筛选器谓词再次添加:

 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> 
 ');