title: Parsing XML with T-SQL returns anything
body: <p>I wrote a web service and a webmethod it returns list of my class(DOVIZ_RETURN) on xml format like this:</p>
  
  <pre><code> &lt;?xml version="1.0" encoding="utf-8"?&gt;
  &lt;ArrayOfDOVIZ_RETURN xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://tempuri.org/"&gt;
      &lt;DOVIZ_RETURN&gt;
          &lt;dovizAdi&gt;AMERİKAN DOLARI&lt;/dovizAdi&gt;
          &lt;dovizKodu&gt;USD&lt;/dovizKodu&gt;
          &lt;dovizAlis&gt;1.7928&lt;/dovizAlis&gt;
          &lt;dovizSatis&gt;1.8014&lt;/dovizSatis&gt;
      &lt;/DOVIZ_RETURN&gt;
      &lt;DOVIZ_RETURN&gt;
          &lt;dovizAdi&gt;KANADA DOLARI&lt;/dovizAdi&gt;
          &lt;dovizKodu&gt;CAD&lt;/dovizKodu&gt;
          &lt;dovizAlis&gt;1.8321&lt;/dovizAlis&gt;
          &lt;dovizSatis&gt;1.8404&lt;/dovizSatis&gt;
      &lt;/DOVIZ_RETURN&gt;
  &lt;/ArrayOfDOVIZ_RETURN&gt;
  </code></pre>
  
  <p>and ı am calling this service with a stored procedure sucessfully. I am getting this xml. But when ı parse this xml with sql below, it returns any rows just returns column titles; Look below please:</p>
  
  <pre><code>ALTER PROCEDURE GET_DOVIZ_KUR_RESULT_FROM_SERVIS
  
  AS
      declare @obj int
      declare @servisUrl varchar(200)
      declare @response xml
  
      SET @servisUrl='http://localhost/DovizKurTestApp/DovizKurService.asmx/GET_DOVIZ_KUR'
  
      EXEC sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
      EXEC sp_OAMethod @obj,'Open',NULL,'GET',@servisUrl,false
      EXEC sp_OAMethod @obj,'send'
      EXEC sp_OAGetProperty @obj, 'responseText', @response OUT
  
      create table #DOVIZ_KUR (dovizKurList xml)
      insert into #DOVIZ_KUR select @response 
  
      SELECT
      a.b.value('dovizAdi[1]', 'Varchar(100)') DovizAdi,
      a.b.value('dovizKodu[1]', 'Varchar(100)') DovizKodu,
      a.b.value('dovizAlis[1]', 'Varchar(100)') DovizAlis,
      a.b.value('dovizSatis[1]', 'Varchar(100)') DovizSatis
      FROM #DOVIZ_KUR
      CROSS APPLY dovizKurList.nodes('/ArrayOfDOVIZ_RETURN/DOVIZ_RETURN') AS a(b)
  
      EXEC sp_OADestroy @obj
  
  RETURN
  </code></pre>
  
  <p>what is wrong here?</p>