CODEWITHSUNDEEP
sql-serverxml
user612970
user612970

Reputation: 41

How do I replace an XML Node value?

I need to replace 2 XML nodes, both postcodes with their correct value. How do I accomplish this in SQL 2005. The XML is in an XML column.

<customer><postcode>P22 2XH</postcode></customer>

with IP22 2XH

Regards

Rob

Upvotes: 2

Views: 1573

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107686

Working example to update xml node in a table

create table xml (xml xml);
insert xml values('<customer name="John"><postcode>P22 2XH</postcode></customer>');
insert xml values('<customer name="Doe"><postcode>P22 2XH</postcode></customer>');
insert xml values('<customer name="Jane"><postcode>P9 2XH</postcode></customer>');

UPDATE xml
SET xml.modify('
  replace value of (//customer/postcode[text()="P22 2XH"]/text())[1]
  with "IP22 2XH" ');

select * from xml;

If you had multiple postcode nodes PER xml-record-column, then you can use the below. SQL Server only allows one xml node replacement per modify, so you need to loop it.

create table xml (salesperson varchar(100), portfolios xml);
insert xml values('jim','
    <customer name="John"><postcode>P22 2XH</postcode></customer>
    <customer name="Doe"><postcode>P22 2XH</postcode></customer>
    <customer name="Jane"><postcode>P9 2XH</postcode></customer>');
insert xml values('mary','
    <customer name="Joe"><postcode>Other</postcode></customer>
    <customer name="Public"><postcode>P22 2XH</postcode></customer>');

while exists (
    select * from xml
    cross apply portfolios.nodes('//customer/postcode[text()="P22 2XH"]') n(c))
UPDATE xml
SET portfolios.modify('
  replace value of (//customer/postcode[text()="P22 2XH"]/text())[1]
  with "IP22 2XH" ');
;

select * from xml

Upvotes: 1

Related Questions