Home Forums Programming XML Retrieve XML Code by API with Automatic Approach RE: Retrieve XML Code by API with Automatic Approach

  • 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