0

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
Charlieface
  • 52,284
  • 6
  • 19
  • 43
Jot Singh Dhaliwal
  • 1
  • 1
  • 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](https://learn.microsoft.com/en-us/sql/t-sql/xml/replace-value-of-xml-dml?view=sql-server-ver15) page in the docs – Panagiotis Kanavos Feb 09 '22 at 10:25
  • @PanagiotisKanavos I want to change the whole node node , value will be inside opening and closing tag, – Jot Singh Dhaliwal Feb 09 '22 at 10:32
  • 1
    How do you know which `widget` node to change? – Charlieface Feb 09 '22 at 10:33
  • @Charlieface first widget need to be changed , which have value <![CDATA[All]]> – Jot Singh Dhaliwal Feb 09 '22 at 10:45
  • You can use X.modify or do the casing in variable. For reference https://stackoverflow.com/questions/50311535/replace-value-in-xml-using-sql – user15367200 Feb 09 '22 at 11:36

2 Answers2

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

Charlieface
  • 52,284
  • 6
  • 19
  • 43
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;
SQLpro
  • 3,994
  • 1
  • 6
  • 14