Upload multiple XML files into XML data type columns

SQL Server database administrators often have some needs such as importing multiple files into a table in the database. This article will discuss how to upload multiple files (especially XML files) into the XML data column of the SQL Server database.

Prerequisites

a. Make sure xp_cmdshell is allowed. If not, execute some of the commands below

use master
go
sp_configure 'show advanced options', 1
go
reconfigure with override
go
sp_configure 'xp_cmdshell', 1
go
reconfigure with override
go

b. Create a database and the table used for this import using the following commands. If not, you can use existing databases.

USE [master]
GO
/ ****** Object: Database [XMLTest]
Script Date: 04/17/2007 01:49:43 ****** /
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'XMLTest ')
DROP DATABASE [XMLTest]
go
create database XMLTest
go
use XMLTest
go

c. Make sure you have created a table with an XML data type. Also, make sure that at least one column in the table can contain the value of the file name as follows

 use [XMLTest] / ****** object: table [dbo]. [myimage] script date: 09/10/2006 21:55:46 ****** / if exists (select * from sys.objects where object_id = object_id (N '[MYXML]') and type in (N'u ')) drop table [MYXML] go create table [MYXML] ([id] int identity (1,1), [XML File Name] varchar (100), [Data] XML) Go 

d. Suppose you want to upload the entire .bmp file from the C: XML directory (see Figures 1 and 2) into the 'MYXML' table in the 'dbo' schema on the database 'XMLTest'

Figure 1

Content example Customer5.xml

 2007-03-31T06: 40: 38.0000000-05: 00 Rainbow.River 1AE A-Accessible 761 Stopped 30 2007-03-31T06: 40: 38.0000000-05: 00 Rainbow.River 1AE Not-Accessible 870 Stopped 30 2007-03- 31T06: 40: 38.0000000-05: 00 Rainbow.River 1AE A-Accessible 97F Started 30 

e. Create a usp_uploadXMLFilesm procedure [use copy and paste the code below or download the existing usp_uploadXMLFiles file]. This creation is to create a stored procedure usp_uploadXMLfiles on the master database so it can execute and call any database.

USE [master]
GO
/ ****** Object: StoredProcedure [dbo]. [Usp_uploadXMLfiles] Script Date: 09/10/2006 23:33:34 ****** /
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID (N '[dbo]. [Usp_uploadXMLfiles]') AND type in (N'P ', N'PC'))
DROP PROCEDURE [dbo]. [Usp_uploadXMLfiles]
go
set quoted_identifier off
go
create procedure usp_uploadXMLfiles
@databasename varchar (128),
@schemaname varchar (128),
@tablename varchar (128),
@FileNameColumn varchar (128),
@xmlcolumn varchar (128),
@path varchar (500),
@filetype varchar (10),
@printorexec varchar (5) = 'print'
as
đặt nocount trên
declare @dircommand varchar (1500)
declare @insertquery varchar (2000)
declare @updatequery varchar (2000)
declare @count int
declare @maxcount int
declare @filename varchar (500)
set @ count = 1
set @dircommand = 'dir / b' + @ path + @ filetype
create #dir table (name varchar (1500))
insert #dir (name) exec master.xp_cmdshell @dircommand
delete from #dir where name is NULL
create table # dir2 (ident ident id (1,1), name varchar (1500))
chèn vào một tên danh sách dir2 từ #dir
--select * from # dir2
set @maxcount = ident_current ('# dir2')
print 'set quoted_identifier off'
print 'go'
while @count <= @ maxcount
begin
set @filename = (select name from # dir2 where id = @count)
set @insertquery = 'Insert into [' + @ databasename + ']. [' + @ schemaname + ']. [' + @ tablename + ']
(['+ @ filenamecolumn +']) values ​​("'+ @ filename +'") '
set @updatequery = 'update [' + @ databasename + ']. [' + @ schemaname + ']. [' + @ tablename + ']
set ['+ @ xmlcolumn +'] = (SELECT * FROM OPENROWSET (BULK "'+ @ path + @ filename +'", SINGLE_BLOB) AS x)
WHERE ['+ @ filenamecolumn +'] = "'+ @ filename +'" '
if @printorexec = 'print'
begin
print @insertquery
print @updatequery
end
if @printorexec = 'exec'
begin
set @ insertquery = 'set quoted_identifier off' + char (10) + char (13) + @ insertquery
set @ updatequery = 'set quoted_identifier off' + char (10) + char (13) + @ updatequery
exec (@insertquery)
exec (@updatequery)
end
set @count = @count +1
end
go

This procedure accepts the following parameters:

@databasename = The name of the database has an existing schema and table.

@schemaname = Reducing database of tables with existing tables

@tablename = Name of the table where the files will be uploaded

@FileNameColumn = The name of the column in the table with the file name to store

@XMLcolumn = Columns of existing XML data types with files will be stored as XML

@path = Path of all files needed to upload. Example 'C: Windows'

@filetype = Type of file to upload. For example '* .XML'

@printorexec = If 'Print' is set as a parameter, it will create and display commands. If 'Exec' is set as a parameter, it will immediately execute the command which means uploading all files.

f. Execute the procedure with the parameter printorexec = 'print' according to the code below

Exec master . [usp_uploadXMLfiles]
@databasename = 'XMLTest',
@schemaname = 'dbo',
@tablename = 'MYXML',
@FileNameColumn = 'XML File Name',
@XMLcolumn = 'Data',
@path = 'c: XML',
@filetype = '*. xml',
@printorexec = 'print'

This procedure will create all the necessary commands for creating one line for each file and update the line with the attribute file according to the following code.

set quoted_identifier off
go
Insert into [XMLTest]. [Dbo]. [MYXML] ([XML File Name]) values ​​("Customer1.xml")
update [XMLTest]. [dbo]. [MYXML] set [Data] = (SELECT *
FROM OPENROWSET (BULK "c: XMLCustomer1.xml", SINGLE_BLOB) AS x)
WHERE [XML File Name] = "Customer1.xml"
Insert into [XMLTest]. [Dbo]. [MYXML] ([XML File Name]) values ​​("Customer2.xml")
update [XMLTest]. [dbo]. [MYXML] set [Data] = (SELECT *
FROM OPENROWSET (BULK "c: XMLCustomer2.xml", SINGLE_BLOB) AS x)
WHERE [XML File Name] = "Customer2.xml"
Insert into [XMLTest]. [Dbo]. [MYXML] ([XML File Name]) values ​​("Customer3.xml")
update [XMLTest]. [dbo]. [MYXML] set [Data] = (SELECT *
FROM OPENROWSET (BULK "c: XMLCustomer3.xml", SINGLE_BLOB) AS x)
WHERE [XML File Name] = "Customer3.xml"
Insert into [XMLTest]. [Dbo]. [MYXML] ([XML File Name]) values ​​("Customer4.xml")
update [XMLTest]. [dbo]. [MYXML] set [Data] = (SELECT *
FROM OPENROWSET (BULK "c: XMLCustomer4.xml", SINGLE_BLOB) AS x)
WHERE [XML File Name] = "Customer4.xml"
Insert into [XMLTest]. [Dbo]. [MYXML] ([XML File Name]) values ​​("Customer5.xml")
update [XMLTest]. [dbo]. [MYXML] set [Data] = (SELECT *
FROM OPENROWSET (BULK "c: XMLCustomer5.xml", SINGLE_BLOB) AS x)
WHERE [XML File Name] = "Customer5.xml"
Insert into [XMLTest]. [Dbo]. [MYXML] ([XML File Name]) values ​​("Customer6.xml")
update [XMLTest]. [dbo]. [MYXML] set [Data] = (SELECT *
FROM OPENROWSET (BULK "c: XMLCustomer6.xml", SINGLE_BLOB) AS x)
WHERE [XML File Name] = "Customer6.xml"
Insert into [XMLTest]. [Dbo]. [MYXML] ([XML File Name]) values ​​("Customer7.xml")
update [XMLTest]. [dbo]. [MYXML] set [Data] = (SELECT *
FROM OPENROWSET (BULK "c: XMLCustomer7.xml", SINGLE_BLOB) AS x)
WHERE [XML File Name] = "Customer7.xml"

g. Execute the procedure with the printorexec = 'exec' parameter under the code below

Exec master . [usp_uploadXMLfiles]
@databasename = 'XMLTest',
@schemaname = 'dbo',
@tablename = 'MYXML',
@FileNameColumn = 'XML File Name',
@XMLcolumn = 'Data',
@path = 'c: XML',
@filetype = '*. xml',
@printorexec = 'exec'

The code will upload all xml files

hour. Now we will query the table using the SQL statement executed as below

use XMLTest
go
select * from myxml
go

This procedure will return the result as shown in Figure 2:

Figure 2

i. Click on the XML data connections to generate the results as shown below

 2007-03-3106: 40: 38.0000000-05: 00 Dancing.Doll 1AE A-Accessible 761 Stopped 30 2007-03-31T06: 40: 38.0000000-05: 00 1AE Not-Accessible Dancing.Doll 200770- 31T06: 40: 38.0000000-05: 00 Dancing.Doll 1AE A-Accessible 97F Started 30