好的,现在 XML 是有效的......
连同您的信息其他问题 https://stackoverflow.com/q/40080985/5089204我建议这种方法:
我将您的 XML 放入已声明的变量中
declare @xml xml=
N'<NewAttributeRules>
<items>
<NewAttributeItem>
<Scale>CAAA</Scale>
<ScaleName>OC07</ScaleName>
<comment />
<positiveRules>
<NewAttributeRule type="POSITIVE">
<conditions>
<InCondition column="PPRD" colDataType="STRING">
<values>
<string>CAAAEXTENDED</string>
</values>
</InCondition>
</conditions>
</NewAttributeRule>
</positiveRules>
<negativeRules />
</NewAttributeItem>
<NewAttributeItem>
<Scale>high TOTAL OTHERS</Scale>
<ScaleName>b007</ScaleName>
<comment />
<positiveRules>
<NewAttributeRule type="POSITIVE">
<conditions>
<InCondition column="ATC3" colDataType="STRING">
<values>
<string>B10787 EXT</string>
</values>
</InCondition>
</conditions>
</NewAttributeRule>
</positiveRules>
<negativeRules>
<NewAttributeRule type="NEGATIVE">
<conditions>
<InCondition column="PPRD" colDataType="STRING">
<values>
<string>hkJKKK</string>
<string>GAGHA</string>
</values>
</InCondition>
</conditions>
</NewAttributeRule>
</negativeRules>
</NewAttributeItem>
<NewAttributeItem>
<Scale>***XXX***</Scale>
<ScaleName>OC07</ScaleName>
<comment />
<positiveRules />
<negativeRules />
</NewAttributeItem>
<NewAttributeItem>
<Scale>***XXX***</Scale>
<ScaleName>OC07</ScaleName>
<comment />
<positiveRules />
<negativeRules />
</NewAttributeItem>
<NewAttributeItem>
<Scale>***XXX***</Scale>
<ScaleName>b007</ScaleName>
<comment />
<positiveRules />
<negativeRules />
</NewAttributeItem>
<NewAttributeItem>
<Scale>***XXX***</Scale>
<ScaleName>b007</ScaleName>
<comment />
<positiveRules />
<negativeRules />
</NewAttributeItem>
</items>
</NewAttributeRules>';
--和之前一样,CTE会读取ScaleName用于后面的分组,但是会让整个节点as is
WITH ScaleNames AS
(
SELECT ai.query('.') AS AiNode
,ai.value('(ScaleName)[1]','nvarchar(100)') AS ScaleName
FROM @xml.nodes('/NewAttributeRules/items/NewAttributeItem') AS A(ai)
WHERE ai.value('(Scale)[1]','nvarchar(100)')<>'***XXX***'
)
--此 SELECT 将使用现有节点重建整个 XML,并添加两倍的节点XXX nodes.
SELECT
(
SELECT (
SELECT x.AiNode AS [node()]
FROM ScaleNames AS x
WHERE x.ScaleName=ScaleNames.ScaleName
FOR XML PATH(''),TYPE
) AS [node()]
,(SELECT
(SELECT '***XXX***' AS Scale, ScaleName, '' AS comment, '' AS positiveRules, '' AS negativRules FOR XML PATH('NewAttributeItem'),TYPE )
,(SELECT '***XXX***' AS Scale, ScaleName, '' AS comment, '' AS positiveRules, '' AS negativRules FOR XML PATH('NewAttributeItem'),TYPE)
FOR XML PATH(''),TYPE
) AS [node()]
FROM ScaleNames
GROUP BY ScaleName
ORDER BY ScaleName
FOR XML PATH(''),ROOT('items'),TYPE
)
FOR XML PATH(''),ROOT('NewAttributeRules')
结果
<NewAttributeRules>
<items>
<NewAttributeItem>
<Scale>high TOTAL OTHERS</Scale>
<ScaleName>b007</ScaleName>
<comment />
<positiveRules>
<NewAttributeRule type="POSITIVE">
<conditions>
<InCondition column="ATC3" colDataType="STRING">
<values>
<string>B10787 EXT</string>
</values>
</InCondition>
</conditions>
</NewAttributeRule>
</positiveRules>
<negativeRules>
<NewAttributeRule type="NEGATIVE">
<conditions>
<InCondition column="PPRD" colDataType="STRING">
<values>
<string>hkJKKK</string>
<string>GAGHA</string>
</values>
</InCondition>
</conditions>
</NewAttributeRule>
</negativeRules>
</NewAttributeItem>
<NewAttributeItem>
<Scale>***XXX***</Scale>
<ScaleName>b007</ScaleName>
<comment />
<positiveRules />
<negativRules />
</NewAttributeItem>
<NewAttributeItem>
<Scale>***XXX***</Scale>
<ScaleName>b007</ScaleName>
<comment />
<positiveRules />
<negativRules />
</NewAttributeItem>
<NewAttributeItem>
<Scale>CAAA</Scale>
<ScaleName>OC07</ScaleName>
<comment />
<positiveRules>
<NewAttributeRule type="POSITIVE">
<conditions>
<InCondition column="PPRD" colDataType="STRING">
<values>
<string>CAAAEXTENDED</string>
</values>
</InCondition>
</conditions>
</NewAttributeRule>
</positiveRules>
<negativeRules />
</NewAttributeItem>
<NewAttributeItem>
<Scale>***XXX***</Scale>
<ScaleName>OC07</ScaleName>
<comment />
<positiveRules />
<negativRules />
</NewAttributeItem>
<NewAttributeItem>
<Scale>***XXX***</Scale>
<ScaleName>OC07</ScaleName>
<comment />
<positiveRules />
<negativRules />
</NewAttributeItem>
</items>
</NewAttributeRules>