New to xml

Posted on 2013-11-27
Last Modified: 2016-02-11
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.
Question by:Southern_Gentleman
LVL 26

Expert Comment

ID: 39682241
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
LVL 65

Expert Comment

by:Jim Horn
ID: 39682351

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?
LVL 37

Assisted Solution

ValentinoV earned 250 total points
ID: 39682863
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!
LVL 65

Accepted Solution

Jim Horn earned 250 total points
ID: 39685679
>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.

Author Closing Comment

ID: 39703942
Thank you guys, This is a start. I will definitely look to your help in the future. Thanks

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
My client has a dictionary table. They're defining a list of standard naming convention. Now, they are requiring my team to provide us a mechanism how to match new incoming data with existing data in their system.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now