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

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

718 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