title: How can I query a value in SQL Server TEXT column that contains XML (not xml column type)
body: <p>I have table DOCUMENTS with:</p>
  
  <pre><code>DOCUMENTS
  ____________________
  DOCUMENTID   int
  USERID       int
  CONTENT      text
  </code></pre>
  
  <p>I have following XML stored in TEXT column with name CONTENT in a SQL Server database</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8" standalone="yes"?&gt;
  &lt;IDMSDocument&gt;
      &lt;DocumentContent&gt;
          &lt;Attribute Name="Number" GUID="{FFFFFFFF-0000-0000-0000-000000000001}"&gt;
              &lt;IDMSType&gt;3060&lt;/IDMSType&gt;
              &lt;Value Type="Integer"&gt;
                  &lt;Value&gt;122&lt;/Value&gt;
              &lt;/Value&gt;
          &lt;/Attribute&gt;
          &lt;Attribute Name="Date" GUID="{FFFFFFFF-0000-0000-0000-000000000002}"&gt;
              &lt;IDMSType&gt;3061&lt;/IDMSType&gt;
              &lt;Value Type="DateTime"&gt;
                  &lt;Date&gt;10-09-2014&lt;/Date&gt;
              &lt;/Value&gt;
          &lt;/Attribute&gt;
          &lt;Attribute Name="Публикуване" GUID="{CA646F55-5229-4FC5-AA27-494B25023F4E}"&gt;
              &lt;IDMSType&gt;3062&lt;/IDMSType&gt;
              &lt;Value Type="String"&gt;
                  &lt;Value&gt;Да&lt;/Value&gt;
              &lt;/Value&gt;
          &lt;/Attribute&gt;
          &lt;Attribute Name="Дата" GUID="{AC0465B0-4FB4-49E2-B4FA-70901068FD9B}"&gt;
              &lt;IDMSType&gt;3063&lt;/IDMSType&gt;
              &lt;Value Type="DateTime"&gt;
                  &lt;Date&gt;01-10-2014&lt;/Date&gt;
              &lt;/Value&gt;
          &lt;/Attribute&gt;
          &lt;Attribute Name="Предмет на поръчка" GUID="{04A4EC72-6F33-461F-98DD-D8D271997788}"&gt;
              &lt;IDMSType&gt;3065&lt;/IDMSType&gt;
              &lt;Value Type="String"&gt;
                  &lt;Value&gt;Избор на консултанти&lt;/Value&gt;
              &lt;/Value&gt;
          &lt;/Attribute&gt;
      &lt;/DocumentContent&gt;
  &lt;/IDMSDocument&gt;
  </code></pre>
  
  <p>I find a way how to query dingle XML attribute in a single row from table, with heavy conversion :</p>
  
  <pre><code>DECLARE @strContent NVARCHAR(MAX);
  DECLARE @xmlContent XML;
  SET @strContent = (select content from DOCUMENTS where DocumentID=24);
  SET @xmlContent = CAST(REPLACE(CAST(@strContent AS NVARCHAR(MAX)),'utf-8','utf-16') AS XML) 
  SELECT @xmlContent.query('/IDMSDocument/DocumentContent/Attribute[5]/Value/Value') 
  where @xmlContent.value('(/IDMSDocument/DocumentContent/Attribute[5]/Value/Value)[1]', 'nvarchar(max)') like '%search%'
  </code></pre>
  
  <p>I need to make query  like "select all rows in table that in fifth attribute in XML have value 'search' "</p>
  
  <p>For me the general problem is that column type is not XML, but I have text column with stored xml inside. when I try cast, query, value directly server return that I can use it Only with XML column.</p>
  
  <p>I would be very grateful if someone suggest how to do this!</p>
  
  <p>Thanks!</p>
  
  <p>Kamen</p>