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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

911 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

18 Experts available now in Live!

Get 1:1 Help Now