The series so far:

  1. SQL Server Fundamentals - Part 1: Life of Databases
  2. SQL Server Fundamentals - Part 2: How Introduction to Tables in Databases
    can keep you out of trouble
  3. SQL Server Fundamentals - Part 3: Working with data in the table
  4. SQL Server Fundamentals - Part 4: How Indexes can help you live a better life
  5. 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.

  1. Processing Instructions
    Starts with Processing Instruction to help Parser with version and type of encoding used.
  2. 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 and OPENJSON 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