New to xml

I'm new to the advances of XML. I'm creating a project for my intranet using Visual Studio 2012 and SQL Server (or .mdb). I currently use an e-commerce to have my customers enter in orders. The e-commerce site offers an API service that I'm able to download Orders, Orderdetails, Customers, Products, etc, etc, via an XML file. I was also able to track down all xsd files in order to have my schema's.  Now i'm trying to get a grasp of how I can trigger a download of the xml files on a interval of time to a specific folder location in my project. And then somehow do a bulk update or insert of the xml data to my sql server tables.

I'm taking on this project in order to learn more about programming so I think detailed instructions my benefit me. Thanks experts.
Southern_GentlemanAsked:
Who is Participating?
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>how I can trigger a download of the xml files on a interval of time
Sounds like a scheduling issue.  If your company doesn't have any third-party scheduling tools, then it would be a SQL Agent job that is scheduled to execute at a certain interval, say one minute / hour.

>to a specific folder location in my project.
Sounds like a connection that will be your output.   Typically flat file, and there are multiple ways via expressions and script task/variables to rename the file to meet your needs.

>then somehow do a bulk update or insert of the xml data to my sql server tables.
Data pump, with an XML Source, and OLEDB / SQL destination.

>I'm taking on this project in order to learn more about programming so I think detailed instructions my benefit me.
Sounds like baby steps are in order.  You and I are in the same boat, as I'm studying for the  70-433 SQL '08 development exam, and querying XML is not a strength of mine.  Feel free to ask one or two follow-up questions here, and as many EE questions as you wish, until you get what you need.
0
 
tigin44Commented:
This is an ETL (Export Transfer Load) project. So you should use the SQL Server Integration Services. There you can do all the things you described. Here are some links you may take a look at.

<Link to Google search results list removed>

kaufmed, Topic Advisor


http://www.sqlservercentral.com/Forums/Topic1405446-391-1.aspx
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
tigin44

fyi Posting a Google search link is considered the height of laziness around here.

Also, the sqlservercentral link you posted is essentially another link, which results in a boatload of other links.

EE has a statement on links that would be a good read.

Any chance you can comment your own work?
0
 
ValentinoVBI ConsultantCommented:
Obviously writing your own application to handle this is always possible.  But as you're using SQL Server, and as mentioned before, SSIS is ideal for this.  If the XML is not too complex, you'll be fine using the XML Source component in a Data Flow.  I wrote an article on that a while ago: Loading XML Using SSIS

For more complex XML you'll find that the XML Source is rather limited.  To solve that I usually use XSLT to first convert the XML into CSV which can then more easily get imported into the DB.  This can all be automated using SSIS as well.  And I also wrote an article about that: Loading Complex XML Using SSIS

Let me know if you have any questions after having looked at those articles!
0
 
Southern_GentlemanAuthor Commented:
Thank you guys, This is a start. I will definitely look to your help in the future. Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.