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?

IF it's XML, why isn't it **stored** as `XML` ?? All the XQuery functions like `.nodes()`, `.value()` etc. require XML - so it would be beneficial to have that most appropriate datatype from the beginning. Also, `XML` stores the data in a highly optimized fashion and will use up less space than storing XML as plain text — marc_s, Mar 22 '18 at 05:30
Right now I cant modify the data type. I can convert it to nvarchar(max) while updating. Do you have any solution for the posted question? — , Mar 22 '18 at 05:37
While SQL Server 2008 provides extensive support for working with XML, I've personally always found it quite cumbersome. You can use the [`modify()`](https://learn.microsoft.com/en-us/sql/t-sql/xml/modify-method-xml-data-type) built in method if you can convert the data type to `xml`. I don't think I can offer a simpler way to do it. — Zohar Peled, Mar 22 '18 at 07:51

score 1 · Accepted Answer · answered Mar 22 '18 at 09:22

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>

How to update xml field in SQL Server

1 Answers1