如何在SQL Server表中更新现有的XML字段数据？

Question

我尝试更新下面XML输出中显示的CertificateIssuers字段

查询：

SELECT Settings FROM Sites

输出：

<ClientOperationalSettings>
    <Version>5.00.8740.1002</Version>
    <SecurityConfiguration>
        <SecurityModeMask>0</SecurityModeMask>
        <SecurityModeMaskEx>480</SecurityModeMaskEx>
        <HTTPPort>80</HTTPPort>
        <HTTPSPort>443</HTTPSPort>
        <CertificateStoreName/>
        <CertificateIssuers>CN=Mars-M-CA-CA-1; DC=Mars; DC=Local</CertificateIssuers>
        <CertificateSelectionCriteria>SubjectAttr:OU=London</CertificateSelectionCriteria>
        <CertificateSelectFirstFlag>1</CertificateSelectFirstFlag>
        <PKICertOptions>1</PKICertOptions>
        <SiteSigningCert>308202ED308201D5A0030201020210129F196B3060FF94418BEA860E8DD712300D06092A864886F70D01010B05003016311430120603550403130B53697465205365727665723020170D3138303933303031353534395A180F32313138303930373031353534395A3016311430120603550403130B536974652053657276657230820122300D06092A864886F70D01010105000382010F003082010A0282010100B2B3E6757D8E4DF7977F061543CF629B9129C337D373945A4FFE4625D557370FA21B6E03C09AE692A32A6B783BE2C19E3FEC965054F9A54BFDDB7C4B23E45456353EEA9D14FCA5EF58DCDECA3EEB9B7D589162DD87005799D473BB1518398C638217986780CCE8DFB778A84915019700BAF53AB3205730060911D7EEF17C4EDE904953EAEB8BAEBC85CAAFE82F121FC16A9316DB09E94D76A01F61A2129F4F807ADCB68270410393D9BD2E435C253683B013677EFFEBF09EE6BAC25D9AA21742142A58A1E2EAF09BD75C07BA18B85080F6700245461734AB9A466B56BE7D7F2F8E07D62AA75253B8A2839777E7C0A6DC6FF575164232EB05B3C9EA2CFC3531C30203010001A3353033301B0603551D110414301282104D2D5030312E4D6172732E4C6F63616C30140603551D25040D300B06092B060104018237650B300D06092A864886F70D01010B050003820101004AAAD78752D9650694401B43191B0BD586B48A96990C68B40673444C878032CDB7E8113E7344E7FD47DF18FF0DE51F8396B5F670AA5998BE34E6F91BAA79593D5578FB54A3EDABA20E14FE4B5D2736E5FB234F4FED7BA53972A5462682B67A1B3DD6FD2599DBFF0DE49B99C44C1003932816784F06D47C69584DA61C9E026EB2B1730920A06B9296FE69141CCCA1677A52AF644E5834D429F4810340FB6FB30B061A3555F4C3E506B960496CF10E54145384B1A6FE87A1FB26EB078246D132DD509F82180B0CDD9AF485329159F2145CBA6B55A36777558F2901B30B9974739640C4B8DCDF0319C9A10EEF4EAA33B74E7F54CE5C6F313DCB4FF7EEA1FA6453A5</SiteSigningCert>
    </SecurityConfiguration>
    <AADConfig Version="1.0">
        <Tenants/>
    </AADConfig>
</ClientOperationalSettings>

我能够在XML from SQL column: Cannot call methods on nvarchar(max)中找到答案，我可以像下面这样查询值：

查询语句：

SELECT cast([Sites].[Settings] as xml).query('data(ClientOperationalSettings/SecurityConfiguration/CertificateIssuers)') FROM [dbo].[Sites]

输出：

(No column name)
CN=Mars-M-CA-CA-1; DC=Mars; DC=Local

但是我要如何将CN更新为以下示例呢？

<CertificateIssuers>CN=Mars Planet; DC=Mars; DC=Local</CertificateIssuers>

- SunMan

---

为什么不使用“xml”数据类型来存储XML数据？不确定你在这里问什么，你是否真的想要在表上执行“UPDATE”操作？“'-M-CA-CA-1'”如何转换为“'planet'”？ - Thom A

更改数据类型为XML是一个选项吗？ - Salman A

Answer 1

你可以尝试以下查询。

UPDATE 
   dbo.Sites
SET 
   Settings.modify('replace value of (/ClientOperationalSettings/SecurityConfiguration/CertificateIssuers/text())[1] with "CN=Mars Planet; DC=Mars; DC=Local"')
WHERE ....

- Mano