SQL 字符串操作以添加 xml 节点 [英] SQL string manipulation to add xml nodes

问题描述

我使用的是 sql 2008R2，该表具有 xml 列，例如-

I am using sql 2008R2, The table is having xml column like-

<New>
   <From>
        <Scale>Tony</Scale>
        <ScaleName>Name</ScaleName>
    </From>
</New>
<New>
    <From>
        <Scale>Tom</Scale>
        <ScaleName>Name</ScaleName>
    </From>
</New>

 <New>
    <From>
        <Scale>Seven</Scale>
        <ScaleName>Height</ScaleName>
    </From>
</New>
<New>
    <From>
        <Scale>Ten</Scale>
        <ScaleName>Height</ScaleName>
    </From>
</New>
<New>
    <From>
        <Scale>***XXX***</Scale>
        <ScaleName>Height</ScaleName>
   </From>
</New>

.......等等

我需要编写一个 SQL 来检查所有 ScaleName 的 Scale 没有以 XXX 作为值的节点，然后添加/插入以下文本，为 2次.当只有一个 ***XXX**** 条目时，它应该只添加/插入一次

I need to write a SQL which can check all the nodes where the ScaleName's Scale is not havng XXX as value and then add/insert the following text, for 2 times. When there is only one ***XXX**** entry it should add/insert only one time

<New>
    <From>
        <Scale>***XXX***</Scale>
        <ScaleName>Respective Scalename</ScaleName>
    </From>
</New>  

预期结果 -----

<New>
   <From>
        <Scale>Tony</Scale>
        <ScaleName>Name</ScaleName>
    </From>

<New>
    <From>
        <Scale>Tom</Scale>
        <ScaleName>Name</ScaleName>
    </From>
</New>
<New>
   <From>
        <Scale>***XXX***</Scale>
        <ScaleName>Name</ScaleName>
    </From>
</New>
<New>

   <From>
        <Scale>***XXX***</Scale>
        <ScaleName>Name</ScaleName>
    </From>
</New>


 <New>
    <From>
        <Scale>Seven</Scale>
        <ScaleName>Height</ScaleName>
    </From>
 </New>
 <New>
    <From>
        <Scale>Ten</Scale>
        <ScaleName>Height</ScaleName>
    </From>
 </New>
 <New>
    <From>
        <Scale>***XXX***</Scale>
        <ScaleName>Height</ScaleName>
   </From>
 </New>
 <New>
    <From>
        <Scale>***XXX***</Scale>
        <ScaleName>Height</ScaleName>
    </From>
</New>

推荐答案

我不知道我是否完全理解您的需求，但这可能会有所帮助:

I do not know whether I fully understood what you need, but this might help:

注意:这是 - 在大多数情况下！- 使用魔法值的坏主意，例如***XXX***...

attention: It is - in most cases! - a bad idea to work with magic values such as ***XXX***...

这是您的示例 XML.比例 Name 没有 ***XXX*** 条目，比例 height 有一个...

This is your example XML. The scale Name has no ***XXX*** entry and scale height has got one...

DECLARE @xml XML=
(N'<New>
  <From>
    <Scale>Tony</Scale>
    <ScaleName>Name</ScaleName>
  </From>
</New>
<New>
  <From>
    <Scale>Tom</Scale>
    <ScaleName>Name</ScaleName>
  </From>
</New>
<New>
  <From>
    <Scale>Seven</Scale>
    <ScaleName>Height</ScaleName>
  </From>
</New>
<New>
  <From>
    <Scale>Ten</Scale>
    <ScaleName>Height</ScaleName>
  </From>
</New>
<New>
  <From>
    <Scale>***XXX***</Scale>
    <ScaleName>Height</ScaleName>
  </From>
</New>');

--CTE 将 XML 读入派生表，其中省略了带有 ***XXX***

--The CTE reads the XML into a derived table ommiting the entries with ***XXX***

WITH ScaleNames AS
(
    SELECT  fr.value('(Scale)[1]','nvarchar(100)') AS Scale
           ,fr.value('(ScaleName)[1]','nvarchar(100)') AS ScaleName
    FROM @xml.nodes('/New/From') AS A(fr)
    WHERE fr.value('(Scale)[1]','nvarchar(100)')<>'***XXX***'
)

--此 SELECT 将使用 real 值重建整个 XML，并添加两倍的 ***XXX*** 节点.

--This SELECT will rebuild the whole XML using the real values and adding two times the ***XXX*** nodes.

SELECT (
            SELECT x.Scale AS [From/Scale]
                  ,x.ScaleName AS [From/ScaleName]
            FROM ScaleNames AS x
            WHERE x.ScaleName=ScaleNames.ScaleName
            FOR XML PATH('New'),TYPE
       )
      ,(SELECT
        (SELECT '***XXX***' AS Scale, ScaleName FOR XML PATH('From'),ROOT('New'),TYPE )
        ,(SELECT '***XXX***' AS Scale, ScaleName FOR XML PATH('From'),ROOT('New'),TYPE )
        FOR XML PATH(''),TYPE
       ) AS [node()]
FROM ScaleNames
GROUP BY ScaleName
FOR XML PATH('')

结果

<New>
  <From>
    <Scale>Seven</Scale>
    <ScaleName>Height</ScaleName>
  </From>
</New>
<New>
  <From>
    <Scale>Ten</Scale>
    <ScaleName>Height</ScaleName>
  </From>
</New>
<New>
  <From>
    <Scale>***XXX***</Scale>
    <ScaleName>Height</ScaleName>
  </From>
</New>
<New>
  <From>
    <Scale>***XXX***</Scale>
    <ScaleName>Height</ScaleName>
  </From>
</New>
<New>
  <From>
    <Scale>Tony</Scale>
    <ScaleName>Name</ScaleName>
  </From>
</New>
<New>
  <From>
    <Scale>Tom</Scale>
    <ScaleName>Name</ScaleName>
  </From>
</New>
<New>
  <From>
    <Scale>***XXX***</Scale>
    <ScaleName>Name</ScaleName>
  </From>
</New>
<New>
  <From>
    <Scale>***XXX***</Scale>
    <ScaleName>Name</ScaleName>
  </From>
</New>

这篇关于SQL 字符串操作以添加 xml 节点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！