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>
<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>james.brewer</user>
<Userid>1AE</Userid>
<ls>A-Accessible</ls>
<eqtid>97F</eqtid>
<es>Started</es>
<tp>30</tp>
</CustomerLogInfo>
</Customer>

Bước 2

Tạ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 = N'XMLTest')
DROP DATABASE [XMLTest]
go
create database XMLTest
go
use XMLTest
go
Create XML Schema Collection XMLTrack
as
N'<xs:schema
attributeFormDefault="unqualified"
elementFormDefault="qualified"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Customer">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="CustomerLogInfo">
<xs:complexType>
<xs:sequence>
<xs:element name="Date" type="xs:string" />
<xs:element name="user" type="xs:string" />
<xs:element name="Userid" type="xs:string" />
<xs:element name="ls" type="xs:string" />
<xs:element name="eqtid" type="xs:string" />
<xs:element name="es" type="xs:string" />
<xs:element name="tp" type="xs:int" />
</xs:sequence>
</xs:complexType>
</xs:element>
</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

Truy vấn bảng XMLFiles, sử dụng các thao tác SQL như bên dưới:

USE [XMLTest]
go
select * from XMLFiles where FileId=1
go

Lệnh này sẽ đưa ra các kết quả sau:

Hình 1.0

Khi kích chuột lên dữ liệu, nó cũng hiển thị dữ liệu XML (Hình 1.1).

<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>
<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>james.brewer</user>
<Userid>1AE</Userid>
<ls>A-Accessible</ls>
<eqtid>97F</eqtid>
<es>Started</es>
<tp>30</tp>
</CustomerLogInfo>
</Customer>

Hình 1.1

Bước 6

Bâ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ết quả. Thực thi chương trình XQuery 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],
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=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]
go
INSERT 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]
go
select * from XMLFiles
go

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],
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=1
union all
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],
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=2

Kết quả có dạng như Hình 1.4.

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

Chú ý: Nếu bạn đang có kế hoạch hiển thị tất cả dữ liệu từ từng hàng có kiểu dữ liệu XML, bạn có thể tạo một thủ tục lưu trữ với bảng tạm thời hoặc con trỏ…

Hình 1.4

Kết luận

Bài này minh hoạ cho các bạn cách tạo một XML Schema, 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 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.