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.

Bước 1

Đầu tiên, tạo một file C:\XML\Customer1.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.
<?xml version="1.0" standalone="yes"?>
<Customer>
<CustomerLogInfo>
<Date>2007-03-31T06:40:38.0000000-05:00</Date>
<user>james.brewer</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>james.brewer</user>
<Userid>1AE</Userid>

</xs:sequence></xs:complexType></xs:element> </xs:schema>'go
Chú ý: update tập hợp Schema dựa trên dữ liệu của riêng bạn trong file XML.

Bước 3

Tạo một bảng với kiểu dữ liệu XML:
USE [XMLTest]
GO
/****** Object: Table [dbo].[XMLFiles] Script Date: 04/17/2007 02:07:52 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[XMLFiles]') AND type in (N'U'))
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 4

Nhập file XML vừa tạo (C:\XML\Customer1.XML), sử dụng hàm openrowset như bên dưới:
USE [XMLTest]
go
INSERT INTO XMLFiles(Filename,DATA)
SELECT 'Customer1' a,*
FROM OPENROWSET( BULK 'C:\XML\Customer1.xml' ,SINGLE_CLOB)
as mytable
go
Chú ý: Từ khoá SINGLE_BLOB sẽ nhập toàn bộ file XML cho cột có kiểu dữ liệu XML.

Bước 5

FROM XMLFiles CROSS APPLY Data.nodes ('//Customer/CustomerLogInfo') R(ref)
where Fileid=1
Chương trình sẽ cho kết quả như Hình 1.2:
Date,User,Userid,ls,eqtid,es,tp 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

Hình 1.2
Bước 7

Bâ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:\XML\Customer1.xml' ,SINGLE_CLOB)as mytable go
Bước 8

Truy vấn bảng như bên dưới:
USE [XMLTest]goselect * from XMLFilesgo
Kết quả là:

Hình 1.3
Bướ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:
SELECT
ref.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],

nhập file XML vào bảng với kiểu dữ liệu XML, truy vấn file XML và đưa ra kết quả tương tự
như tập hợp kết quả thực hiện bởi các lệnh SQL Transact.
