How to update an existing XML field data in SQL Server table?

Question

I am trying to update CertificateIssuers field shown in the XML output below

Query:

SELECT Settings FROM Sites

Output:

<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>

I found XML from SQL column: Cannot call methods on nvarchar(max) and I am able to query the value like below

Query:

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

Output:

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

but how to I update the CN to lets say to below for example

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

Why aren't you storing XML data using the xml datatype? NOt sure what you're asking here though, are you actually wanting to process an UPDATE on the table? How does '-M-CA-CA-1' translate to 'planet'? — Thom A
– Thom A ♦, Commented Feb 22, 2019 at 12:16

Salman Arshad · Accepted Answer · 2019-02-22 12:34:19Z

1

You can try the following query.

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

edited Feb 22, 2019 at 12:34

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

answered Feb 22, 2019 at 12:17

Mano

7883 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to update an existing XML field data in SQL Server table?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related