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>

xmlns:xs=" /><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'))

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

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