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 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.
SELECT TOP 5 LastName FROM SalesLT.Customer FOR XML AUTO, ROOT('Customer')
<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));
|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|
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 AUTOto generate JSON and
OPENJSONto read JSON
On Azure, use Azure DocumentDB to store and work with JSON documents
- Use the native
XMLdata type for interoperability and schema enforcement
- Use the
NVARCHARdata type to store and work with JSON
- Use the
Spatialdata type for geometry and geography