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

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

Answer 1

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