Sử dụng SQL XML và JSON trong SQL Server

XML trong SQL Server

XML là gì?

XML (eXtensible Markup Language) là một định dạng dữ liệu văn bản phổ biến, thường được sử dụng để lưu trữ dữ liệu phân cấp và có cấu trúc. Có rất nhiều cơ sở dữ liệu hỗ trợ lưu kiểu dữ liệu XML như IBM, DB2, Oracle, PostgreSQL và SQL Server.

Trong SQL Server, XML là một kiểu dữ liệu cơ bản được giới thiệu từ phiên bản SQL Server 2005. Nó cho phép lưu trữ và xử lý dữ liệu XML trong cơ sở dữ liệu SQL Server.

Ví dụ: Bảng thông tin nhân viên (Employee), cột thành viên gia đình (Family) đang lưu theo kiểu dữ liệu XML

Với EmployeeID = 1 thì thông tin Family như sau:

Trong đoạn dữ liệu XML, chúng ta có hai khái niệm quan trọng là thuộc tính và phần tử.

• Thuộc tính (Attributes): là các phần của các phần tử XML. Một phần tử có thể có nhiều nhãn thuộc tính duy nhất.
• Cú pháp của một thuộc tính XML như sau:

Ở đây, attribute1 và attribute2 là nhãn thuộc tính duy nhất. Giá trị của thuộc tính phải nằm trong dấu trích dẫn đơn (‘ ‘) hoặc trích dẫn kép (” “).

• Phần tử (Elements): là các khối xây dựng nên tài liệu XML. Mỗi phần tử có thể chứa một hoặc nhiều phần tử con. Phạm vi của phần tử được giới hạn bởi các thẻ đóng và thẻ mở hoặc được giới hạn bởi các phần tử trống.

Ví dụ: Ta có đoạn dữ liệu XML sau:

Nhìn vào ví dụ, ta thấy EmployeeID và FullName là thuộc tính của phần tử Employee. Các phần tử trong đoạn XML trên gồm Employees, Employee, Phone và DepartmentName.

Các hàm và phương thức xử lý dữ liệu XML

Các hàm và phương thức cho phép bạn dùng để truy vấn, tạo và xử lý dữ liệu XML như:

FOR XML

Chuyển đổi từ bảng SQL sang định dạng XML, sử dụng mệnh đề FOR XML AUTO hoặc FOR XML PATH.

FOR XML AUTO

Chuyển mỗi cột trong bảng SQL thành thuộc tính trong tài liệu XML tương ứng.

Ví dụ: Từ bảng Employee, chuyển dữ liệu sang dạng XML bằng FOR XML AUTO

FOR XML PATH

Tạo tài liệu XML với mỗi bản ghi là một phần tử và mỗi cột là một phần tử lồng nhau.

Ví dụ: Từ bảng Employee, chuyển dữ liệu sang dạng XML bằng FOR XML PATH

Theo mặc định thì tên phần tử cha là “row”. Chúng ta có thể thay đổi điều đó bằng cách sử dụng truy vấn sau:

Trong kết quả trả về, có thể thấy Employee là phần tử gốc cho từng phần tử phụ. Tuy nhiên, chưa định dạng đúng vì không có phần tử gốc. Để thêm phần tử gốc, ta cần thực hiện như sau:

Giả sử bạn muốn “EmployeeID” phải là thuộc tính của phần tử “Employee” chứ không phải là một phần tử, thì bạn có thể làm như sau:

Chúng ta có thể thêm các mức lồng nhau khác vào tài liệu XML. Như ví dụ, nếu chúng ta muốn các phần tử “FullName”, “Phone”, “DepartmentName” được lồng bên trong một phần tử “EmployeeInfo” khác, thì chúng ta có thể làm như sau:

Cuối cùng, nếu bạn muốn chuyển đổi các phần tử “FullName” và “Phone” thành thuộc tính của phần tử “EmployeeInfo”, thì bạn có thể thực hiện như sau:

Đầu ra XML với giá trị NULL

Ví dụ: Bảng thông tin nhân viên (Employee), cột tên phòng ban (DepartmentName) có thể chứa giá trị NULL

Dữ liệu trên ở định dạng XML với mệnh đề FOR XML PATH:

Do EmployeeID = 11, cột DepartmentName có giá trị là NULL, nên khi chuyển đổi XML thì cột DepartmentName sẽ không được hiển thị.

Chúng ta có thể sử dụng tham số ELEMENTS XSINIL để hiển thị các giá trị NULL trong đầu ra XML.

Kết quả trả về đã thêm các thứ sau:

• xsi:nil= “true” cho các giá trị NULL
• Định nghĩa thêm vào phần tử gốc

xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”

OPENXML

Tổng quan về OPENXML

• OPENXML cung cấp một cách để xem dữ liệu XML như một bảng hoặc view trong SQL Server.
• Nó cho phép truy cập dữ liệu XML dưới dạng một tập hợp các hàng, giống như bảng quan hệ.
• Bạn có thể sử dụng OPENXML để xử lý dữ liệu XML trong các truy vấn Transact-SQL.

Cú pháp OPENXML

• Cú pháp:

• OPENXML có 3 tham số và 1 mệnh đề WITH. Trong đó:
• idoc: là phần xử lý của biểu diễn nội bộ của tài liệu XML. Phần xử lý này được tạo bằng cách gọi sp_xml_preparedocument.
• rowpattern: là mẫu XPath được sử dụng để xác định các node sẽ được xử lý như các hàng. Các node này đến từ tài liệu XML có phần xử lý được truyền vào tham số idoc.
• flags: xác định cách ánh xạ giữa dữ liệu XML và rowset quan hệ, cũng như cách điền dữ liệu vào cột “spill-over” (nếu có). Có một số giá trị cho flags, ví dụ:
• 0: mặc định là ánh xạ theo thuộc tính.
• 1: sử dụng ánh xạ theo thuộc tính. Có thể kết hợp với phần tử.
• 2: sử dụng ánh xạ theo phần tử. Có thể kết hợp với thuộc tính.
• 3: sử dụng ánh xạ theo thuộc tính và phần tử.
• 8: XML không xử lý cho hàng mới sẽ được ghi vào thuộc tính @mp:xmltext
• Mệnh đề WITH: sử dụng SchemaDeclaration (định nghĩa lược đồ) hoặc chỉ định một TableName (tên bảng) hiện có để cung cấp định dạng rowset và thông tin ánh xạ bổ sung nếu cần.

Ví dụ: Ta có đoạn dữ liệu XML sau:

Từ đoạn dữ liệu XML trên, ta thực hiện lấy thông tin của Employee bằng các cách sau:

• Sử dụng câu lệnh SELECT cơ bản với OPENXML
• OPENXML với flags = 1:

Với kết quả trả về khi sử dụng flags = 1, ta thấy các thuộc tính EmployeeID và FullName được lấy ra. Các phần tử Phone và DepartmentName sẽ trả về giá trị NULL.

• OPENXML với flags = 2:

Với kết quả trả về khi sử dụng flags = 2, ta thấy các thuộc tính EmployeeID và FullName sẽ trả về giá trị NULL. Các phần tử Phone và DepartmentName được lấy ra.

• OPENXML với flags = 3:

Với kết quả trả về khi sử dụng flags = 3, ta thấy các thuộc tính EmployeeID, FullName và các phần tử Phone, DepartmentName được lấy ra.

• OPENXML với flags = 8:

Chi tiết XMLData của EmployeeID = 1:

Với kết quả trả về khi sử dụng flags = 8, ta thấy các thuộc tính EmployeeID và FullName được lấy ra. Các phần tử Phone và DepartmentName sẽ trả về giá trị NULL. XMLData sẽ chứa giá trị các phần tử Phone và DepartmentName chưa được xử lý trong đoạn dữ liệu XML.

• Chỉ định ColPattern để ánh xạ giữa các cột và thuộc tính XML

Khi sử dụng flags = 2 thì chỉ trả về được các giá trị phần tử Phone và DepartmentName. Các thuộc tính EmployeeID và FullName sẽ trả về giá trị NULL. Do đó, để lấy được giá trị các thuộc tính thì ta sử dụng ColPattern (@EmployeeID, @FullName).

Khi sử dụng ColPattern để chị định cột ánh xạ thì tên các cột định nghĩa không nhất thiết phải giống với thuộc tính hay phần tử trong đoạn dữ liệu XML.

XQuery

XQuery trong SQL Server là một công cụ mạnh mẽ cho việc truy vấn và xử lý dữ liệu XML, được lưu trữ trong cơ sở dữ liệu SQL Server. Nó cho phép bạn trích xuất dữ liệu cụ thể từ tài liệu XML hoặc thay đổi cấu trúc của tài liệu XML để đáp ứng các yêu cầu cụ thể.

XQuery cung cấp nhiều hàm và phương pháp cho việc truy vấn và thay đổi dữ liệu XML. Bạn có thể sử dụng chúng để thực hiện các thao tác như lọc dữ liệu, thay đổi cấu trúc XML và thực hiện các biến đổi khác

Ví dụ: Từ bảng thông tin nhân viên (Employee), ta có các thông tin sau:

Với EmployeeID = 1 thì thông tin FamilyMember như sau:

• Lấy thông tin trong XML

Để đưa ra được thông tin các thành viên gia đình (FamilyMember) của EmployeeID = 1 thì ta thực hiện câu truy vấn như bên dưới:

• Lọc dữ liệu trong XML

Để lấy thông tin “Father” của các nhân viên thì ta thực hiện như sau:

JSON trong SQL Server

JSON là gì?

JSON (Java Script Object Notation) là một định dạng dữ liệu văn bản phổ biến khác, thường được sử dụng trong ứng dụng web và di động. Từ SQL Server 2016 (phiên bản 13.x) trở đi, bạn có thể làm việc với JSON bằng cách sử dụng các hàm và toán tử tích hợp sẵn.

Trong chuỗi JSON, chúng ta có hai khái niệm quan trọng là key và value:

• Key (khóa): là tên của thuộc tính trong JSON. Key được đặt trong cặp dấu ngoặc kép (” “). Có phân biệt hoa thường.
• Value (giá trị): là giá trị của Key. Value có thể là một trong các kiểu dữ liệu cơ bản trong JSON như number, boolean, string, null, object và array.

Ví dụ: Bảng thông tin nhân viên (Employee), cột thành viên gia đình (Family) đang lưu theo dạng JSON

Với EmployeeID = 1 thì thông tin Family như sau:

Với đoạn JSON trên, thì MemberId, Relationship, MemberName và Phone là các Key của JSON.

Các hàm và phương thức xử lý của JSON

Các hàm và phương thức cho phép bạn kiểm tra, trích xuất, đọc và định dạng dữ liệu JSON như:

ISJSON

ISJSON dùng để kiểm tra xem một chuỗi có chứa JSON hợp lệ hay không.

Ví dụ 1: Kiểm tra chuỗi có phải là JSON hay không.

Kết quả trả về là 1 (True), có nghĩa là chuỗi trong ví dụ trên là một đoạn JSON.

Ví dụ 2: Kiểm tra giá trị cột Family trong bảng Employee có đang lưu trữ dữ liệu JSON hay không.

Với kết quả trả về, thì ta thấy cột Family của bảng Employee đang lưu dữ liệu JSON.

JSON_VALUE

JSON_VALUE dùng để trích xuất một giá trị vô hướng từ chuỗi JSON.

Ví dụ: Lấy ra một số thông tin thành viên thứ nhất và thứ hai của nhân viên có EmployeeID = 1 trong bảng nhân viên (Employee).

Sử dụng $[0] và $[1] để lấy thông tin của thành viên thứ nhất và thứ hai trong JSON Family lưu thông tin thành viên gia đình của Employee.

JSON_QUERY

JSON_QUERY dùng để trích xuất một mảng hoặc chuỗi từ JSON trong SQL Server.

Ví dụ: Lấy ra tất cả thông tin thành viên thứ nhất của nhân viên có EmployeeID = 1 trong bảng nhân viên (Employee).

FOR JSON

• Sử dụng mệnh đề FOR JSON trong câu lệnh SELECT để định dạng kết quả truy vấn thành JSON hoặc xuất dữ liệu từ SQL Server dưới dạng JSON.
• Mệnh đề này giúp đơn giản hóa ứng dụng khách bằng cách chuyển việc định dạng đầu ra JSON từ ứng dụng đến SQL Server.
• Sử dụng mệnh đề FOR JSON AUTO hoặc FOR JSON PATH để chuyển đổi sang dạng JSON.

FOR JSON AUTO

Khi sử dụng tùy chọn AUTO, định dạng của kết quả JSON được tự động xác định dựa trên thứ tự các cột trong danh sách SELECT và các bảng nguồn. Chúng ta không thể thay đổi định dạng này.

Ví dụ: Sử dụng FOR JSON AUTO với các cột EmployeeID, FullName, Phone và DepartmentName trong bảng Employee.

FOR JSON PATH

Khi sử dụng tùy chọn PATH, chúng ta được phép kiểm soát đầy đủ định dạng của kết quả JSON. Chúng ta có thể tạo các đối tượng bao và lồng các thuộc tính phức tạp.

Ví dụ: Sử dụng FOR JSON PATH với các cột EmployeeID, FullName, Phone và DepartmentName trong bảng Employee.

Cách sử dụng cơ bản thì FOR JSON AUTO và FOR JSON PATH đều trả về kết quả giống nhau.

Để sắp xếp đầu ra JSON thành các mảng con, chúng ta sẽ phải sử dụng cú pháp dấu chấm. Nhãn trước dấu chấm thể hiện tên của đối tượng – trong trường hợp này chúng ta có hai đối tượng có tên là EmployeeInfo và Department.

Chúng ta có thể sắp xếp kết quả đầu ra JSON PATH thành các mảng con, bằng cách chuyển đổi tập lệnh của chúng ta thành một phép nối lồng nhau.

Các tùy chọn cho mệnh đề FOR JSON

Cả hai mệnh đề FOR JSON AUTO và FOR JSON PATH đều cho phép bạn chỉ định các tùy chọn bổ sung như ROOT, INCLUDE_NULL_VALUES và WITHOUT_ARRAY_WRAPPER.

• ROOT: là tùy chọn được sử dụng để gán nhãn cho mảng cấp cao nhất.

Ví dụ: Ta thêm ROOT “Employees” cho FOR JSON PATH trên bảng Employee.

• INCLUDE_NULL_VALUES: theo mặc định khi sử dụng FOR JSON thì các giá trị NULL sẽ không được đưa vào JSON. Để đưa ra các giá trị NULL thì chúng ta sử dụng tùy chọn INCLUDE_NULL_VALUES.

Ví dụ: Ta có thông tin TOP 2 nhân viên trong bảng Employee như sau:

Sử dụng FOR JSON PATH, ta được kết quả bên dưới.

Nhìn vào kết quả trả về, ta thấy DepartmentName của EmployeeID = 2 có giá trị NULL. Nên JSON trả về sẽ không cột đó. Để trả về được DepartmentName thì ta thực hiện như sau:

• WITHOUT_ARRAY_WRAPPER: theo mặc định thì mọi mệnh đề FOR JSON đều trả về dữ liệu JSON được bao quanh dấu ngoặc vuông – còn được gọi là mảng. Có những trường hợp chúng ta muốn loại trừ dấu ngoặc vuông khỏi đầu ra vì chúng ta có thể muốn nối hai hoặc nhiều dữ liệu JSON. Để làm được vậy thì ta sử dụng tùy chọn WITH_ARRAY_WRAPPER.

Ví dụ: Sử dụng WITH_ARRAY_WRAPPER khi FOR JSON PATH trong bảng Employee.

Nhìn câu truy vấn trên, chúng ta có thể thấy rằng, sử dụng mệnh đề TOP để giới hạn lựa chọn thành một hàng duy nhất. Chúng ta nên cẩn thận khi sử dụng tùy chọn WITHOUT_ARRAY_WRAPPER đối với tập dữ liệu có nhiều hàng, vì nó có thể dẫn đến những lỗi xác thực ngoài ý muốn.

OPENJSON

Trong SQL Server, chúng ta có một hàm có kiểu bảng gọi là OPENJSON(), giúp tạo ra một dạng dữ liệu quan hệ từ dữ liệu JSON. Khi gọi hàm này, chúng ta truyền một tài liệu JSON như đối số, và OPENJSON() sẽ phân tích nó và trả về các đối tượng và thuộc tính của tài liệu JSON dưới dạng bảng – với các hàng và cột.

• Lấy dữ liệu từ đoạn JSON

Ví dụ: Ta có đoạn JSON bên dưới.

Để lấy được dữ liệu thông tin nhân viên thì ta thực hiện như sau:

• Lấy dữ liệu từ cột lưu trữ dữ liệu JSON

Ví dụ: Ta có TOP 3 lấy từ bảng Employee như bên dưới.

Để lấy được thông tin Family của EmployeeID = 1 trong bảng Employee. Ta sử dụng OUTER APPLY, thực hiện truy vấn như sau:

Nên chọn JSON hay XML khi lưu trữ trong SQL Server?

Khi lưu trữ dữ liệu trong SQL Server, việc chọn giữa JSON và XML phụ thuộc vào nhu cầu cụ thể của ứng dụng. Dưới đây là một số điểm để bạn cân nhắc:

Lưu Trọng Hưng

Data Analyst