本文介紹了使用 SQL 編輯 XML 列.不那么結構化的 XML的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學習吧！
問題描述
限時送ChatGPT賬號..
如何通過獲取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的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！
【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題，如果有圖片或者內容侵犯了您的權益，請聯系我們刪除處理，感謝您的支持！