New to xml

Posted on 2013-11-27
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 66

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 1000 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 66

Accepted Solution

Jim Horn earned 1000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

770 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