CODEWITHSUNDEEP
xmlsql-server-2008replacexquery-sql
Raj
Raj

Reputation: 23

replacing special characters in attribute values for xml

I need to update xml column in a table to remove the special char(%) for a particular node Rating Ratingtype="unknown". Special characters present in nodes other than mentioned above should not change. Below are my input and desired results. 

            Input xml:
            <Ratings>
              <Rating Ratingtype="unknown">
                <AppliedKnowledge value="Yes%">3.0</AppliedKnowledge>
                <ToolSkills>3.5</ToolSkills>
              </Rating>
              <Rating Ratingtype="known">
              <AppliedKnowledge value="%Yes%">3.0</AppliedKnowledge>
                <Experience>9.5</Experience>
                <Education>16.0</Education>
                <DbDevelopment>4.5</DbDevelopment>
              </Rating>
              <Rating Ratingtype="unknown">
                <AppliedKnowledge value="%No%">4.0</AppliedKnowledge>
                <ToolSkills>4.5</ToolSkills>
              </Rating>
            </Ratings>

            Expected output xml:
            <Ratings>
              <Rating Ratingtype="unknown">
                <AppliedKnowledge value="Yes">3.0</AppliedKnowledge>
                <ToolSkills>3.5</ToolSkills>
              </Rating>
              <Rating Ratingtype="known">
              <AppliedKnowledge value="%Yes%">3.0</AppliedKnowledge>
                <Experience>9.5</Experience>
                <Education>16.0</Education>
                <DbDevelopment>4.5</DbDevelopment>
              </Rating>
              <Rating Ratingtype="unknown">
                <AppliedKnowledge value="No">4.0</AppliedKnowledge>
                <ToolSkills>4.5</ToolSkills>
              </Rating>
            </Ratings>

Upvotes: 1

Views: 745

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

Similar to your last question, there is no easy going.

The .modify() method will allow one change per call. This means you'd have to work with some kind of loop.

Still there are approaches:

If the % is not used anywhere else you might simply do this:

Cast the XML to VARCHAR, do a simple text replace and cast it back

SELECT CAST(REPLACE(CAST(@YourXml AS VARCHAR(MAX)),'%','')  AS XML)

Better than the loop would be this

Shred the XML down and build it up again. Use the REPLACE on the target value only:

DECLARE @x XML=
'<Ratings>
  <Rating Ratingtype="unknown">
    <AppliedKnowledge value="Yes%">3.0</AppliedKnowledge>
    <ToolSkills>3.5</ToolSkills>
  </Rating>
  <Rating Ratingtype="known">
    <AppliedKnowledge value="%Yes%">3.0</AppliedKnowledge>
    <Experience>9.5</Experience>
    <Education>16.0</Education>
    <DbDevelopment>4.5</DbDevelopment>
  </Rating>
  <Rating Ratingtype="unknown">
    <AppliedKnowledge value="%No%">4.0</AppliedKnowledge>
    <ToolSkills>4.5</ToolSkills>
  </Rating>
</Ratings>';

SELECT CAST(REPLACE(CAST(@x AS VARCHAR(MAX)),'%','')  AS XML)

SELECT R.value('@Ratingtype','varchar(max)') AS [@Ratingtype]
      ,CASE WHEN R.value('@Ratingtype','varchar(max)')='unknown'
            THEN REPLACE(R.value('(AppliedKnowledge/@value)[1]','varchar(max)'),'%','')
            ELSE R.value('(AppliedKnowledge/@value)[1]','varchar(max)') END AS [AppliedKnowledge/@Value]
      ,R.value('AppliedKnowledge[1]','varchar(max)') AS [AppliedKnowledge]
      ,R.value('ToolSkills[1]','varchar(max)') AS [ToolSkills]
      ,R.value('Experience[1]','varchar(max)') AS [Experience]
      ,R.value('Education[1]','varchar(max)') AS [Education]
      ,R.value('DbDevelopment[1]','varchar(max)') AS [DbDevelopment]
FROM @x.nodes('/Ratings/Rating') AS A(R)
FOR XML PATH('Rating'),ROOT('Ratings')

The result 

<Ratings>
  <Rating Ratingtype="unknown">
    <AppliedKnowledge Value="Yes">3.0</AppliedKnowledge>
    <ToolSkills>3.5</ToolSkills>
  </Rating>
  <Rating Ratingtype="known">
    <AppliedKnowledge Value="%Yes%">3.0</AppliedKnowledge>
    <Experience>9.5</Experience>
    <Education>16.0</Education>
    <DbDevelopment>4.5</DbDevelopment>
  </Rating>
  <Rating Ratingtype="unknown">
    <AppliedKnowledge Value="No">4.0</AppliedKnowledge>
    <ToolSkills>4.5</ToolSkills>
  </Rating>
</Ratings>

Upvotes: 1

Related Questions