The series so far:
- SQL Server Fundamentals - Part 1: Life of Databases
- SQL Server Fundamentals - Part 2: How Introduction to Tables in Databases
can keep you out of trouble- SQL Server Fundamentals - Part 3: Working with data in the table
- SQL Server Fundamentals - Part 4: How Indexes can help you live a better life
- SQL Server Fundamentals - Part 5: Non Relational Data Types in 2 Easy Steps
XML Data Type
Why needed in SQL? To utilise storage if we already have XML data.
- Processing Instructions
Starts with Processing Instruction to help Parser with version and type of encoding used. - Elements
Elements are user-defined with attributes and values
1. Generate XML from SQL Queries
Creating XML from SQL Query using Auto formatting rule wrapped in root element called customer.
Input
SELECT TOP 5 LastName
FROM SalesLT.Customer
FOR XML AUTO, ROOT('Customer')
Output
<Customer>
<SalesLT.Customer LastName="Gee" />
<SalesLT.Customer LastName="Harris" />
<SalesLT.Customer LastName="Carreras" />
<SalesLT.Customer LastName="Gates" />
<SalesLT.Customer LastName="Harrington" />
</Customer>
Note: We need to take care of the strict parsing of XML in SQL Server
2. Reading XML using SQL Queries
Passing an entire blob of XML in a variable and traversing the hierarchy in the XML to pull out information.
We are using OPENXML
function and telling what part of tree to traverse root to customer and then take result and assign column CustomerId and ContactName
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10), ContactName varchar(20));
Feature | Description |
---|---|
xml data type | Store XML in variables and columns |
XQuery | Node-tree query language for XML |
XML Indexes | Optimize XQuery performance |
XSD Schemas | Enforce type validation for XML data |
FOR XML clause | Generate XML from relational data |
OPENXML function | Generate relational data from XML |
NOTE:
-
This feature will also come in handy for monitoring SQL Server using Execution Plans stored as XML, we can read Execution Plan information in the SQL Server using XML support.
-
Also to support JSON Data Type we use
JSON AUTO
to generate JSON andOPENJSON
to read JSON -
On Azure, use Azure DocumentDB to store and work with JSON documents
Summary
- Use the native
XML
data type for interoperability and schema enforcement - Use the
NVARCHAR
data type to store and work with JSON - Use the
Spatial
data type for geometry and geography