Write a SQL Server query to modify XML tag not value-CodePudding

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

CodePudding user response：

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;

CodePudding user response：

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