Upload Multiple XML Files into XML Data Type Columns

The sections below explain upload multiple xml files into xml data type columns in a clear and practical way. Review the key details, examples, and considerations before applying the information.

Use master go Sp_configure 'show advanced options', 1 go Reconfigure with override go Sp_configure 'xp_cmdshell', 1 go Reconfigure with override go

How Upload Multiple XML Files into XML Data Type Columns Works

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 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