SQL XML用条件修改特定值

Question

我在我的SQL表中有以下XML，我想修改Flag从false真正到仅用于CustomSettingValue的Token值为">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

我上面的尝试运行没有错误，但我根本没有看到值的变化。

进一步看，我认为我需要添加一些条件来找到Token="C"at[1]而不是硬编码，但我不能弄清楚语法。

Answer 1

请尝试以下解决方案。

/p>

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;