Update XML Node with Conversion in SQL Server 2005

Question

I have a column that contains XML data, but is TEXT type and not XML type. (I have to leave it like this for another reason).

Basically i need to cast it to NText first and then XML. The only problem is my current format that works for selecting the Node value doesnt work to update it.

Error Message: Incorrect syntax near the keyword 'AS'.

UPDATE tbl_Module_RequestForms_Items
 SET CAST(CAST(TicorOregon..tbl_Module_RequestForms_Items.XML AS NTEXT) AS XML).value('(//Record/Submitted)[1]', 'NVARCHAR(max)') = 'True'
 WHERE CAST(CAST(TicorOregon..tbl_Module_RequestForms_Items.XML AS NTEXT) AS XML).value('(//Record/Submitted)[1]', 'NVARCHAR(max)') <> 'True'

XML Data:

 <Record>
   <Submitted>False</Submitted>
 </Record>

If it **IS** XML - make it an `XML` column - you'll spare yourself so much hassle and trouble .... — marc_s, Dec 05 '11 at 17:21
is there anyway around that. It's hard to explain, but at this point, i can't change the column type. I got the select to work, but just can't get this update to work. thanks — the sandman, Dec 05 '11 at 17:42

score 2 · Accepted Answer · answered Dec 05 '11 at 22:29

There might be valid reason to store XML in a [n]varchar(max). If you want to only store the XML it is perfectly OK but if you want to modify parts of the XML using TSQL or if you need to query the XML for values or use node/attribute values in a where clause you should switch to XML where you can benefit from indexes on the data and skip the type conversions. Since text is deprecated you should at least consider to switch data type to [n]varchar(max)

If you had your data in a XML column you would use XML DML to modify the XML. In your case you would use replace value of like this.

update tbl_Module_RequestForms_Items
set XMLData.modify('replace value of (/Record/Submitted/text())[1] with "True"')
where XMLData.value('(/Record/Submitted)[1]', 'bit') = 0

Without the XML data type that is not possible so you have to extract the entire XML document, modify it and then update the table with the modified XML document.

You can of course do that using some kind of client development tool but it is also possible in TSQL.

Declare a table variable with the primary key from tbl_Module_RequestForms_Items and the XMLData column but as data type XML.
Copy the rows from tbl_Module_RequestForms_Items to the table variable that should be updated.
Update the XML using replace value of.
Apply the changes back to tbl_Module_RequestForms_Items.

Something like this where I assume that ID is the primary key for tbl_Module_RequestForms_Items and that your XML data is in column XMLData:

declare @T table
(
  ID int primary key,
  XMLData xml
)

insert into @T 
select M.ID,
       M.XMLData
from tbl_Module_RequestForms_Items as M
where cast(cast(XMLData as nvarchar(max)) as xml).value('(/Record/Submitted)[1]', 'bit') = 0

update @T
set XMLData.modify('replace value of (/Record/Submitted/text())[1] with "True"')

update M
set XMLData = cast(T.XMLData as nvarchar(max))
from tbl_Module_RequestForms_Items as M
  inner join @T as T
    on M.ID = T.ID

Update XML Node with Conversion in SQL Server 2005

1 Answers1