sql query to update xml node stored in a column

Question

I am working on a project where I am using a table which have xml data stored in one of its column.I am trying to update my entire xml node based on xml_id and position of xml node but I am unable to do that.I tried the following query but its only updating the value of xml node not the entire key/value of node.

Table structure

enter image description here

Query to update value of xml node

update tblCCBT_Step_Page_Text_Xml
set Xml_XmlData.modify('replace value of (/page/*[position()=1]/text())[1] 
                with "test value"')where xml_id = 101

So now I tried the following query to update entire internal node

update tblCCBT_Step_Page_Text_Xml
set Xml_XmlData.modify('replace value of (/page/*[position()=1]/text())[1] 
                with "<testnode>test value</testnode>"') where xml_id = 101

But I am getting error while trying to do this

Msg 9306, Level 16, State 1, Line 2
XQuery [tblCCBT_Step_Page_Text_Xml.Xml_XmlData.modify()]: The target of 'replace value of' cannot be a union type, found '(element(*,xdt:untyped) | comment | processing-instruction | text) ?'.

This is my xml stored in the column

<page name="page0" identifier="ff-102-101-101">
     <backBut>test value</backBut>
     <printBut>Print</printBut>
     <quiz1>Click on the circle that best describes your</quiz1>
     <quiz2>Continue</quiz2>
     <quiz3>Finish</quiz3>
     <quiz4>You are now on questions </quiz4>
     <quiz5>out of</quiz5>
     <quiz6>Please answer each question before continuing.</quiz6>
 </page>

Now in above xml I am trying to replace -

<backBut>test value</backBut> with <testnode>test value</testnode>

Please suggest how to achieve this. Thanks

souplex · Accepted Answer · 2015-04-15 04:12:19Z

4

You could use an insert/delete approach.

Your code did not throw any error for me on SQL2008R2. But it did NOT give the desired result either.

The statement resulted in replacing the text value of the node, not in replacing the node itself, as you can see below.

<page name="page0" identifier="ff-102-101-101">
  <backBut>&lt;testnode&gt;test value&lt;/testnode&gt;</backBut>
  <printBut>Print</printBut>
  <quiz1>Click on the circle that best describes your</quiz1>
  <quiz2>Continue</quiz2>
  <quiz3>Finish</quiz3>
  <quiz4>You are now on questions </quiz4>
  <quiz5>out of</quiz5>
  <quiz6>Please answer each question before continuing.</quiz6>
</page>

You could achieve it via insert/delete:

First insert the new node:

UPDATE tblCCBT_Step_Page_Text_Xml
SET Xml_XmlData.modify('insert <testnode>test value</testnode> into /page[1]')
WHERE xml_id = 101

Then simply delete the old node:

UPDATE tblCCBT_Step_Page_Text_Xml
SET Xml_XmlData.modify('delete (/page/backBut)[1]')
WHERE xml_id =101

answered Apr 15, 2015 at 4:12

souplex

9816 silver badges16 bronze badges

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

6 Comments

rupinder18 Over a year ago

thanx for the reply.You mean to say we can't perform edit node operation in a single query

rupinder18 Over a year ago

also how to insert at particular position like 1,2 etc

souplex Over a year ago

I am not very familiar with xml manipulation inside SQL, I simply don't know. Just had a look at your code, spotted what went wrong and hooked you up with a possible solution. I read about updating values inside nodes, adding and deleting nodes, but have not seen anything that replaces one node with another.

rupinder18 Over a year ago

ok but thanx for your effort,if will not find single query will surely go with your solution

souplex Over a year ago

The XML DML only has 3 methods: insert, replace value of, and delete. I'm afraid there's not a lot of options.

|

Roger Wolf · Accepted Answer · 2015-04-15 07:29:35Z

Can't believe I did it :)

declare @t table (
    Id int,
    XMLData xml
);

insert into @t (Id, XMLData)
values
    (101, N'<page name="page0" identifier="ff-102-101-101">
     <backBut>test value</backBut>
     <printBut>Print</printBut>
     <quiz1>Click on the circle that best describes your</quiz1>
     <quiz2>Continue</quiz2>
     <quiz3>Finish</quiz3>
     <quiz4>You are now on questions </quiz4>
     <quiz5>out of</quiz5>
     <quiz6>Please answer each question before continuing.</quiz6>
 </page>');

-- Before modification
select * from @t;

update t set XMLData = nt.ModXML
from @t t
    inner join (
        select t2.Id, t2.XMLData.query('
element page {
    /page/@*,
    for $n in /page/*
    return
        if (local-name($n) = "backBut") then
            <testnode>test value</testnode>
        else
            $n
}') as [ModXML]
        from @t t2
    ) nt on t.Id = nt.Id
where t.Id = 101;

-- After modification
select * from @t;

As you can see, it's not an XML update, strictly speaking - I am replacing the contents of the entire XML field. Also, it might stop working in case of more complex structure. And, well, performance can be an issue, too.

Collectives™ on Stack Overflow

sql query to update xml node stored in a column

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related