Importing a web service XML document into SQL stored procedure

  • Hi

    I am trying to import XML data from a web service.

     I need to pass variables to the webpage which produces a new XML document which then needs to be imported.

    So in  a stored procedure I would cycle through types, make a call to the website with the type

    ke.test.com\example\?type=1 ----> This displays an XML document

    I import this data into a SQL table

    and then go on to

    ke.test.com\example\?type=2

    ke.test.com\example\?type=3 etc...

    I have been looking into using CLR but I havent found that much documentation

    stuck! help

    thanks

  • This is probably more a job for SSIS than for stored procs.

    Calling a web service in SSIS is pretty easy (there's a task for it), and you should be able to do a loop over the table that contains the types you need to hit.

    Not sure if you need to call this on a schedule, or it's something the front-end app would kick off. If it's the latter, may be a bit tougher to do with SSIS.

     


    Greg Walker
    DBA, ExpenseWatch.com

  • Hi

    I tried doing it in SSIS but I had some issues

    Firstly its not a proper web service so the web service task would fail as there is no WSDL

    I thought I could then do it with the XML Source in Data Flow, inserting a user variable of the URL into the XML location field.

    SSIS didnt like the idea of a variable in the XML location. I think column mapping to the destination is not possible.

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

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