Post reply

XML and Web services

  • September 23, 2007 at 6:14 am

    #69185

    Hi All

    I am inexperienced with XML and am hoping someone can start me off in the right direction......

    We are using SQL 2005 and I want to be able to access XML data from a remote server via the remote servers web services. The remote server belongs to another vendor and holds quality data that I want to use and access in SQL 2005. The vendor says they have web services available but when I asked for a list of supported methods for accessing the service all I got back from them was a url which when opened in a browser has a defined set of data displayed. Viewing the source of the browser page shows that it is an xml document. Irrespective of the fact that this is not what I would call web services, can anyone tell me how I would code a call to such a url aand process the data returned in a stored procedure? A few pointers would really help here as I can write SQL querires but dont know where to start on something like this. Any help would be greatly appreciated.

    Paul

    PS this is a cross post from SQL 2005 General discussion - I thought this forum might be a more appropriate one for my question. My apologies if this causes offence or breaks any rules.

  • September 24, 2007 at 5:14 am

    #735563

    Wow - I have to get used to the new website here - change always freaks me out.

    As for your question, I would start by using the CLR integration in SQL 2005 for this.  It consumes web services very nicely.  Once you have a new CLR stored procedure project open in VS, add the web reference (using the URL they gave you) to your project.  You should then see the XML you saw in the web page as methods from the web service.

    If you have any experience in visual studio at this point, you should find it pretty easy to call the web methods.  If they are returning simple data types, you will need no further assistance.  If they are returning XML, look at the XML parse functions within T-SQL in SQL 2005 - they are well documented in books online.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply