Truy vấn dữ liệu XML từ một bảng với kiểu dữ liệu XML

Mục đích của bài này nhằm hướng dẫn các quản trị viên cơ sở dữ liệu Microsoft SQL Server trong việc: Tạo giản đồ XML (XML Shema).Tạo một bảng với kiểu dữ liệu XML. Nhập file XML vào bảng với kiểu dữ liệu XML. Truy vấn file XML. Truy vấn file XML và đưa ra kết quả, tương tự như kết quả trả ra từ các lệnh Transact SQL Statement.
Nội dung trích xuất từ tài liệu:
Truy vấn dữ liệu XML từ một bảng với kiểu dữ liệu XMLTruy vấn dữ liệu XML từ một bảng với kiểu dữ liệu XMLMục đích của bài này nhằm hướng dẫn các quản trị viên cơ sở dữ liệu Microsoft SQLServer trong việc:• Tạo giản đồ XML (XML Shema).• Tạo một bảng với kiểu dữ liệu XML.• Nhập file XML vào bảng với kiểu dữ liệu XML.• Truy vấn file XML.• Truy vấn file XML và đưa ra kết quả, tương tự như kết quả trả ra từ các lệnhTransact SQL Statement.Bước 1Đầu tiên, tạo một file C:XMLCustomer1.XML như bên dưới. File XML này chứa dữliệu liên quan đến một khách hàng. 2007-03-31T06:40:38.0000000-05:00 james.brewer 1AE A-Accessible 761 Stopped 30 2007-03-31T06:40:38.0000000-05:00 james.brewer 1AE Not-Accessible 870 Stopped 30 2007-03-31T06:40:38.0000000-05:00 james.brewer 1AE A-Accessible 97F Started 30 Bước 2Tạo một cơ sở dữ liệu (CSDL) và một tập hợp XML Schema như bên dưới:USE [master]GO/****** Object: Database [XMLTest] Script Date: 04/17/2007 01:49:43******/IF EXISTS (SELECT name FROM sys.databases WHERE name = NXMLTest)DROP DATABASE [XMLTest]gocreate database XMLTestgouse XMLTestgoCreate XML Schema Collection XMLTrackasNgoChú ý: update tập hợp Schema dựa trên dữ liệu của riêng bạn trong file XML.Bước 3Tạo một bảng với kiểu dữ liệu XML:USE [XMLTest]GO/****** Object: Table [dbo].[XMLFiles] Script Date: 04/17/200702:07:52 ******/IF EXISTS (SELECT * FROM sys.objects WHERE object_id =OBJECT_ID(N[dbo].[XMLFiles]) AND type in (NU))DROP TABLE [dbo].[XMLFiles]create table XMLFiles(Fileid int identity(1,1),ImportedDate datetime constraint xmldatestamp default getdate(),Filename varchar(500),data xml (XMLTrack))Bước 4Nhập file XML vừa tạo (C:XMLCustomer1.XML), sử dụng hàm openrowset như bêndưới:USE [XMLTest]goINSERT INTO XMLFiles(Filename,DATA)SELECT Customer1 a,*FROM OPENROWSET( BULK C:XMLCustomer1.xml ,SINGLE_CLOB)as mytablegoChú ý: Từ khoá SINGLE_BLOB sẽ nhập toàn bộ file XML cho cột có kiểu dữ liệuXML.Bước 5Truy vấn bảng XMLFiles, sử dụng các thao tác SQL như bên dưới:USE [XMLTest]goselect * from XMLFiles where FileId=1goLệnh này sẽ đưa ra các kết quả sau: Hình 1.0Khi kích chuột lên dữ liệu, nó cũng hiển thị dữ liệu XML (Hình 1.1).2007-03-31T06:40:38.0000000-05:00james.brewer1AEA-Accessible761Stopped302007-03-31T06:40:38.0000000-05:00james.brewer1AENot-Accessible870Stopped302007-03-31T06:40:38.0000000-05:00james.brewer1AEA-Accessible97FStarted30 Hình 1.1Bước 6Bây giờ, truy vấn dữ liệu XML từ bảng để đưa ra một giao dịch SQL như tập hợp kếtquả. Thực thi chương trình XQuery như bên dưới:SELECTref.value (Date, nvarchar(364)) as [Date],ref.value (user, nvarchar(364)) as [User],ref.value (Userid, nvarchar(364)) as [Userid],ref.value (ls, nvarchar(364)) as [ls],ref.value (eqtid, nvarchar(364)) as [eqtid],ref.value (es, nvarchar(364)) as [es],ref.value (tp, nvarchar(364)) as [tp]FROM XMLFiles CROSS APPLY Data.nodes (//Customer/CustomerLogInfo)R(ref)where Fileid=1Chương trình sẽ cho kết quả như Hình 1.2:Date,User,Userid,ls,eqtid,es,tp2007-03-31T06:40:38.0000000-05:00,james.brewer,1AE,A-Accessible,761,Stopped,302007-03-31T06:40:38.0000000-05:00,james.brewer,1AE,Not-Accessible,870,Stopped,302007-03-31T06:40:38.0000000-05:00,james.brewer,1AE,A-Accessible,97F,Started,30 Hình 1.2Bước 7Bây giờ lặp lại bước 4 và nhập lại dữ liệu.USE [XMLTest]goINSERT INTO XMLFiles(Filename,DATA)SELECT Customer1 a,*FROM OPENROWSET( BULK C:XMLCustomer1.xml ,SINGLE_CLOB)as mytablegoBước 8Truy vấn bảng như bên dưới:USE [XMLTest]goselect * from XMLFilesgoKết quả là: Hình 1.3Bước 9Để hiển thị tất cả dữ liệu từ cả hai hàng, chúng ta có thể viết truy vấn như bên dưới:SELECTref.value (Date, nvarchar(364)) as [Date],ref.value (user, nvarchar(364)) as [User],ref.value (Userid, nvarchar(364)) as [Userid],ref.value (ls, nvarchar(364)) as [ls],ref.value (eqtid, nvarchar(364)) as [eqtid],ref.value (es, nvarchar(364)) as [es],ref.value (tp, nvarchar(364)) as [tp]FROM XMLFiles CROSS APPLY Data.nodes (//Customer/CustomerLogInfo)R(ref)where Fileid=1union allSELECTref.value (Date, nvarchar(364)) as [Date],ref.value (user, nvarchar(364)) as [User],ref.value (Userid, nvarchar(364)) as [Userid],ref.value (ls, nvarchar(364)) as [ls],ref.value (eqtid, nvarchar(364)) as [eqtid],ref.value (es, nvarchar(364)) as [es],ref.value (tp, nvarchar(364)) as [tp]FROM XMLFiles CROSS APPLY Data.nodes (//Customer/CustomerLogInfo)R(ref)where Fileid=2Kết quả có dạng ...