本文介绍了使用 SQL 编辑 XML 列.不那么结构化的 XML的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着跟版网的小编来一起学习吧！
问题描述
如何通过获取XXX"的新标签来编辑 XML，而不是结构化的 xml.需要帮助，我对 XML 和 XQuery 非常陌生.如果 X 没有，则必须为 X 获取新标签(节点)，在 1 的情况下只需要插入 1.有没有办法在更大范围内操作字符串
How to edit XML by getting new tags for 'XXX', not so structured xml. Need help, I am very new to XML and XQuery. Have to get new tags (nodes) for X if it is none, in case of 1 need to insert 1 only. is there any way to manipulate string on larger scale
<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>
推荐答案
好的，现在 XML 是有效的...
Okay, now the XML is valid...
连同来自您的其他问题的信息，我建议采用这种方法:
Together with the information form your other question I'd suggest this approach:
我把你的 XML 放入一个声明的变量中
I put your XML into a declared variable
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 before, the CTE will read the ScaleName for later grouping, but will let the whole node 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 节点.
--This SELECT will rebuild the whole XML using the existing nodes and adding two times the 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>
这篇关于使用 SQL 编辑 XML 列.不那么结构化的 XML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！
本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益，请联系我们，我们会在确认后第一时间进行删除！