Edit XML column using SQL . Not so structured XML

问题:

This question is a Follow-Up to this question

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>

回答1:

Okay, now the XML is valid...

Together with the information form your other question I'd suggest this approach:

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

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

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

The result

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