Replace value in XML using SQL

Question

I need to replace a value in xml using SQL, challenge here I am facing is the value which I want to replace is not on a specific xpath, same value is on different nodes and the xpath is also different, so basically I want to find and replace a value.

example:

<data>
  <policy>
    <Effectivedate>2018-04-05</Effectivedate>
    <TermStartDate>2018-04-05</TermStartDate>
    <Line>
        <Risk>
            <Coverage>
                <Type>1</Type>
                <coverstartdate>2018-04-05</coverstartdate>
            </Coverage>
            <Coverage>
                <Type>2</Type>
                <coverstartdate>2018-04-05</coverstartdate>
            </Coverage>
            <Coverage>
                <Type>3</Type>
                <coverstartdate>2018-04-05</coverstartdate>
            </Coverage>
        </Risk>
    </Line>
  </policy>
 </data>

In above example I need to replace date 2018-04-05 with 2018-04-06

Please can anyone help here.

Please add a tag for the RDBMS you are using, as string operations differ widely among them. Also, can you edit above to show a few samples of the XML and how the values must change? — Michael Berkowski, May 12 '18 at 23:40
If the XML is simple without attributes and without irregular whitespace, it might be easy. Otherwise it might be challenging. — Michael Berkowski, May 12 '18 at 23:41
Please share instances of your data, your attempted code. And why do you need to do this with SQL? — pyeR_biz, May 12 '18 at 23:41
Did you tried anything, or you just asking for ready solution? — Fabio, May 13 '18 at 00:45
If you are just replacing text without regards to the XML structure at all....... then I would think you can just search and replace text with whatever "string replace" functions your programming environment supports. If you have the xml in some kind of object, you would output it to a string variable or text file first. — Mike M, May 13 '18 at 04:34

score 6 · Accepted Answer · answered May 13 '18 at 07:52

If you have to replace elements in different paths you can cast the xml to a *char and use replace:

declare @xml xml='<data> <policy> <Effectivedate>2018-04-05</Effectivedate> <TermStartDate>2018-04-05</TermStartDate> <Line> <Risk> <Coverage> <Type>1</Type> <coverstartdate>2018-04-05</coverstartdate> </Coverage> <Coverage> <Type>2</Type> <coverstartdate>2018-04-05</coverstartdate> </Coverage> <Coverage> <Type>3</Type> <coverstartdate>2018-04-05</coverstartdate> </Coverage> </Risk> </Line> </policy> </data>'

select cast (replace (cast(@xml as nvarchar(max)), '2018-04-05','2018-04-06') as xml)

Result:

score 4 · Answer 2 · answered May 13 '18 at 20:57

The .modify() function will not allow you to change more than one value per call. You might do this in string level with REPLACE() but this could have side-effects...

What you can do (but this won't be fast) is to use .modify() in a loop, until all occurances are replaced, something like this:

DECLARE  @xml XML=
N'<data>
  <policy>
    <Effectivedate>2018-04-05</Effectivedate>
    <TermStartDate>2018-04-05</TermStartDate>
    <Line>
        <Risk>
            <Coverage>
                <Type>1</Type>
                <coverstartdate>2018-04-05</coverstartdate>
            </Coverage>
            <Coverage>
                <Type>2</Type>
                <coverstartdate>2018-04-05</coverstartdate>
            </Coverage>
            <Coverage>
                <Type>3</Type>
                <coverstartdate>2018-04-05</coverstartdate>
            </Coverage>
        </Risk>
    </Line>
  </policy>
 </data>';

WHILE @xml.exist(N'//*[text()="2018-04-05"]')=1
BEGIN
    SET @xml.modify(N'replace value of (//*[text()="2018-04-05"]/text())[1] with "2018-04-06"');
END
SELECT @xml;

This is ugly (due to the loop) and slow, but - at least - it is XQuery.

Replace value in XML using SQL

2 Answers2

Linked