--------------------------------------------------------------------------------------------------------
-- XML - EXtensible Markup Language
--------------------------------------------------------------------------------------------------------
-- * XML stands for EXtensible Markup Language
-- * XML was designed to carry data, not to display data
-- * XML tags are not predefined. You must define your own tags
-- * XML is designed to be self-descriptive
-- * XML Documents Form a Tree Structure
-- * All XML Elements Must Have a Closing Tag
-- * XML Tags are Case Sensitive
-- * XML Attribute Values Must be Quoted
-- * An XML element is everything from (including) the element's start tag to
-- (including) the element's end tag.
-- * An element can contain:
-- other elements
-- text
-- attributes
-- * XML Attributes Must be Quoted
--------------------------------------------------------------------------------------------------------
'
Harry Potter
J K. Rowling
2005
29.99
Learning XML
Erik T. Ray
2003
39.95
'
--------------------------------------------------------------------------------------------------------
-- 1. Record => XML Methods
-- 1. FOR XML RAW
-- 2. FOR XML AUTO
-- 3. FOR XML EXPLICIT
-- 4. FOR XML PATH
-- 2. Creating & Populating XML columns
-- 3. OPENXML - Shredding a XML to a DB RowSet
-- 4. XML Indexes
-- 1. Intro - XML Methods
-- 2. Primary XML Index
-- 3. Secondary XML Index
--------------------------------------------------------------------------------------------------------
-- Record => XML
--------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------
-- FOR XML RAW [('name of element')] , [ROOT ('name of root')] , ELEMENTS
--------------------------------------------------------------------------------------------------------
USE Nortwind
GO
-- Select a rowset from the employees table:
SELECT lastname, title, birthdate
FROM employees
-- Recieve flat XML instead of a rowset:
-- Default is:
-- 1. All columns are Attributes of the row elements
-- 2. Element names are "row" ( |
)
-- 3. No Root element
SELECT lastname, title, birthdate
FROM employees
FOR XML RAW
-- Change the name of row element: (Instead of |
)
SELECT lastname, title, birthdate
FROM employees
FOR XML RAW ('Employee')
-- Add a ROOT element:
SELECT lastname, title, birthdate
FROM employees
FOR XML RAW ('Employee'), ROOT('Employees')
-- Receive each row data as a SubElemnt:
SELECT lastname, title, birthdate
FROM employees
FOR XML RAW ('Employee'), ROOT('Employees'), ELEMENTS
--------------------------------------------------------------------------------------------------------
-- FOR XML AUTO
--------------------------------------------------------------------------------------------------------
SELECT cat.categoryname, pro.productID, pro.productName , pro.unitPrice
FROM products pro, categories cat
WHERE pro.categoryID = cat.categoryID
ORDER BY cat.categoryID, pro.productID
FOR XML AUTO
SELECT lastname, title, birthdate
FROM employees
FOR XML AUTO
SELECT productID, productName , unitPrice, categoryID
FROM products
FOR XML AUTO
SELECT categoryID , productID, productName , unitPrice
FROM products
FOR XML AUTO
SELECT categoryID, productID, productName , unitPrice
FROM products
ORDER BY categoryID, productID
FOR XML AUTO
SELECT sup.CompanyName , pro.productName , pro.unitPrice
FROM products pro, suppliers sup
WHERE pro.supplierID = pro.SupplierID
ORDER BY sup.CompanyName , pro.productName
FOR XML AUTO
-- All customers and their orders:
-- Use Order by to ensure order of hierarchy
SELECT c.customerid, c.companyname, c. country, o.orderid, o.orderdate
FROM customers c JOIN orders o
ON c.customerid = o.customerid
ORDER BY c.customerid, o.orderid
FOR XML AUTO
-- Also with ELEMENTS - Display column data as subelements for each row:
SELECT customer.customerid, customer.companyname, customer. country, order_detailes.orderid, order_detailes.orderdate
FROM customers customer JOIN orders order_detailes
ON customer.customerid = order_detailes.customerid
ORDER BY customer.customerid, order_detailes.orderid
FOR XML AUTO ,ELEMENTS
-- Also possible to use ROOT
SELECT customer.customerid, customer.companyname, customer. country, order_detailes.orderid, order_detailes.orderdate
FROM customers customer JOIN orders order_detailes
ON customer.customerid = order_detailes.customerid
ORDER BY customer.customerid, order_detailes.orderid
FOR XML AUTO ,ELEMENTS, ROOT('Customer_Orders')
--------------------------------------------------------------------------------
-- FOR XML EXPLICIT
--------------------------------------------------------------------------------
-- If I want to manually and explicitly determine the structure of the XML,
-- use a Universal Table to specify the structure.
-- * Each feild in the Universal table represents an element in the XML
-- * The alias for each selected column will determine its location in the XML
-- * The alias specifies the row Element name (Must be the same in all columns),
-- whether the value will be an attribute or a subelement,
-- and the name for the attribute or Subelement.
-- * For now, the first two columns (Tag and Parent) are always 1 and NULL.
-- We will change them only to create a more complex nested XML.
SELECT 1 AS Tag,
NULL AS Parent,
SupplierID AS [Products!1!SupplierID],
ProductName AS [Products!1!ProductName],
Unitprice AS [Products!1!Unitpricet]
FROM products
FOR XML EXPLICIT, ROOT('Products')
-- Using !Element
SELECT 1 AS Tag,
NULL AS Parent,
SupplierID AS [Products!1!SupplierID],
ProductName AS [Products!1!ProductName!Element],
Unitprice AS [Products!1!Unitprice!Element]
FROM products
FOR XML EXPLICIT
SELECT 1 AS Tag,
NULL AS Parent,
SupplierID AS [Products!1!SupplierID],
CategoryID AS [Products!1!CategoryID],
ProductName AS [Products!1!ProductName!Element],
Unitprice AS [Products!1!Unitprice!Element]
FROM products
FOR XML EXPLICIT
-- ELEMENTS is not allowed
SELECT 1 AS Tag,
NULL AS Parent,
SupplierID AS [Products!1!SupplierID],
CategoryID AS [Products!1!CategoryID],
ProductName AS [Products!1!ProductName!Element],
Unitprice AS [Products!1!Unitprice!Element]
FROM products
FOR XML EXPLICIT, ELEMENTS
-- Possible to use ROOT
SELECT 1 AS Tag,
NULL AS Parent,
SupplierID AS [Products!1!SupplierID],
CategoryID AS [Products!1!CategoryID],
ProductName AS [Products!1!ProductName!Element],
Unitprice AS [Products!1!Unitprice!Element]
FROM products
FOR XML EXPLICIT, ROOT('Products')
-- Nesting XML
SELECT 1 AS Tag,
NULL AS Parent,
CategoryName AS [category!1!CategoryName],
NULL AS [product!2!productName],
NULL AS [product!2!unitPrice]
FROM categories
UNION ALL
SELECT 2 ,
1 ,
CategoryName AS [category!1!CategoryName],
productName AS [product!2!productName],
unitPrice AS [product!2!unitPrice]
FROM products pro JOIN categories cat
ON pro.CategoryID = cat.CategoryID
ORDER BY [category!1!CategoryName] , [product!2!productName]
FOR XML EXPLICIT
--------------------------------------------------------------------------------
-- FOR XML PATH
--------------------------------------------------------------------------------
-- * Another way to explicitly map values in columns to XML positions
-- * Again, it is done using the column's alias.
SELECT SupplierID AS [@SupplierID],
ProductName AS [ProductName],
Unitprice AS [Unitprice]
FROM products
FOR XML PATH
SELECT SupplierID AS [@SupplierID],
ProductName AS [ProductName],
Unitprice AS [Unitprice]
FROM products
FOR XML PATH ('Products')
SELECT SupplierID AS [@SupplierID],
CategoryID AS [@categoryID],
ProductName AS [ProductName],
Unitprice AS [Unitprice]
FROM products
FOR XML PATH ('Products')
-- Building levels in the hierarchy is quite simple:
SELECT SupplierID AS [@SupplierID],
CategoryID AS [@categoryID],
ProductName AS [Basic_Details/ProductName],
Unitprice AS [Basic_Details/Unitprice]
FROM products
FOR XML PATH ('Products')
SELECT SupplierID AS [@SupplierID],
CategoryID AS [@categoryID],
ProductName AS [Basic_Details/ProductName],
Unitprice AS [Basic_Details/Unitprice],
QuantityPerUnit AS [Quantity_Details/ProductName],
UnitsInStock AS [Quantity_Details/Unitprice] ,
UnitsOnOrder AS [Quantity_Details/UnitsOnOrder]
FROM products
FOR XML PATH ('Products')
-- ELEMENTS allowed but does not have any effect
SELECT SupplierID AS [@SupplierID],
CategoryID AS [@categoryID],
ProductName AS [Basic_Details/ProductName],
Unitprice AS [Basic_Details/Unitprice],
QuantityPerUnit AS [Quantity_Details/ProductName],
UnitsInStock AS [Quantity_Details/Unitprice] ,
UnitsOnOrder AS [Quantity_Details/UnitsOnOrder]
FROM products
FOR XML PATH ('Products') , ELEMENTS
-- ROOT allowed
SELECT SupplierID AS [@SupplierID],
CategoryID AS [@categoryID],
ProductName AS [Basic_Details/ProductName],
Unitprice AS [Basic_Details/Unitprice],
QuantityPerUnit AS [Quantity_Details/ProductName],
UnitsInStock AS [Quantity_Details/Unitprice] ,
UnitsOnOrder AS [Quantity_Details/UnitsOnOrder]
FROM products
FOR XML PATH ('Products') , ROOT ('ProductsInfo')
--------------------------------------------------------------------------------------------------------
-- Creating & Populating XML columns
--------------------------------------------------------------------------------------------------------
DROP TABLE TestXML
GO
CREATE TABLE TestXML
(KioskID VARCHAR(10) PRIMARY KEY,
XMLValue xml)
--Insert Data Into Table
DECLARE @myXML1 xml,
@myXML2 xml
SET @myXML1 = '
'
SET @myXML2 = '
'
INSERT INTO TestXML VALUES ('Kiosk1', @myXML1)
INSERT INTO TestXML VALUES ('Kiosk2', @myXML2)
-- See the value
SELECT * FROM TestXML
-------------------------------------------------------------------------------------------
-- OPENXML - Shredding a XML to a DB RowSet - Demo
-------------------------------------------------------------------------------------------
-- ROOT
-- Customer - CustomerID, ContactName
-- Order - OrderID, CustomerID, EmployeeID, OrderDate
-- OrderDetail - ProductID, Quantity
-- Order
-- Customer
-- ROOT
DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT stmt using OPENXML rowset provider.
-- 1. Deepest Level
SELECT *
FROM OPENXML ( @idoc, '/ROOT/Customer/Order/OrderDetail',3)
WITH ( ProdID int '@ProductID',
Qty int '@Quantity')
-- 2. Deepest Level + 1 Level up
SELECT *
FROM OPENXML ( @idoc, '/ROOT/Customer/Order/OrderDetail',2)
WITH ( OrderID int '../@OrderID',
CustomerID varchar(10) '../@CustomerID',
OrderDate datetime '../@OrderDate',
ProdID int '@ProductID',
Qty int '@Quantity')
-- 2. Deepest Level + 2 Levels up
SELECT *
FROM OPENXML ( @idoc, '/ROOT/Customer/Order/OrderDetail',3)
WITH ( CustomerID varchar(25) '../../@CustomerID',
ContactName varchar(25) '../../@ContactName',
OrderID int '../@OrderID',
OrderDate datetime '../@OrderDate',
ProdID int '@ProductID',
Qty int '@Quantity')
----------------------------------------------------------------------------------------------------------------------------------
-- XML Indexes
----------------------------------------------------------------------------------------------------------------------------------
-- Products_Categories
-- Category - CategoryID, CategoryName
-- Product - ProductID, ProductName
-- Category
-- Products_Categories
-----------------------------------------------------------------------
-- Intro - XQUERY METHODS
-----------------------------------------------------------------------
-----------------------------------------------------------------------
-- Query
-----------------------------------------------------------------------
SELECT * FROM TestXML
-- Get all products:
SELECT KioskID, XMLValue.query('(/Products_Categories/Category/Product)')
FROM TestXML
-- Get all categories
SELECT KioskID, XMLValue.query('(/Products_Categories/Category)')
FROM TestXML
-- Specify a condition inside [ ]
-- Get the 4th product:
SELECT KioskID, XMLValue.query('(/Products_Categories/Category/Product)[4]')
FROM TestXML
-- Get the Second category with Query:
SELECT KioskID, XMLValue.query('(/Products_Categories/Category[2])')
FROM TestXML
-- Get product 214:
SELECT KioskID, XMLValue.query('(/Products_Categories/Category/Product[@ProductID=214])')
FROM TestXML
-- Get product where id>100:
SELECT KioskID, XMLValue.query('(/Products_Categories/Category/Product[@ProductID>100])')
FROM TestXML
-- Get the Toys category:
SELECT KioskID, XMLValue.query('(/Products_Categories/Category[@CategoryName="Toys"])')
FROM TestXML
-----------------------------------------------------------------------
-- Exist
-----------------------------------------------------------------------
-- * Returns 0 (False) or 1 (True)
--Existing
SELECT KioskID, XMLValue.exist('(/Products_Categories/Category[@CategoryName="Toys"])')
FROM TestXML
--Non-existing
SELECT KioskID, XMLValue.exist('(/Products_Categories/Category[@CategoryName="Fruits"])')
FROM TestXML
-----------------------------------------------------------------------
-- Value
-----------------------------------------------------------------------
SELECT KioskID, XMLValue.value('(/Products_Categories/Category/Product/@ProductID)[1]' , 'int' )
FROM TestXML
SELECT KioskID, XMLValue.value('(/Products_Categories/Category/Product/@ProductName)[1]' , 'varchar(25)' )
FROM TestXML
SELECT KioskID, XMLValue.value('(/Products_Categories/Category/Product/@ProductName)[3]' , 'varchar(25)' )
FROM TestXML
----------------------------------------------------------------------------------------------------------------------------------
-- Primary XML Index
----------------------------------------------------------------------------------------------------------------------------------
--Essentially, the primary XML index is a clustered index on an internal table,
--sometimes referred to as the "node" table (in query execution plans it will be referred
--to as "xml_index_nodes_N" where N is a random integer). This clustered index
--stores data according to XML document order.
SELECT * FROM TestXML
-- Only XML Index can be created on XML column 'XMLValue'.
CREATE INDEX p_xml_ix ON TestXML(xmlValue)
-- Must create primary index first
CREATE XML INDEX p_xml_ix ON TestXML(xmlValue)
-- Create XML Index
CREATE PRIMARY XML INDEX p_xml_ix ON TestXML(xmlValue)
-- Get product 214:
SELECT KioskID, XMLValue.query('(/Products_Categories/Category/Product[@ProductID=214])')
FROM TestXML
----------------------------------------------------------------------------------------------------------------------------------
-- Secondary XML Index
----------------------------------------------------------------------------------------------------------------------------------
-- The PATH secondary XML index created is based on the primary XML index
-- and improves any path expression queries made on this column (query / exist)
CREATE XML INDEX secondary_path_ix
ON TestXML(xmlValue)
USING XML INDEX p_xml_ix
FOR PATH
-- When querying for specific values in an XML instance, you should use the VALUE index
CREATE XML INDEX secondary_value_ix
ON TestXML(xmlValue)
USING XML INDEX p_xml_ix
FOR VALUE
-- When querying for multiple values in an XML instance, you should use the VALUE index
CREATE XML INDEX secondary_property_ix
ON TestXML(xmlValue)
USING XML INDEX p_xml_ix
FOR PROPERTY