Importing XMLs daily

[Jimdeb03]

We have a project in the works that involves a daily download of .xml files for our accounting program. These xml's are to be imported into a SQL database where all the necessary calculations and reports are to be generated. Once imported, the XML's are to be archived.

There is no question we're going to import the xml data into relational tables to query and archive the data. Some xml files will be imported into multiple tables depending on their design. We'll have at least 15 xml files to import daily.

The project will be in VB.NET 2.0. Database is SQLExpress.

Is there a preference to where this functionality should be performed? Is it best use the tools within SQL Express or use ADO.NET?

I plan on sticking to the Relational instead of the XML Data Model for the reasons above. However, the xml's we'll be receiving will change simply due to upgrades. This could mean simple name changes of Attributes to moving of entire Elements. Schemas aren't supplied by the vendor but obviously can be created. Is Relational still the best option even though the xml's will change? (It's my understanding the XML model is preferred if the structure changes significantly.)

Is using the BLOB or varchar(max) practical for archiving? We'll be archiving the xml files too so would this be overkill?

thanks
Jim

 

[damber]

With SQL Express you wont get SQL Server Integration Services, so the first thing you can decide is where to do the 'transformation' - in this case in your .NET app would probably be easiest.

Second, to get it into the database, ideally use a Data Access Layer to abstract your db implementation, though at least use a tableadapter and dataset with your related tables to create the relevant data within the structure that represents your database. You can then name your stored procs to execute on insert/update/etc and let the tableadapter take care of it for you.

To parse the XML instance, use xpath expressions to take what you need, but don't validate against a schema. This way, assuming the XML designer has any half decent governance practices you will not be affected by additions to the schema (if they remove the element you get with xpath you're still going to have issues with changing something.. this is unavoidable).

As for archiving, you can store a blob if you want, though you might want to look at the xml data type. this way you can use xquery to search and retrieve the xml messages you want using the actual xml content. just note that XML files can get very big, so you may want to think about how you want to use them, and the benefit of putting them in certain places.

So, it comes down to what you intend to do with the data from these xml files.. it could be as simple as storing them as XML data types and using SQL or XQuery to get what you need. Or making the effort to import fully into existing tables. You may even just want to use XSLT to produce your reports if it only involves the XML data...


A smile is worth a thousand kind words. So smile, it's easy!