How to update xml field in SQL Server

Question

I have a XML column called xmlValue in a SQL Server table tbl1 with datatype nvarchar(max).

The xml value in this column looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<main>  
    <sub>
        <subMode>
            <name>abc</name>
            <address>add abc</address>
        </subMode>
        <subMode>
            <name>xyz</name>
            <address>add xyz</address>
        </subMode>
    <sub>
</main>

Currently, the address value of name 'xyz' is 'add xyz'. I need to update it to something else say 'add xyz updated'.

Is there any simple way to do this.

I tried using solution provided in How to Update XML in SQL based on values in that XML but it seems complicated.

Do anyone has a simpler solution to achieve this?

Answer 1

You were told already, that your XML should be stored as native XML.

Your problem is multifolded

• wrong datatype (NVARCHAR(MAX) instead of XML)
• A cast to XML from NVARCHAR is not allowed with a declaration stating UTF-8 encoding
• .modify is not applicable on the fly

So the workaround is a temp table

A mockup scenario

DECLARE @tbl TABLE(ID INT IDENTITY, YourXmlAsString NVARCHAR(MAX));
INSERT INTO @tbl VALUES
('<?xml version="1.0" encoding="UTF-8"?>
<main>  
    <sub>
        <subMode>
            <name>abc</name>
            <address>add abc</address>
        </subMode>
        <subMode>
            <name>xyz</name>
            <address>add xyz</address>
        </subMode>
    </sub>
</main>');

--This SELECT converts your string-XML and stores the result as real XML

SELECT ID
        ,CAST(REPLACE(YourXmlAsString,'UTF-8','UTF-16') AS XML) CastedToRealXML
        ,YourXmlAsString AS OriginalValue
INTO #tempTblKeepsCastedValue
FROM @tbl 
--WHERE SomeCriteria;

--Your search for xyz and append something to the existing value

DECLARE @SearchForName NVARCHAR(100)=N'xyz';
DECLARE @Append NVARCHAR(100)=N'add to the value';

UPDATE #tempTblKeepsCastedValue
SET CastedToRealXML.modify('replace value of 
                            (/main/sub/subMode[name/text()=sql:variable("@SearchForName")]/address/text())[1]
                            with concat((/main/sub/subMode[name/text()=sql:variable("@SearchForName")]/address/text())[1],sql:variable("@Append"))');

--Now you update the original tabel using an INNER JOIN to the temp table

UPDATE t
SET YourXmlAsString=CAST(tmp.CastedToRealXml AS NVARCHAR(MAX))
FROM @tbl AS t
INNER JOIN #tempTblKeepsCastedValue AS tmp ON t.ID=tmp.ID;

--The result (and clean-up)

SELECT * FROM @tbl
DROP TABLE #tempTblKeepsCastedValue;

ID  YourXmlAsString
1   <main><sub><subMode><name>abc</name><address>add abc</address></subMode><subMode><name>xyz</name><address>add xyzadd to the value</address></subMode></sub></main>