Write a SQL Server query to modify XML tag not value

Question

In my table column I have a value like:

    <Config>        
        <Aidgets>
            <widget condition="true" typesenums="1,2" templatetypes="a,b">
                <![CDATA[All]]>
            </widget>
            <widget condition="true" typesenums="1" templatetypes="a" >
                <![CDATA[pdfprints]]>
            </widget>
         <Aidgets>
    </Config>

Here I have to replace the value of

<widget condition="true" typesenums="1,2" templatetypes="a,b">

with

<widget condition="true" typesenums="1,2,3" templatetypes="a,b,c">

I have tried this but not working

UPDATE column
SET table = REPLACE(CAST(table AS NVARCHAR(MAX)), "firstvalue", "newvalue")
WHERE id = 1

SQL Server has an XML type and XML functions. You can't modify XML with string replacements. You have to identify the correct element first, then modify its attrributes. Check the replace value of page in the docs — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Feb 9, 2022 at 10:25
@PanagiotisKanavos I want to change the whole node node , value will be inside opening and closing tag, — Jot Singh Dhaliwal
– Jot Singh Dhaliwal, Commented Feb 9, 2022 at 10:32
@Charlieface first widget need to be changed , which have value <![CDATA[All]]> — Jot Singh Dhaliwal
– Jot Singh Dhaliwal, Commented Feb 9, 2022 at 10:45
You can use X.modify or do the casing in variable. For reference stackoverflow.com/questions/50311535/… — user15367200
– user15367200, Commented Feb 9, 2022 at 11:36

SQLpro · Accepted Answer · 2022-02-09 11:02:27Z

0

CREATE TABLE T (ID INT, X XML);

INSERT INTO T VALUES (1, 
N'<Config>        
   <Aidgets>
     <widget condition="true" typesenums="1,2" templatetypes="a,b">
        <![CDATA[All]]>
     </widget>
     <widget condition="true" typesenums="1" templatetypes="a" >
        <![CDATA[pdfprints]]>
     </widget>
   </Aidgets>
</Config>');

UPDATE T
   SET X.modify('replace value of (/Config/Aidgets/widget/@typesenums)[1] with "1, 2, 3"');
UPDATE T
   SET X.modify('replace value of (/Config/Aidgets/widget/@templatetypes)[1] with "a, b, c"');

SELECT * FROM T;

answered Feb 9, 2022 at 11:02

SQLpro

5,3121 gold badge9 silver badges19 bronze badges

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

2 Comments

Jot Singh Dhaliwal Over a year ago

I have nText column dataType

SQLpro Over a year ago

So convert it in XML !

Charlieface · Accepted Answer · 2022-02-09 11:33:50Z

0

You can use two XQuery .modify updates to do this

UPDATE [column]
SET [table].modify('replace value of
    (Config/Aidgets/widget[text() = "All"]/@typesenums)[1]
    with "1,2,3"
    ');

UPDATE [column]
SET [table].modify('replace value of
    (Config/Aidgets/widget[text() = "All"]/@templatetypes)[1]
    with "a,b,c"
    ');

db<>fiddle

Alternatively, you can rebuild the whole XML instead.

UPDATE [column]
SET [table] = [table].query(
    '<Config>
    <Aidgets>
      {
        for $w in /Config/Aidgets/widget
        return
          if($w/text() = "All") then $w else
          <widget condition="true" typesenums="1,2,3" templatetypes="a,b,c">
            {$w/text()}
          </widget>
      }
    </Aidgets>
    </Config>'
)

db<>fiddle

edited Feb 9, 2022 at 11:33

answered Feb 9, 2022 at 11:01

Charlieface

78.6k8 gold badges35 silver badges77 bronze badges

2 Comments

Jot Singh Dhaliwal Over a year ago

I have ntext column dataType

Charlieface Over a year ago

Is there any reason you are not storing XML in an xml column? ntext is in any case deprecated, at the very least use nvarchar(max)

Collectives™ on Stack Overflow

Write a SQL Server query to modify XML tag not value

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related