I recently had a phone interview in which I was asked about importing XML schema's and converting SQL Server 2000 DTS packages to SSIS 2008 packages. The interviewer had a need to import data from XML files and automatically generate the schema's and didn't have a lot of time to figure out how. I had no recent experience on the subjects so I did some research. The answers to the interviews questions can be found in the following links and may end up being useful to me at some point now that I have done a little research. I'm sure there is still more complexity to deal with but basically it just involves using SQLXMLBulkLoad, setting the Bulk Load property to false so that no XML data is loaded and seting the SchemaGen property to true so that the schema will be generated. The data could be loaded this way as well or BCP could be used to import the XML data. I found another article on how to Exporting a Large XML File
which involves creating a BCP format file for the data so I suspect you could use SQLXMLBulkLoad to create the schema and then BCP to create a format file to be used for importing and exporting the data.
DataSet Schema Inference Process
Summary of the DataSet Schema Inference Process - 1
Summary of the DataSet Schema Inference Process - 2
Guru's Guide to SQL Server Architecture and Internals, The Chapter 18
XML Bulk Load Examples (SQLXML 4.0) - Note: Search for SchemaGen property.
XML to Schema Wizard (Visual Basic)
Exporting a Large XML File
Examples of Bulk Importing and Exporting XML Documents
Another subject the interviewer was interested in was converting DTS packages to SSIS 2008.
There is an option are a few options for this described in the following links.
No comments:
Post a Comment