使用 SQL 编辑 XML 列.不那么结构化的 XML

2021-10-01 00:00:00 xml xpath tsql xquery sql-server

如何通过获取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>

相关文章