Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Retrieve XML Code by API with Automatic Approach Expand / Collapse
Author
Message
Posted Tuesday, November 19, 2013 7:21 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 9, 2014 1:21 AM
Points: 58, Visits: 191
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.
Post #1515541
Posted Tuesday, November 19, 2013 12:18 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 28, 2014 4:49 AM
Points: 556, Visits: 2,581
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/


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


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1515725
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse