Parsing XML with T-SQL returns anything

Question

I wrote a web service and a webmethod it returns list of my class(DOVIZ_RETURN) on xml format like this:

 <?xml version="1.0" encoding="utf-8"?>
<ArrayOfDOVIZ_RETURN xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://tempuri.org/">
    <DOVIZ_RETURN>
        <dovizAdi>AMERİKAN DOLARI</dovizAdi>
        <dovizKodu>USD</dovizKodu>
        <dovizAlis>1.7928</dovizAlis>
        <dovizSatis>1.8014</dovizSatis>
    </DOVIZ_RETURN>
    <DOVIZ_RETURN>
        <dovizAdi>KANADA DOLARI</dovizAdi>
        <dovizKodu>CAD</dovizKodu>
        <dovizAlis>1.8321</dovizAlis>
        <dovizSatis>1.8404</dovizSatis>
    </DOVIZ_RETURN>
</ArrayOfDOVIZ_RETURN>

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:

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

what is wrong here?

Adriaan Stander · Accepted Answer · 2012-09-20 15:57:40Z

2

The reason for this was due to the fact that namespaces were included in the XML.

You can try this

SELECT  
    a.b.value('declare namespace MI="http://tempuri.org/"; MI:dovizAdi[1]', 'Varchar(100)') DovizAdi, 
    a.b.value('declare namespace MI="http://tempuri.org/"; MI:dovizKodu[1]', 'Varchar(100)') DovizKodu, 
    a.b.value('declare namespace MI="http://tempuri.org/"; MI:dovizAlis[1]', 'Varchar(100)') DovizAlis, 
    a.b.value('declare namespace MI="http://tempuri.org/"; MI:dovizSatis[1]', 'Varchar(100)') DovizSatis 
FROM @DOVIZ_KUR d
    CROSS APPLY dovizKurList.nodes('
    declare namespace MI="http://tempuri.org/";
    /MI:ArrayOfDOVIZ_RETURN/MI:DOVIZ_RETURN') AS a(b)

OR Even better

;WITH XMLNAMESPACES('http://tempuri.org/' as MI)
SELECT  
    a.b.value('MI:dovizAdi[1]', 'Varchar(100)') DovizAdi, 
    a.b.value('MI:dovizKodu[1]', 'Varchar(100)') DovizKodu, 
    a.b.value('MI:dovizAlis[1]', 'Varchar(100)') DovizAlis, 
    a.b.value('MI:dovizSatis[1]', 'Varchar(100)') DovizSatis 
FROM @DOVIZ_KUR d
    CROSS APPLY dovizKurList.nodes('/MI:ArrayOfDOVIZ_RETURN/MI:DOVIZ_RETURN') AS a(b)

SQL Fiddle Demo

answered Sep 20, 2012 at 15:57

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Mehmet Over a year ago

sorry man ı edited my question. It returns xml with <?xml version="1.0" encoding="utf-8"?> tag.. When ı SELECT @response query gets this xml(it is on my question).. I tired your solution but it returns anything!!!

Mehmet Over a year ago

I tired both of your solutions it gives error : "XML parsing: line 4, character 19, illegal xml character "

Adriaan Stander Over a year ago

Yup, sorry, just got home from work. Will try to see what I can do. CAn you try to remove the encoding at the top?

Mehmet Over a year ago

yes man ı did it.. With substring(@response,38,LEN(@response)) thank you.

Adriaan Stander Over a year ago

I will have a look at this further, and let you know, maybe using NVARCHAR or REPLACE instead of substring. But good job... X-)

Collectives™ on Stack Overflow

Parsing XML with T-SQL returns anything

1 Answer 1

SQL Fiddle Demo

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related