Upload nhiều file XML vào cột dữ liệu XML

Bài viết này sẽ thảo luận đến việc làm thế nào để upload nhiều file (đặc biệt là file XML) vào cột kiểu dữ liệu XML của cơ sở dữ liệu SQL Server. Bước 1: Phải đảm bảo xp_cmdshell đã được cho phép. Nếu chưa được, hãy thực thi một số câu lệnh dưới đây use...

Bài viết này sẽ thảo luận đến việc làm thế nào để upload nhiều file (đặc biệt là file XML) vào cột kiểu dữ liệu XML của cơ sở dữ liệu SQL Server.

Bước 1:

Phải đảm bảo xp_cmdshell đã được cho phép. Nếu chưa được, hãy thực thi một số câu lệnh dưới đây

view plainprint?

1. use master
2. go
3. sp_configure 'show advanced options',1
4. go
5. reconfigure with override
6. go
7. sp_configure 'xp_cmdshell',1
8. go
9. reconfigure with override
10. go

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ước 2:

Tạo một CSDL và bảng được dùng cho việc import này bằng các câu lệnh dưới. Nếu không bạn có thể sử dụng CSDL đang có sẵn.

view plainprint?

1. USE [master]
2. GO
3. /****** Object: Database [XMLTest]
4. Script Date: 04/17/2007 01:49:43 ******/
5. IF EXISTS (SELECT name FROM sys.databases WHERE name = N'XMLTest')
6. DROP DATABASE [XMLTest]
7. go
8. create database XMLTest
9. go
10. use XMLTest
11. go

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

Bước 3:

Đảm bảo rằng bạn đã tạo một bảng với kiểu dữ liệu XML. Ngoài ra, hãy chắc chắn có ít nhất một cột trong bảng có thể chứa giá trị của tên file như sau

view plainprint?

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

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

Bước 4:

Giả sử bạn muốn upload toàn bộ file .bmp từ thư mục C:\XML vào bảng “MYXML” trong lược đồ “dbo” trên cơ sở dữ liệu “XMLTest”

Nội dung ví dụ Customer.xml

view plainprint?

1. "1.0" standalone="yes"?>
4. 2007-03-31T06:40:38.0000000-05:00
5. Rainbow.River
6. 1AE
7. A-Accessible
8. 761
9. Stopped
10. 30
13. 2007-03-31T06:40:38.0000000-05:00
14. Rainbow.River
15. 1AE
16. Not-Accessible
17. 870
18. Stopped
19. 30
22. 2007-03-31T06:40:38.0000000-05:00
23. Rainbow.River
24. 1AE
25. A-Accessible
26. 97F
27. Started
28. 30

<?xml version="1.0" standalone="yes"?> <customer> <customerloginfo> <date>2007-03-31T06:40:38.0000000-05:00</date> <user>Rainbow.River</user> <userid>1AE</userid> <ls>A-Accessible</ls> <eqtid>761</eqtid> <es>Stopped</es> <tp>30</tp> </customerloginfo> <customerloginfo> <date>2007-03-31T06:40:38.0000000-05:00</date> <user>Rainbow.River</user> <userid>1AE</userid> <ls>Not-Accessible</ls> <eqtid>870</eqtid> <es>Stopped</es> <tp>30</tp> </customerloginfo> <customerloginfo> <date>2007-03-31T06:40:38.0000000-05:00</date> <user>Rainbow.River</user> <userid>1AE</userid> <ls>A-Accessible</ls> <eqtid>97F</eqtid> <es>Started</es> <tp>30</tp> </customerloginfo> </customer>

Bước 5:

Hãy tạo một thủ tục usp_uploadXMLFilesm [sử dụng copy và paste đoạn mã dưới đây hoặc download file usp_uploadXMLFiles có sẵn]. Việc tạo này là tạo một thủ tục lưu trữ usp_uploadXMLfiles trên cơ sở dữ liệu chủ vì vậy nó có thể thực thi và gọi bất kỳ cơ sở dữ liệu nào.

view plainprint?

1. USE [master]
2. GO
3. /****** Object: StoredProcedure [dbo].[usp_uploadXMLfiles] Script Date: 09/10/2006 23:33:34 ******/
4. IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_uploadXMLfiles]') AND type in (N'P', N'PC'))
5. DROP PROCEDURE [dbo].[usp_uploadXMLfiles]
6. go
7. set quoted_identifier off
8. go
9. create procedure usp_uploadXMLfiles
10. @databasename varchar(128),
11. @schemaname varchar(128),
12. @tablename varchar(128),
13. @FileNameColumn varchar(128),
14. @xmlcolumn varchar(128),
15. @path varchar(500),
16. @filetype varchar(10),
17. @printorexec varchar(5) = 'print'
18. as
19. set nocount on
20. declare @dircommand varchar(1500)
21. declare @insertquery varchar(2000)
22. declare @updatequery varchar(2000)
23. declare @count int
24. declare @maxcount int
25. declare @filename varchar(500)
26. set @count=1
27. set @dircommand = 'dir /b '+@path+@filetype
28. create table #dir (name varchar(1500))
29. insert #dir(name) exec master..xp_cmdshell @dircommand
30. delete from #dir where name is NULL
31. create table #dir2 (id int identity(1,1),name varchar(1500))
32. insert into #dir2 select name from #dir
33. --select * from #dir2
34. set @maxcount = ident_current('#dir2')
35. print 'set quoted_identifier off'
36. print 'go'
37. while @count <=@maxcount
38. begin
39. set @filename =(select name from #dir2 where id = @count)
40. set @insertquery = 'Insert into ['+@databasename+'].['+@schemaname+'].['+@tablename+']
41. ([' +@filenamecolumn +']) values ("mailto:'+@filename+'%22)'
42. set @updatequery = 'update ['+@databasename+'].['+@schemaname+'].['+@tablename+']
43. set ['+@xmlcolumn+'] = (SELECT * FROM OPENROWSET(BULK "'+@path+@filename+'", SINGLE_BLOB)AS x )
44. WHERE ['+@filenamecolumn +']="mailto:'+@filename+'%22'
45. if @printorexec ='print'
46. begin
47. print @insertquery
48. print @updatequery
49. end
50. if @printorexec ='exec'
51. begin
52. set @insertquery='set quoted_identifier off' + char(10)+char(13)+@insertquery
53. set @updatequery='set quoted_identifier off' + char(10)+char(13)+@updatequery
54. exec (@insertquery)
55. exec (@updatequery)
56. end
57. set @count = @count +1
58. end
59. go

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 set nocount on 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 table #dir (name varchar(1500)) insert #dir(name) exec master..xp_cmdshell @dircommand delete from #dir where name is NULL create table #dir2 (id int identity(1,1),name varchar(1500)) insert into #dir2 select name from #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 ("mailto:'+@filename+'%22)' set @updatequery = 'update ['+@databasename+'].['+@schemaname+'].['+@tablename+'] set ['+@xmlcolumn+'] = (SELECT * FROM OPENROWSET(BULK "'+@path+@filename+'", SINGLE_BLOB)AS x ) WHERE ['+@filenamecolumn +']="mailto:'+@filename+'%22' 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

Thủ tục này chấp nhận các tham số sau:

@databasename = Tên của cơ sở dữ liệu có giản đồ và bảng đang tồn tại.

@schemaname = Giảm đồ cơ sở dữ liệu có bảng đang tồn tại

@tablename = Tên của bảng có các file sẽ được upload

@FileNameColumn = Tên của cột trong bảng có tên file để lưu trữ

@XMLcolumn = Cột kiểu dữ liệu XML hiện tại có file sẽ được lưu trữ như XML

@path = Đường dẫn của tất cả các file cần có để upload. Ví dụ “C:\Windows\”

@filetype = Loại file muốn upload. Ví dụ “*.XML”

@printorexec = Nếu “Print” được đặt như một tham số, nó sẽ tự tạo ra và hiển thị các lệnh. Nếu “Exec” được đặt như một tham số, nó sẽ ngay lập tức thực thi câu lệnh có ý nghĩa upload tất cả các file.

Bước 6:

Thực thi thủ tục với tham số printorexec=’print’ theo đoạn mã dưới đây

view plainprint?

1. Exec master..[usp_uploadXMLfiles]
2. @databasename ='XMLTest',
3. @schemaname ='dbo',
4. @tablename ='MYXML',
5. @FileNameColumn ='XML File Name',
6. @XMLcolumn = 'Data',
7. @path = 'c:\XML\',
8. @filetype ='*.xml',
9. @printorexec ='print'

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

Thủ tục này sẽ tự tạo ra tất cả các câu lệnh cần thiết cho việc tạo một dòng cho từng file và update dòng với file thuộc tính theo đoạn mã sau.

view plainprint?

1. set quoted_identifier off
2. go
3. Insert into [XMLTest].[dbo].[MYXML] ([XML File Name]) values ("Customer1.xml")
4. update [XMLTest].[dbo].[MYXML] set [Data] = (SELECT *
5. FROM OPENROWSET(BULK "c:\XML\Customer1.xml", SINGLE_BLOB)AS x )
6. WHERE [XML File Name]="Customer1.xml"
7. Insert into [XMLTest].[dbo].[MYXML] ([XML File Name]) values ("Customer2.xml")
8. update [XMLTest].[dbo].[MYXML] set [Data] = (SELECT *
9. FROM OPENROWSET(BULK "c:\XML\Customer2.xml", SINGLE_BLOB)AS x )
10. WHERE [XML File Name]="Customer2.xml"
11. Insert into [XMLTest].[dbo].[MYXML] ([XML File Name]) values ("Customer3.xml")
12. update [XMLTest].[dbo].[MYXML] set [Data] = (SELECT *
13. FROM OPENROWSET(BULK "c:\XML\Customer3.xml", SINGLE_BLOB)AS x )
14. WHERE [XML File Name]="Customer3.xml"
15. Insert into [XMLTest].[dbo].[MYXML] ([XML File Name]) values ("Customer4.xml")
16. update [XMLTest].[dbo].[MYXML] set [Data] = (SELECT *
17. FROM OPENROWSET(BULK "c:\XML\Customer4.xml", SINGLE_BLOB)AS x )
18. WHERE [XML File Name]="Customer4.xml"
19. Insert into [XMLTest].[dbo].[MYXML] ([XML File Name]) values ("Customer5.xml")
20. update [XMLTest].[dbo].[MYXML] set [Data] = (SELECT *
21. FROM OPENROWSET(BULK "c:\XML\Customer5.xml", SINGLE_BLOB)AS x )
22. WHERE [XML File Name]="Customer5.xml"
23. Insert into [XMLTest].[dbo].[MYXML] ([XML File Name]) values ("Customer6.xml")
24. update [XMLTest].[dbo].[MYXML] set [Data] = (SELECT *
25. FROM OPENROWSET(BULK "c:\XML\Customer6.xml", SINGLE_BLOB)AS x )
26. WHERE [XML File Name]="Customer6.xml"
27. Insert into [XMLTest].[dbo].[MYXML] ([XML File Name]) values ("Customer7.xml")
28. update [XMLTest].[dbo].[MYXML] set [Data] = (SELECT *
29. FROM OPENROWSET(BULK "c:\XML\Customer7.xml", SINGLE_BLOB)AS x )
30. WHERE [XML File Name]="Customer7.xml"

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:\XML\Customer1.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:\XML\Customer2.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:\XML\Customer3.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:\XML\Customer4.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:\XML\Customer5.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:\XML\Customer6.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:\XML\Customer7.xml", SINGLE_BLOB)AS x ) WHERE [XML File Name]="Customer7.xml"

Bước 7:

Thực thi thủ tục với tham số printorexec=’exec’ theo đoạn mã dưới

view plainprint?

1. Exec master..[usp_uploadXMLfiles]
2. @databasename ='XMLTest',
3. @schemaname ='dbo',
4. @tablename ='MYXML',
5. @FileNameColumn ='XML File Name',
6. @XMLcolumn = 'Data',
7. @path = 'c:\XML\',
8. @filetype ='*.xml',
9. @printorexec ='exec'

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

Đoạn mã sẽ upload toàn bộ các file xml

Bước 8:

Giờ thì chúng ta sẽ truy vấn bảng sử dụng câu lệnh SQL thực hiện như đoạn dưới

1. use XMLTest
2. go
3. select * from myxml
4. go

use XMLTest go select * from myxml go

Thủ tục này sẽ trả về kết quả như hình dưới:

Bước 9:

Kích lên các kết nối dữ liệu XML để phát sinh kết quả như dưới đây

3. 2007-03-31T06:40:38.0000000-05:00
4. Dancing.Doll
5. 1AE
6. A-Accessible
7. 761
8. Stopped
9. 30
12. 2007-03-31T06:40:38.0000000-05:00
13. Dancing.Doll
14. 1AE
15. Not-Accessible
16. 870
17. Stopped
18. 30
21. 2007-03-31T06:40:38.0000000-05:00
22. Dancing.Doll
23. 1AE
24. A-Accessible
25. 97F
26. Started
27. 30

<customer> <customerloginfo> <date>2007-03-31T06:40:38.0000000-05:00</date> <user>Dancing.Doll</user> <userid>1AE</userid> <ls>A-Accessible</ls> <eqtid>761</eqtid> <es>Stopped</es> <tp>30</tp> </customerloginfo> <customerloginfo> <date>2007-03-31T06:40:38.0000000-05:00</date> <user>Dancing.Doll</user> <userid>1AE</userid> <ls>Not-Accessible</ls> <eqtid>870</eqtid> <es>Stopped</es> <tp>30</tp> </customerloginfo> <customerloginfo> <date>2007-03-31T06:40:38.0000000-05:00</date> <user>Dancing.Doll</user> <userid>1AE</userid> <ls>A-Accessible</ls> <eqtid>97F</eqtid> <es>Started</es> <tp>30</tp> </customerloginfo> </customer>