Question

SQL SERVER 2005 XML LOAD

XML, SQL Server 2005

Hi EVeryone,

Having a text file (in which resides the xml data and the xml schema definition),i have to create the afilliate tables to that XML(+XSD).The problem is the XSD is variable , and can not be hardcoded, i mean that XSD could vary from file to file...
Is there a way to create automatically the tables and load them with data from the xml?

Greets,
Adrian

You can use BULK INSERT to load the entire XML file into a single field of a single record of a temp table. Then use OPENXML to query for the XML and XSD (I'm pretty sure you can query for the inline XSD in this mannor, if you you can probably figure it out by looping through the data structor directly). Use this information to dynamically create the CREATE TABLE scripts and then execute the scripts, then use dynamic SQL to load the data from the OPENXML into the new tables.