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
surilpatel 