SQL string manipulation to add xml nodes

Question

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>

nd then add the text where it is not, for 2 times - show how should look the expected result — RomanPerekhrest
– RomanPerekhrest, Commented Oct 17, 2016 at 7:50
Is your XML really without a root element (not impossible, but not best choice...)? And please explain, what you mean with add/insert the following text, for 2 times. I do not see any following text and where/why/what do you want to insert two times? As @RomanPerekhrest asked already: Please provide the expected output and the code you have tried so far... — Gottfried Lesigang
– Gottfried Lesigang, Commented Oct 17, 2016 at 8:06
add/insert means adding XML codes. This code is required for a java basd app. XXX entries need to be 2 times for each unique scalename — Priya
– Priya, Commented Oct 17, 2016 at 8:15
XXX entries = <New> <From> <Scale>***XXX***</Scale> <ScaleName>Respective Scalename</ScaleName> </From> </New> — Priya
– Priya, Commented Oct 17, 2016 at 8:16

Gottfried Lesigang · Accepted Answer · 2016-10-17 10:59:06Z

0

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>

answered Oct 17, 2016 at 10:59

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Priya Over a year ago

wow!!! thats supercool, I will check and update. regarding magic word, its a part of java frontend dropdown. Thank you so much.

Priya Over a year ago

by the way, is there any way to get it done by reading xml as string. just in case

Gottfried Lesigang Over a year ago

@Priya, glad to help you! If you like this, it would be kind to tick the acceptance check below the answer's vote counter. About reading the XML as string: As long as the string contains valid XML it is simply done by DECLARE @xml XML=(SELECT CAST(@SomeValidXML AS XML)). It is best to use a variable of type NVARCHAR, but make sure, that the XML does not start with a declaration with utf-8. If you've got the XML as literal, it is best to add a N in front of the string to get it unicode (DECLARE @myXML NVARCHAR(MAX)=N'some xml here')

Priya Over a year ago

I am new here, any possibility to share ur feedback or rating please

Gottfried Lesigang Over a year ago

@Priya What do you mean with share ur feedback or rating ?

|

Collectives™ on Stack Overflow

SQL string manipulation to add xml nodes

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related