How do I replace an XML Node value?

Question

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

are both nodes in the same record, or one postcode node per record column? — RichardTheKiwi
– RichardTheKiwi, Commented Feb 11, 2011 at 11:55
There are 2 nodes before the postcode node within the customer node. There is 1 postcode node per customer. — user612970
– user612970, Commented Feb 11, 2011 at 13:58

RichardTheKiwi · Accepted Answer · 2011-02-11 19:04:09Z

1

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

edited Feb 11, 2011 at 19:04

answered Feb 11, 2011 at 11:59

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

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

2 Comments

user612970 Over a year ago

That works if you know the position of the problem node, but how do I do this when I don't know it's position? There are thousand of customers. No attributes are available to myself, only node names and values.

RichardTheKiwi Over a year ago

@user - answer updated. //customer searches for customer anywhere in the XML, and then searches for postcode within it

Collectives™ on Stack Overflow

How do I replace an XML Node value?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related