title: Convert string into xml and insert Sql Server
body: <p>We have a SQL Server 2008 R2 database table with XML stored in a column of <code>VARCHAR</code> data type. </p>
  
  <p>I now have to fetch some of the elements of the xml.</p>
  
  <p>So I want to first convert the xml stored as a <code>VARCHAR</code> data type, to an xml stored as <code>xml</code> data type.</p>
  
  <p>Example : </p>
  
  <p>Table A</p>
  
  <pre><code>Id(int) , ProductXML (varchar(max))
  </code></pre>
  
  <p>Table B</p>
  
  <pre><code>Id(int), ProductXML(XML)
  </code></pre>
  
  <p>I want to convert the <code>ProductXML</code> from <code>Table A</code> into XML data type and insert into <code>Table B</code>.</p>
  
  <p>I tried using the <code>CAST()</code> and <code>CONVERT()</code> function as shown below :</p>
  
  <pre><code>insert into TableB (ProductXML)
  select CAST(ProductXML as XML) from TableA;
  </code></pre>
  
  <p>Similarly tried convert but I get an error </p>
  
  <blockquote>
    <p>XML Parsing : unable to switch encoding</p>
  </blockquote>
  
  <p>Is there any way I can convert the <code>varchar</code> entries in the table into XML entries ?</p>
  
  <p>About the XML: it is huge with many nodes, and its structure changes dynamically. </p>
  
  <p>Example : One row can have and XML entry for 1 product and another row can have an xml entry for multiple products.</p>