SQL XML modify specific value with condition-CodePudding

I have the following XML in my SQL table and I want to modify the Flag from false to true only for CustomSettingValue that has Token value of "C"

    DECLARE @tbl TABLE(testXML XML)
    INSERT INTO @tbl (testXML)
    VALUES ('<CustomSettingValues xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
                     xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
                     xmlns="http://ABC.ABC.Interfaces/PInterfaceSettings.xsd">
  <CustomSettingValue>
    <Token>A</Token>
    <Display>true</Display>
    <Flag>true</Flag>
    <Required>false</Required>
    <SettingValue>0</SettingValue>
  </CustomSettingValue>
  <CustomSettingValue>
    <Token>B</Token>
    <Display>true</Display>
    <Flag>false</Flag>
    <Required>false</Required>
  </CustomSettingValue>
  <CustomSettingValue>
    <Token>C</Token>
    <Display>true</Display>
    <Flag>false</Flag>
    <Required>false</Required>
  </CustomSettingValue>
  <CustomSettingValue>
    <Token>D</Token>
    <Display>true</Display>
    <Flag>false</Flag>
    <Required>true</Required>
  </CustomSettingValue>
</CustomSettingValues>')


;WITH XMLNAMESPACES(DEFAULT 'http://ABC.ABC.Interfaces/PInterfaceSettings.xsd')
UPDATE @tbl
SET testXML.modify('replace value of (/CustomSettingValues/CustomSettingValue/Flag/text()[.="false"])[1] with "true"')
WHERE testXML.exist('/CustomSettingValues/CustomSettingValue/Token/text()[.="C"]')=1;

SELECT * FROM @tbl

My attempt above runs without error but I don't see the value changes at all.

Upon further look I believe I need to put some condition to find Token="C" at [1] instead of hardcoding it but I can't figure it out syntax wise.

CodePudding user response：

Please try the following solution.

SQL

DECLARE @tbl TABLE(testXML XML);
INSERT INTO @tbl (testXML) VALUES 
(N'<CustomSettingValues xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                     xmlns:xsd="http://www.w3.org/2001/XMLSchema"
                     xmlns="http://ABC.ABC.Interfaces/PInterfaceSettings.xsd">
    <CustomSettingValue>
        <Token>A</Token>
        <Display>true</Display>
        <Flag>true</Flag>
        <Required>false</Required>
        <SettingValue>0</SettingValue>
    </CustomSettingValue>
    <CustomSettingValue>
        <Token>B</Token>
        <Display>true</Display>
        <Flag>false</Flag>
        <Required>false</Required>
    </CustomSettingValue>
    <CustomSettingValue>
        <Token>C</Token>
        <Display>true</Display>
        <Flag>false</Flag>
        <Required>false</Required>
    </CustomSettingValue>
    <CustomSettingValue>
        <Token>D</Token>
        <Display>true</Display>
        <Flag>false</Flag>
        <Required>true</Required>
    </CustomSettingValue>
</CustomSettingValues>')


;WITH XMLNAMESPACES(DEFAULT 'http://ABC.ABC.Interfaces/PInterfaceSettings.xsd')
UPDATE @tbl
SET testXML.modify('replace value of (/CustomSettingValues/CustomSettingValue[Token/text() = "C"]/Flag/text())[1] with "true"')

SELECT * FROM @tbl;