How to get Sql Server XML variable into TEXT column

Question

I need to update an XML document stored in a Microsoft SQL Server database, however the vendor of the product chose to store the XML in a TEXT column.

I've been able to extract the TEXT into an XML-type variable and perform the update I need on the xml within this variable, but when I try to UPDATE the column to push the change back to the database, I run into trouble.

Looking through the documentation it appears that it's not possible to simply CAST/CONVERT an XML type variable to insert it into a TEXT column, but I would think there is some way to extract the xml "string" from the XML-type variable and UPDATE the column using this value.

Any suggestions are appreciated, but I would like to keep the solution pure SQL that it can be run directly (no C# custom function, etc.); just to keep the impact on the database minimal.

(note: isn't it a bit absurd that you can't just CAST XML as TEXT? I'm just saying...)

Answer 1

Casting the XML as VARCHAR(MAX) works.

declare @xml xml

declare @tblTest table (
    Id int,
    XMLColumn text
)

insert into @tblTest
    (Id, XMLColumn)
    values
    (1, '<MyTest><TestNode>A</TestNode></MyTest>')

set @xml =  '<MyTest><TestNode>A</TestNode><TestNode>B</TestNode></MyTest>'

update @tblTest
    set XMLColumn = cast(@xml as varchar(max))
    where Id = 1

select Id, XMLColumn from @tblTest