Refresh XML Node with Conversion in SQL Server 2005

Question

I have a column containing XML data, but a TEXT type, not an XML type. (I have to leave it like this for another reason).

Basically I need to pass it to NText first and then to XML. The only problem is my current format, which works to select Node, doesn't work to update it.

Error message: Invalid syntax next to 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> 

Answer 1

There may be a good reason to store XML in [n]varchar(max) . If you want to store only XML, that's fine, but if you want to change parts of XML using TSQL or you need to query XML for values ​​or use node / attribute values ​​in the where clause, you should switch to XML where you can use data indexes and skip type conversions. Since text deprecated, you should at least consider switching the data type to [n]varchar(max)

If you had data in an XML column, you can use XML DML to modify XML. In your case, you can use replace the value as follows.

 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 an XML data type, this is not possible, so you need to extract the entire XML document, modify it, and then update the table with the modified XML document.

Of course, you can use some kind of tool for developing client tools, but this is also possible in TSQL.

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

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

 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