CODEWITHSUNDEEP
sql-serverxmlt-sqlxpathxquery
R.Gedam
R.Gedam

Reputation: 51

Replace value in XML using SQL

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.

Upvotes: 0

Views: 15431

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

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.

Upvotes: 4

Andrea
Andrea

Reputation: 12355

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:

enter image description here

Upvotes: 6

Related Questions