We have a sql server 2008 R2 database table with an xml stored in a column as a VARCHAR data type.
I now have to fetch some of the elements of the xml. So I want to first convert the xml stored as a VARCHAR data type, to an xml stored as an xml data type.
example : Table A Id(int) , ProductXML (varchar(max))
I want to convert the ProductXML from Table A into XML data type and insert into Table B.
I tried using the CAST() and CONVERT() function as shown below :
insert into TableB (ProductXML)
select CAST(ProductXML as XML) from TableA;
similarly tried convert but I get an error 'XML Parsing : unable to switch encoding'.
Is there any way I can convert the varchar entries in the table into xml entries ?
About the *XML* : The XML is huge with many nodes , and its structure changes dynamically. Example : One row can have and XML entry for 1 product and another row can have an xml entry for multiple products.