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

score 4 · Accepted Answer · answered Apr 15 '15 at 04:12

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 '15 at 04:12

souplex

981
6
16

thanx for the reply.You mean to say we can't perform edit node operation in a single query – rupinder18 Apr 15 '15 at 04:27
also how to insert at particular position like 1,2 etc – rupinder18 Apr 15 '15 at 04:33
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. – souplex Apr 15 '15 at 04:36
ok but thanx for your effort,if will not find single query will surely go with your solution – rupinder18 Apr 15 '15 at 04:38
1

The XML DML only has 3 methods: insert, replace value of, and delete. I'm afraid there's not a lot of options. – souplex Apr 15 '15 at 04:43
yeah,may be I have also come across these only...waiting if somebody comes with a single query solution-if it's possible – rupinder18 Apr 15 '15 at 04:45

score 1 · Answer 2 · answered Apr 15 '15 at 07:29

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.

sql query to update xml node stored in a column

2 Answers2