title: How to extract values from XML stored as ntext in SQL
body: <p>I have a database where I am trying to query a column where there is XML stored as ntext in our SQL Server. Why is it not stored as XML? Not sure but I can't change it so I have worked to try and convert it to some XML format. At this point I have been able to run this to get rows of entries that are treated as XML.</p>
  
  <pre><code>select  top 5 cast(cast(ATTR_XML as varchar(max)) as XML)
  from dbo.table1
  </code></pre>
  
  <p>This resulted in the following that is recognized as XML</p>
  
  <pre><code>&lt;ArrayOfNameValue xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;
    &lt;NameValue&gt;
      &lt;Name&gt;Form_Submit Request_3E6791F5B9884EE2B335BC1F1E1285C5&lt;/Name&gt;
      &lt;Value xsi:type="xsd:string"&gt;ProjectX-LIB-46c3219d-1fe0-4abb-b559-efb8a3c35505,AgileForm.34&lt;/Value&gt;
    &lt;/NameValue&gt;
    &lt;NameValue&gt;
      &lt;Name&gt;Form&lt;/Name&gt;
      &lt;Value xsi:type="xsd:string"&gt;ProjectX-LIB-46c3219d-1fe0-4abb-b559-efb8a3c35505,AgileForm.69&lt;/Value&gt;
    &lt;/NameValue&gt;
    &lt;NameValue&gt;
      &lt;Name&gt;Form_Update Request_E4FA8434326E42ADB6222978214E93E9&lt;/Name&gt;
      &lt;Value xsi:type="xsd:string"&gt;ProjectX-LIB-46c3219d-1fe0-4abb-b559-efb8a3c35505,AgileForm.69&lt;/Value&gt;
    &lt;/NameValue&gt;
    &lt;NameValue&gt;
      &lt;Name&gt;//&lt;/Name&gt;
      &lt;Value xsi:type="xsd:string"&gt;&amp;lt;?xml version="1.0" encoding="utf-8"?&amp;gt;&amp;lt;pd:Data xmlns:pd="http://www.test.com/XMLSchema"&amp;gt;&amp;lt;pd:Request_Source system_name="Zo5ZCqLRRfV" datatype="object" listID="" listValue=""&amp;gt;Coastal Urban&amp;lt;/pd:Request_Source&amp;gt;&amp;lt;pd:Request_Category system_name="ojZD66m94eu" datatype="object" listID="" listValue=""&amp;gt;Information Request&amp;lt;/pd:Request_Category&amp;gt;&amp;lt;pd:Date_of_Request system_name="KNc4mzCYmYL" datatype="date" listID="" listValue=""&amp;gt;05 Feb 2015&amp;lt;pd:Request_Date system_name="QXf1HQ7EFUZ" datatype="string" listID="" listValue=""&amp;gt;06-Feb-15 03:38:58&amp;lt;/pd:Request_Date&amp;gt;&amp;lt;pd:Request_Priority system_name="" datatype="" listID="" listValue=""&amp;gt;&amp;lt;/pd:Request_Priority&amp;gt;&amp;lt;/pd:Data&amp;gt;&lt;/Value&gt;
    &lt;/NameValue&gt;
  &lt;/ArrayOfNameValue&gt;
  </code></pre>
  
  <p>I am trying to extract the element name and value from this </p>
  
  <pre><code>&lt;Value xsi:type="xsd:string"&gt;&amp;lt;?xml version="1.0" encoding="utf-8"?&amp;gt;&amp;lt;pd:Data xmlns:pd="http://www.test.com/XMLSchema"&amp;gt;&amp;lt;pd:Request_Source system_name="Zo5ZCqLRRfV" datatype="object" listID="" listValue=""&amp;gt;Coastal Urban&amp;lt;/pd:Request_Source&amp;gt;&amp;lt;pd:Request_Category system_name="ojZD66m94eu" datatype="object" listID="" listValue=""&amp;gt;Information Request&amp;lt;/pd:Request_Category&amp;gt;&amp;lt;pd:Date_of_Request system_name="KNc4mzCYmYL" datatype="date" listID="" listValue=""&amp;gt;05 Feb 2015&amp;lt;pd:Request_Date system_name="QXf1HQ7EFUZ" datatype="string" listID="" listValue=""&amp;gt;06-Feb-15 03:38:58&amp;lt;/pd:Request_Date&amp;gt;&amp;lt;pd:Request_Priority system_name="" datatype="" listID="" listValue=""&amp;gt;&amp;lt;/pd:Request_Priority&amp;gt;&amp;lt;/pd:Data&amp;gt;&lt;/Value&gt;
  </code></pre>
  
  <p>I'd like to extract Request_Category is 'Information Request'.</p>
  
  <p>How can I pull these out using XML methods and insert them into two columns in my query so I have Name and Value?</p>
  
  <p>Thanks</p>