给定以下 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>
我尝试了以下操作，但这仅根据<questionSubType/>值过滤 XML，因为我不确定如何继续查询<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>
任何帮助将不胜感激。
这是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';
--XQuery将遍历您的 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>
');
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句