Bulk instert into MS SQL 2008R2 from XML RAW data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Bulk instert into MS SQL 2008R2 from XML RAW data

Author

Topic

dkekesi
Starting Member

38 Posts

Posted - 2012-02-06 : 06:17:51

Hi All,

I have an XML structure like the following:

<tables>
<table name="tableName1">
<row ID="34" col1="data" col2="dom" />
<row ID="35" col1="data2" col2="dom2" />
</table>
<table name="tableName2">
<row ID="1" col1="data" col2="dom" col3="item1" />
<row ID="3" col1="data2" col2="dom2" col3="item2" />
<row ID="7" col1="data4" col3="item3" />
</table>
...
<tables>

Basically the table nodes contain RAW data created by selecting FOR XML RAW.

Now I wish to do the reverse: read the XML and insert data into respective tables of an MS SQL Server 2008 R2 database. However I want the loading process to be robust, meaning I do not want to mess with column names and table names if they change in the future. I need the process to read table names from @name attributes of table nodes and insert data into columns specified by attributes in nodes. I thought of a stored procedure that gets an XML as input and does the rest.

The amount of data is approx. 70 tables ranging from 10 to 30 000 records, altogether no more than 100 000 records. I need to do it as efficiently as possible, bulk loading would be the best.

The process should not take care of foreign keys as the order of tables inside the XML is built so that FK constraints can be kept in place by loading one table after the other. However there are identity columns in each table so I must do a

SET Identity_Insert ON
and
SET Identity_Insert OFF

before and after processing each table. I also need to reseed each table after inserting all rows. Oh,and I need to do the whole shebang in a transaction so that I could roll back if something goes wrong.

Which way do you suggest I go: should I stay with T-SQL or try to write the SP in CLR SQL? Should I use XQuery or can I use some bulk insert method?

Thanks for all the help!

Best Regards,
Daniel

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 09:50:02

so your xml will have tablenames and also column names/datatypes as values?
if thats the case you can extract xml contents to a temporary table and then use value to build a table dynamically. then
will data be also included as a part of same xml?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dkekesi
Starting Member

38 Posts

Posted - 2012-02-06 : 10:22:16

I forgot to specify that I already have the table structure in the DB. The XML will contain data only and this data is what I need to bulk load into existing tables.

Best Regards,
Daniel

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 11:51:09

then whats the issue? will you not get data for all columns at all times? are some of data optional? or is it data matching you're struggling with? is it done based on column information which is an attribute inside xml?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dkekesi
Starting Member

38 Posts

Posted - 2012-02-07 : 03:24:12

I am sorry for not being clear, so let me start from the beginning. The XML I must process serves as a database snapshot: the user presses a button on the GUI and the XML file is created. If the user decides to go back to a certain state he loads the corresponding XML and presses a button. Then comes what I need help for: the XML should then be loaded into the database (first the DB is cleared, of course).
You have pinpointed the problems precisely. The rows in the XML will not always contain all columns (null columns are completely omitted) and I need to match the names of the attributes inside the row nodes to an SQL column. I would like to do this automatically so that I do not need to modify the loading process if the data structure changes in the future.

Thank you again for your help.

Best Regards,
Daniel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources