Post reply

Retrieve XML Code by API with Automatic Approach

  • November 19, 2013 at 7:21 am

    #98947

    Goal:

    Retrieve a XML list from Internet, using API connection, and transfer the data into a sql server's table automatic using Scheduling tool with SQ server agent and its jobs.

    Problem:

    I have two questions:

    1.

    What component in SSIS do I need to retrieve the XML file, using API connection?

    2.

    Is it possible to use stored procedure, code that retrieve and transform the xml file and its data, to transfer it into a table? In This context, no usage of SSIS.

    Information:

    *The computers version is SQL 2008 R2 standard edition.

  • November 19, 2013 at 12:18 pm

    #1667539

    akirajt (11/19/2013)

    1. What component in SSIS do I need to retrieve the XML file, using API connection?

    You can use the SSIS XSLT task. It is not well documented but here is a good article that will get you started:

    Loading Complex XML using SSIS/[/url]

    2.Is it possible to use stored procedure, code that retrieve and transform the xml file and its data, to transfer it into a table? In This context, no usage of SSIS.

    You can use OPENROWSET along with the T-SQL XML datatype methods listed here. You can also use mdq.xmltransform.

    I am personally a fan of using XSLT SSIS-related tasks. The solutions are more elegant and the code is 100% portable to any system that supports XSLT 1.0 (1999).

    EDIT: Added final note about XSLT 1.0

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

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

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