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
– Michael Berkowski, Commented May 12, 2018 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
– Michael Berkowski, Commented May 12, 2018 at 23:41
Please share instances of your data, your attempted code. And why do you need to do this with SQL? — pyeR_biz
– pyeR_biz, Commented May 12, 2018 at 23:41

Andrea · Accepted Answer · 2018-05-13 07:52:51Z

6

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:

answered May 13, 2018 at 7:52

Andrea

12.5k17 gold badges71 silver badges76 bronze badges

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

Comments

Gottfried Lesigang · Accepted Answer · 2018-05-13 20:57:27Z

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.

Collectives™ on Stack Overflow

Replace value in XML using SQL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related