SỬ DỤNG XML TRONG SQL SEVER | Duemcoquenanh's Blog

1. KIỂU DỮ LIỆU XML – Kiểu dữ liệu XML được sử dụng để lưu trữ tài liệu và các phân đoạn XML trong CSDL SQL Server. – Cột có kiểu XML không thể được sử dụng làm khóa chính, khóa ngoại hay khóa duy nhất. Tạo các biến kiểu xml và các cột Kiểu dữ liệu xml là một kiểu dữ liệu được tích hợp sẵn trong SQL Sever và có phần giống với các kiểu dữ liệu khác được tích hợp sẵn như int hay varchar. Cũng như các kiểu dữ liệu có sẵn khác, bạn có thể sử dụng kiểu dữ liệu xml như một cột dữ liệu khi bạn tạo một table như kiểu biến, tham số, kiểu hàm trả về hoặc trong CAST và CONVERT. Ví dụ 1: Tạo một cột kiểu xml CREATE TABLE Tex1(Col1 int primary key, Col2 xml) Ví dụ 2: Tạo một biến của kiểu xml DECLARE @x xml Tạo một biến kiểu xml bằng cách chỉ định tập hợp lược đồ XML (XML schema collection) DECLAR @x xml(Sales.StoreSurveyCollection) Ví dụ 3: Truyền một tham số kiểu xml tới một thủ tục (stored procedure) CREATE PROCEDURE SampleProc(@xmlDoc xml) AS Gán giá trị mặc định Trong một Table bạn có thể gán 1 xml mặc định tới 1 cột kiểu xml. Bạn có thể cung cấp xml mặc định bằng 1 trong 2 cách sau: Ví dụ 1 Cung cấp XML mặc định như là một hằng XML(chuỗi được chuyển ngầm định CAST tới kiểu xml) CREATE TABLE T (xmlCol xml default N„‟) Ví dụ 2: Cung cấp XML mặc định như một tường minh CAST tới kiểu xml CREATE TABLE T(xmlCol xml default CAST(N„‟) AS xml) Ngoài ra SQL Server cũng hỗ trợ ràng buộc NULL và NOT NULL tên cột dữ liệu kiểu xml Ví dụ: CREATE TABLE T (xmlCol xml NOT NULL) Chỉ định ràng buộc Khi tạo các cột kiểu xml, có thể xác định mức độ ràng buộc của Column, Table. Tuy nhiên, không thể sử dụng phương thức kiểu dữ diệu XML (XML Data Type Methods) khi chỉ định ràng buộc. Cách khác để thay thế là tạo ra một vỏ bọc (wrapper), sau đó sử dụng chức năng người dùng tự định nghĩa (user-defined function) để bọc các phương thức kiểu dữ liệu XML (XML Data Type Methods) trong việc kiểm tra các ràng buộc. Ví dụ: Trong ví dụ sau, ràng buộc trên cột Col2 quy định mỗi bản XML được lưu trữ trong cột này phải có một thành phần chứa thuộc tính ProductID. Ràng buộc này được thực thi bằng tính năng người dùng tự định nghĩa CREATE FUNCTION myUDF(@var xml) returns bit AS BEGIN RETURN @var.exist(„/ProductDescription/@ProductID‟) END GO Lưu ý: phương thức exist() của kiểu dữ liệu xml trả về 1 nếu thành phần có chứa các thuộc tính ProductID, ngược lại trả về 0. Sau khi tạo hàm myUDF thì có thể tạo Table có ràng buộc. CREATE TABLE T ( Col1 int primary key, Col2 xml check(dbo.myUDF(Col2)=1) GO Chèn dữ liệu thành công INSERT INTO T VALUES(1, „ Do có ràng buộc nên cách chèn sau đây bị lỗi INSERT INTO T VALUES(1, „‟) Chỉnh sửa Table Sử dụng Alter Table CREATE TABLE T (Col1 int primary key, Col2 nvarchar(max)) GO INSERT INTO T VALUES (1, ”) GO ALTER TABLE T ALTER COLUMN Col2 xml GO Cũng có thể thay đổi một cột kiểu xml từ Untyped XML sang Typed XML CREATE TABLE T (Col1 int primary key, Col2 xml) GO INSERT INTO T VALUES ( 1, „ ‟) GO — Make it a typed xml column by specifying a schema collection. ALTER TABLE T ALTER COLUMN Col2 xml ( Production.ProductDescrip tionSchemaCollection) GO Tạo Views Có thể sử dụng một cột kiểu xml để tạo View –Create the table CREATE TABLE T ( ProductID int primary key, CatalogDescription xml) –Insert sample data INSERT INTO T values(1,”) GO — Create view (note the value() method used to retrieve ProductName — attribute value from the XML). CREATE VIEW MyView AS SELECT ProductID, CatalogDescription.value(‘(/ProductDescription/@ProductName)[1]’, ‘varchar(40)’) AS PName FROM T GO Thực hiện câu lệnh truy vấn SELECT * FROM MyView Kết quả ProductID PName ———– ————1 SomeName 2. XML DATA TYPE MEDTHODS a. Query() Method(xml Data Type) Sử dùng phương thức này để truy vấn trên 1 đối tượng XML Cú pháp: Query („Xquery‟) Đối số: Xquery: Là một chuỗi, một biểu thức Xquery để truy vấn cho các nút XML đó có thể là một phần tử, một thuộc tính trong đối tượng XML. Ví dụ sử dụng:  Sử dụng phương thức Query() với một biến kiểu XML Ví dụ sau đây khai báo một biến @myDoc của kiểu XML và gán một đối tượng XML tới nó. Truy vấn này lấy ra thành phần con của thành phần declare @myDoc xml set @myDoc = ‘

1 year parts and labor 3 year parts and labor extended maintenance is available

‘ SELECT @myDoc.query(‘/Root/ProductDescription/Features’) Kết quả hiển thị:

1 year parts and labor 3 year parts and labor extended maintenance is avai lable

 Sử dụng phương thức Query() với một column kiểu XML Theo ví dụ dưới đây thì phương thức Query được chỉ định một Xquery tới cột CatalogsDescription thuộc kiểu XML trong CSDL AdvantureWorks SELECT CatalogDescription.query(‘ declare namespace PD=”http://schemas.microsoft.com/sqlserver/2004/07/adventureworks/ProductModelDescription”; ‘) as Result FROM Production.ProductModel where CatalogDescription.exist(‘ declare namespace PD=”http://schemas.microsoft.com/sqlserver/2004/07/adventureworks/ProductModelDescription”; declare namespace wm=”http://schemas.microsoft.com/sqlserver/2004/07/adventureworks/ProductModelWarrAndMain”; /PD:ProductDescription/PD:Features/wm:Warranty ‘) = 1 Kết quả hiển thị b. Value() Method(xml Data Type) Thực hiện một Xquery đối với XML và trả về một giá trị kiểu SQL. Phương thức này trả về một giá trị vô hướng. Phương thức này thường được sử dụng để trích xuất một giá trị từ một đối tượng XML được lưu trữ trong cột kiểu XML, tham số hoặc là biến. Bằng cách này có thể sử dụng phát biểu SELECT để kết hợp hoặc so sánh những cột có kiểu dữ liệu XML với dữ liệu trong những cột không thuộc kiểu XML. Cú pháp: Value (Xquery,SQLType) Đối số: Xquery: Là một chuỗi, một biểu thức Xquery để truy vấn dữ liệu bên trong đối tượng XML. Các Xquery phải trả về ít nhất 1 giá trị. SQLType: Là một kiểu người dùng tự định nghĩa Ví dụ sử dụng:  Sử dụng phương thức Value() với một biến kiểu XML Trong ví dụ dưới đây, một đối tượng XML được lưu trữ trong một biến có kiểu XML. Phương thức Value() sẽ lấy 1 giá trị của thuộc tính ProductID từ XML và giá trị này sau đó sẽ được gán cho 1 biến kiểu int. DECLARE @myDoc xml DECLARE @ProdID int SET @myDoc = ‘

1 year parts and labor 3 year parts and labor extended maintenance is available

‘ SET @ProdID = @myDoc.value(‘(/Root/ProductDescription/@ProductID)[1]’, ‘int’ ) SELECT @ProdID Kết quả hiển thị: Giá trị trả về: 1  Sử dụng phương thức Value() để lấy về giá trị từ một cột kiểu XML Các truy vấn dưới đây được xác định đối với một cột dữ liệu kiểu xml (CatalogDescription) trong Database AdventureWorks. Truy vấn sẽ lấy những giá trị của thuộc tính ProductModelID từ những đối tượng XML được lưu trữ trong cột SELECT CatalogDescription.value(‘ declare namespace PD=”http://schemas.microsoft.com/sqlserver/2004/07/adventureworks/ProductModelDescription&#8221;; (/PD:ProductDescription/@ProductModelID)[1]’, ‘int’) AS Result FROM Production.ProductModel WHERE CatalogDescription IS NOT NULL ORDER BY Result desc Kết quả hiển thị: ———–35 34 …  Sử dụng phương thức Value() và Exist() để lấy về giá trị từ một cột kiểu XML Ví dụ dưới đây sử dụng cả 2 phương thức value() và exist() của kiểu dữ liệu xml. Phương thức value() được sử dụng để lấy giá trị thuộc tính ProductModelID từ XML. Phương thức exist() trong mệnh đề WHERE được sử dụng để lọc ra những dòng dữ liệu từ table. SELECT CatalogDescription.value(‘ declare namespace PD=”http://schemas.microsoft.com/sqlserver/2004/07/adventureworks/ProductModelDescription&#8221;; (/PD:ProductDescription/@ProductModelID)[1] ‘, ‘int’) as Result FROM Production.ProductModel WHERE CatalogDescription.exist(‘ declare namespace PD=”http://schemas.microsoft.com/sqlserver/2004/07/adventureworks/ProductModelDescription&#8221;; declare namespace wm=”http://schemas.microsoft.com/sqlserver/2004/07/adventureworks/ProductModelWarrAndMain&#8221;; /PD:ProductDescription/PD:Features/wm:Warranty ‘) = 1 Kết quả hiển thị: Result ———–19 23 …  Sử dụng phương thức Exist() thay thế cho phương thức Value() Để tăng hiệu suất làm việc thì thay vì dùng phương thức Value() để so sánh với 1 giá trị quan hệ thì có thể sử dụng phương thức exist() với cú pháp sql:column(). Ví dụ thay vì viết CREATE TABLE T (c1 int, c2 varchar(10), c3 xml) GO SELECT c1, c2, c3 FROM T WHERE c3.value( ‘/root[1]/@a’, ‘integer’) = c1 GO Thì có thể viết câu lệnh bằng cách này: SELECT c1, c2, c3 FROM T WHERE c3.exist( ‘/root[@a=sql:column(“c1″)]’) = 1 GO c. Exist() Method(xml Data Type) Phương thức Exist sẽ return về 1 trong 2 giá trị đại diện sai: 1 (True): Nếu biểu thức Xquery trong truy vấn trả về một giá trị khác NULL. Nghĩa là nó trả về ít nhất có 1 node XML. 0 (False): Nếu kết quả là NULL. Cú pháp: Exist (Xquery) Ví dụ sử dụng:  Xác định phương thức exist() đối với 1 biến kiểu xml Trong ví dụ dưới đây, 1 biến @x kiểu xml (untyped xml) và 1 biến @f kiểu integer được dùng để lưu trữ giá trị trả về từ phương thức exist(). Phương thức exist() sẽ trả về TRUE(1) nếu giá trị được lưu trữ trong đối tượng XML là 2002-01-01 declare @x xml declare @f bit set @x = ” set @f = @x.exist(‘/root[(@Somedate cast as xs:date?) eq xs:date(“2002-01-01Z”)]’) select @f  Xác định phương thức exist() đối với 1 biến định kiểu xml (typed xml) Trong ví dụ minh họa của việc sử dụng phương thức exist() đối với một biến kiểu xml. Nó là một biến định kiểu XML, bởi vì nó xác định tên lược đồ namespace collection ManuInstructionsSchemaCollection. Trong ví dụ này thì phương thức exist() được dùng để tìm document nào có phần tử có LocationID = 50 DECLARE @x xml (Production.ManuInstructionsSchemaCollection) SELECT @x=Instructions FROM Production.ProductModel WHERE ProductModelID=67 –SELECT @x DECLARE @f int SET @f = @x.exist(‘ declare namespace AWMI=”http://schemas.microsoft.com/sqlserver/2004/07/adventureworks/ProductModelManuInstructions&#8221;; /AWMI:root/AWMI:Location[@LocationID=50] ‘) SELECT @f  Xác định phương thức exist() đối với 1 cột kiểu xml Các truy vấn dưới đây lấy về các ID model sản phẩm có danh mục mô tả không bao gồm các chi tiết kỹ thuật: phần tử < Specifications> SELECT ProductModelID, CatalogDescription.query(‘ declare namespace pd=”http://schemas.microsoft.com/sqlserver/2004/07/adventureworks/ProductModelDescription&#8221;; ‘) AS Result FROM Production.ProductModel WHERE CatalogDescription.exist(‘ declare namespace pd=”http://schemas.microsoft.com/sqlserver/2004/07/adventureworks/ProductModelDescription&#8221;; /pd:ProductDescription[not(pd:Specifications)]’ ) = 1 d. Modify() Method(xml Data Type) Phương thức này dùng để sửa đổi nội dung của một cột hoặc một biến kiều xml. Phương thức này sử dụng một phát biểu XML DML để Insert, Update, Delete các node từ dữ liệu XML. Phương thức Modify() của kiểu dữ liệu xml chỉ có thể được sử dụng trong mệnh đề SET của phát biểu UPDATE Cú pháp: Modify (XML_DML) Đối số: XML_DML: Là dạng chuỗi trong ngôn ngữ thao tác dữ liệu XML (XML Data Manipulation Language (DML)). Tài liệu XML được cập theo biểu thức này. d. Nodes() Method(xml Data Type) Phương thức này rất hữu dụng khi muốn cắt nhỏ một thể loại dữ liệu xml sang dữ liệu quan hệ. Nó cho phép xác định các node mà sẽ được sắp xếp trên một dòng mới. Cú pháp: Nodes (XQuery) as Table(Column) Đối số: XQuery: Là dạng chuỗi trong ngôn ngữ thao tác dữ liệu XML (XML Data Manipulation Language (DML)). Tài liệu XML được cập theo biểu thức này.

Chia sẻ:

• Facebook
• X

Có liên quan