SQL string manipulation to add xml nodes

问题:

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>

.......so on

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>  

EXPECTED RESULT -----

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

回答1:

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

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

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

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

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

The result

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