XML bulk export and import

Recently we came across one scenario in our project where we have to decide best option to store spreadsheet data in the database.

we have finalized four options and out of those options one is to store the data in XML datatype column.

To check the performance of this option, we decided to create one sample xml file which stores 50000 raw data with 10 columns.

Here is script we used in this scenario.

— table used to insert random data

–CREATE TABLE [dbo].[Table_Data](
—    [Table_Id] [int] NOT NULL,
—    [RowId] [int] NOT NULL,
—    [ColumnId] [int] NOT NULL,
—    [Data] [nvarchar](500) NOT NULL
–) ON [PRIMARY]

–GO

— Table used to import data

CREATE TABLE [dbo].[XML_Data](
    [XMLDATA] [int] NOT NULL
)

— 1) Script to insert sample data in sql table

–declare @rows int = 50000
–declare @cols int = 10

–while(@rows > 0)
–begin
—    while(@cols > 0)
—    begin
—        insert into table_data values (1,@rows,@cols, cast(@cols as varchar) + cast(@rows as varchar))
—        set @cols = @cols – 1
—    end
—    set @cols = 10
—    set @rows = @rows – 1
–end

— 2)  Script to count the no of records inserted in sql table

— SELECT COUNT(1) from BOP_TEST.dbo.table_data with (nolock) where rowid >1950000

— 3) create sample xml file from sql table records

— a) enerates xml file from sql table data using BCP

— following commands needs to be executed to enable xp_cmdshell command in sql

–EXEC sp_configure ‘show advanced options’, 1
–RECONFIGURE

–EXEC sp_configure ‘xp_cmdshell’, 1
–RECONFIGURE

— query which generates xml file from sql table data

–declare @cmd nvarchar(255);

—- Please note that the fully qualified table name is needed
–select @cmd = ‘
—    bcp “SELECT * from BOP_TEST.dbo.table_data with (nolock) where rowid >1950000 FOR XML PATH(”DemoModel”), root(”root”)” ‘ +
—    ‘queryout “d:\sample.xml” -S DSK-358\SQLEXPRESS -T -w -r -t’;

–exec xp_cmdshell @cmd;
–go

— b) construct xml and output the xml in the output window of sql

–SELECT  * from table_data with (nolock) where rowid >1950000
–FOR XML PATH(‘DemoModel’), root(‘root’)

–4) insert the xml file content in the table which has one column as xmltype

–INSERT INTO table_data(data)
–SELECT * FROM OPENROWSET(
—   BULK ‘d:\sample.xml’,
—   SINGLE_BLOB) AS XmlFileStorage

This entry was posted in SQL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s