CODEWITHSUNDEEP
sql-serverxmlweb-services
prabs
prabs

Reputation: 169

Calling Web Service from stored procedure

I have a simple web service hosted in IIS. I am calling it from within a stored procedure in SQL Server. The web service has just one method 

 [WebMethod]
 public string HelloWorld()
 {
        return "Hello World";
 }

It returns the xml: 

<string xmlns="http://tempuri.org/">Hello World</string>

When executing the stored procedure:

SET @Url = 'http://localhost:61004/Default Web Service/WebService1.asmx/
   HelloWorld';

EXEC sp_OACREATE 'MSXML2.ServerXMLHTTP', @obj OUT
EXEC sp_OAMethod @obj, 'open', NULL, 'GET', @Url, false
EXEC sp_OAMethod @obj, 'send'
EXEC sp_OAGetProperty @obj, 'responseText', @response OUT

SELECT @Xml = CAST(@response as xml)

Select @xml

EXEC sp_OADestroy @obj

I got this error:

Parsing XML with internal subset DTDs not allowed. Use CONVERT with style option 2 to enable limited internal subset DTD support.

Any suggestions please.

Upvotes: 1

Views: 6686

Answers (2)

prabs
prabs

Reputation: 169

I added the following in the web.config file.

<configuration>
 <system.web>
 <webServices>
     <protocols>
         <add name="HttpGet"/>
         <add name="HttpPost"/>
     </protocols>
 </webServices>
 </system.web>
</configuration>

Both the sql statements worked.

SELECT @Xml = CAST(@response as xml)
SELECT @Xml = CONVERT(XML, @response, 2)

I changed the path to call to Web Service.

SET @Url = 'http://localhost:61004/WebService1.asmx/HelloWorld';

Then it worked!!! and returned the xml string.

<string xmlns="http://tempuri.org/">Hello World</string>

Upvotes: 0

marc_s
marc_s

Reputation: 754258

You need to read the error message and do as it tells you to! You need to replace your CAST

SELECT @Xml = CAST(@response as xml)

with this instead:

SELECT @Xml = CONVERT(XML, @response, 2)

It seems that your XML somehow causes grief when using CAST - but with the CONVERT and style = 2, many of those situations can be handled properly

Upvotes: 2

Related Questions