2779 Module 3 Using XML

-FOR XML clause can be used in select queries

-returns xml

-OPEN XML will transform XML into rows and columns

-returns rows and columns

-XML Datatype

For XML

Raw mode queries

Auto mode

Explicit mode

Path mode (easier to read)

Nested XML

FOR XML Claus

returns XML instead of a record set
Can return attributes, elements, and schema
Supports client applications that work with XML

*Mark says XML is not meant for human consumption its meant to be consumed by applications.

RAW Mode Queries

-It’s a normal query but you throw FOR XML RAW on the end.

select top 1 * from HumanResources.Department

for xml raw

--returns

-Returns either elements or attributes

select top 1 * from HumanResources.Department

for xml raw, elements

--returns

<row>

<Name>Engineering</Name>

<GroupName>Research and Development</GroupName>

</row>

-Can contain root element and row element name.

-If you use root you have to specify the root you are going to use.

select top 1 * from HumanResources.Department
for xml raw('Department'),root('Department') --replaces “row” with “Product”

--returns

</Department>

select top 1 * from HumanResources.Department
for xml raw('GroupName'),root('GroupName')

--returns

</GroupName>

AUTO Mode Queries

-XML representation of data

-in a select statement that has a join, the results display the joined table as nested elements.

-Data is nested based on join precedence

-Can use options like ELEMENTS and ROOT

-Can NOT use group by (query a view instead to get around this if needed)

My Example:

select HD.Name, HDH.EmployeeID from HumanResources.Department HD

join HumanResources.EmployeeDepartmentHistory HDH

on (HD.Departmentid=HDH.DepartmentId)

where HD.Name in ('Engineering','Research and Development')

order by HD.Name

for xml auto

--returns

</HD>

</HD>

My Example2:

select HDwheee.Name, HDHlalalala.EmployeeID from HumanResources.Department HDwheee

join HumanResources.EmployeeDepartmentHistory HDHlalalala

on (HDwheee.Departmentid=HDHlalalala.DepartmentId)

where HDwheee.Name in ('Engineering','Research and Development')

order by HDwheee.Name

for xml auto

</HDwheee>

</HDwheee>

Retrieving Nested XML with (XML AUTO)

-AUTO mode returns either attributes or elements.

--ATTRIBUTES

SELECT Cust.CustomerID, CustomerTYpe,SalesOrderID,Status

FROM Sales.Customer Cust

Join Sales.SalesOrderHeader [Order]

ON Cust.CustomerID = [Order].CustomerID

ORDER BY Cust.CustomerID

FOR XML AUTO

--returns:

</Cust>

</Cust>

--ELEMENTS:

SELECT Cust.CustomerID, CustomerTYpe,SalesOrderID,Status

FROM Sales.Customer Cust

Join Sales.SalesOrderHeader [Order]

ON Cust.CustomerID = [Order].CustomerID

ORDER BY Cust.CustomerID

FOR XML AUTO,Elements

--returns

<Cust>

<Order>

</Order>

<Order>

</Order>

<Order>

</Order>

<Order>

</Order>

</Cust>

-use inner FOR XML with TYPE(returns XML datatype) clause to return xml data type

-- Nested XML with TYPE

SELECT NAME CategoryName,

( SELECT Name SubCategoryName

FROM Production.ProductSubCategory SubCategory

WHERE SubCategory.ProductCategoryID =

Category.ProductCategoryID

FOR XML AUTO, TYPE, ELEMENTS)

FROM Production.ProductCategory Category

FOR XML AUTO

--returns

<SubCategoryName>Mountain Bikes</SubCategoryName>

</SubCategory>

<SubCategoryName>Road Bikes</SubCategoryName>

</SubCategory>

<SubCategoryName>Touring Bikes</SubCategoryName>

</SubCategory>

</Category>…snip

-You can combine EXPLICIT mode with UNION ALL

(insert example here I tried to make my own and it didn’t work)

Here is the slide---

Mark later demoed it and here is the syntax I still don’t really get how SQL Server knows how to convert this to XML

-- Nesting in EXPLICIT Mode

SELECT 1 AS TAG,

NULL AS Parent,

ProductID AS [Product!1!ProductID],

Name AS [Product!1!ProductName!Element],

NULL AS [Review!2!Reviewer],

NULL AS [Review!2]

FROM Production.Product

WHERE ProductID > 795

UNION ALL

SELECT 2,

p.ProductID,

NULL,

r.ReviewerName AS [Review!2!Reviewer],

r.Comments AS [Review!2]

FROM Production.ProductReview r

JOIN Production.Product p

ON p.ProductID = r.ProductID

WHERE p.ProductID > 795

ORDER BY [Product!1!ProductID]

FOR XML EXPLICIT

EXPLICIT Mode Queries (without nesting) (not normally used)

-3^rd party application has specified EXACTLY how they want the data to be outputted.

Book Example:

SELECT 1 as Tag, NULL as Parent,

SalesOrderID as [Invoice!1!InvoiceNo],

OrderDate AS [Invoice!1!Date!Element]

FROM Sales.SalesOrderHeader

FOR XML EXPLICIT

--results

</Invoice>

</Invoice>

</Invoice>

</Invoice>

…….snip

My Example (add TotalDue)

SELECT 1 as Tag, NULL as Parent,

SalesOrderID as [Invoice!1!InvoiceNo],

OrderDate AS [Invoice!1!Date!Element],

TotalDue AS [Invoice!1!Total!Element]

FROM Sales.SalesOrderHeader

FOR XML EXPLICIT

</Invoice>

</Invoice>

</Invoice>

</Invoice>…snip

PATH Mode Queries

-a lot easier to construct than explicit mode

-uses XPath to specify the XML format

-Allows the creation of nested data

Book Example:

select EmployeeID '@EmpID',

FirstName 'EmpName/First',

LastName 'EmpName/Last'

from Person.Contact

inner join HumanResources.Employee

on Person.Contact.ContactID = Employee.ContactID

FOR XML PATH

--returns

<Last>Gilbert</Last>

</EmpName>

</row>

<First>Kevin</First>

<Last>Brown</Last>

</EmpName>

</row>…snip

Mark Example:

SELECT ProductID AS "@ProductID",

Name AS "*",

Size AS "Description/@Size",

Color AS "Description/text()"

FROM Production.Product

ORDER BY Name

FOR XML PATH('Product')

--result

<Product ProductID="1">Adjustable Race</Product>

<Product ProductID="879">All-Purpose Bike Stand</Product>

<Product ProductID="712">AWC Logo Cap<Description>Multi</Description></Product>

<Product ProductID="3">BB Ball Bearing</Product>

<Product ProductID="2">Bearing Ball</Product>

<Product ProductID="877">Bike Wash - Dissolver</Product>

<Product ProductID="316">Blade</Product>

<Product ProductID="843">Cable Lock</Product>

<Product ProductID="952">Chain<Description>Silver</Description></Product>

<Product ProductID="324">Chain Stays</Product>

<Product ProductID="322">Chainring<Description>Black</Description></Product>

<Product ProductID="320">Chainring Bolts<Description>Silver</Description></Product>

<Product ProductID="321">Chainring Nut<Description>Silver</Description></Product>

<Product ProductID="866">Classic Vest, L<Description Size="L">Blue</Description></Product>

<Product ProductID="865">Classic Vest, M<Description Size="M">Blue</Description></Product>

<Product ProductID="864">Classic Vest, S<Description Size="S">Blue</Description></Product>

Using OPEN XML

-taking XML and turning it into rows/columns

-aka shredding

-two sprocs involved

Steps:

an xml document is received by sql server
the document is prepared sproc sp_xml_preparedocument is run and an internal tree is created
OPENXML retrieves rowset
Data shredded into tables
sp_xml_removeddocument cleans up the memory tree

General Syntax of process:

@doc variable holds the xml document

@hdoc (int) holds the value that uniquely identifies the internal tree structure

Call sproc and pass @hdoc as OUTPUT and in @doc

Sproc processes @doc and lets you know what @hdoc # is

When done

call remove sproc to delete the @hdoc tree from memory

Open XML Syntax (do this where the ”–process document” appears above)

DECLARE @doc xml

SET @doc = '<?xml version="1.0" ?>

<Items>

<Item ProductCode="41" Quantity="1" UnitPrice="17.45"><ProductName>Helmet</ProductName></Item>

<Item ProductCode="2" Quantity="1" UnitPrice="2.99"><ProductName>Water Bottle</ProductName></Item>

</Items>

</SalesInvoice>'

DECLARE @docHandle int

EXEC sp_xml_preparedocument @docHandle OUTPUT, @doc

-- OPENXML using attributes only

SELECT * FROM

OPENXML(@docHandle, '/SalesInvoice/Items/Item', 1)

WITH

( ProductCode int,

Quantity int,

UnitPrice float,

ProductName nvarchar(20))

EXEC sp_xml_removedocument @docHandle

--returns

ProductCode Quantity UnitPrice ProductName

----------- ----------- ---------------------- --------------------

12 2 12.99 NULL

41 1 17.45 NULL

2 1 2.99 NULL

XML Namespace

You can pass namespace info into openxml

XML DataType

-Native datatype

-Internal storage structure

-Tables, variables, or parameters

-Methods for querying and modifying XML

XQuery

-identifies the XML Nodes

/InvoiceList/Invoice[@InvoiceNo-2000]

-FLOWR statements (For Let Orderby Where Return)

For –iterates through nodes

Where – Applies filtering

Return - specifies xml to be returned

Query, Value, and Exist Methods

-query returns untyped xml

-Value returns a scalar value

-Exist checks for the existence of a node

*if the invoice number of 1000 exists then this will return a 1 else 0

-Binding relational columns and variables (query method)

Modify Method

-insert add child nodes or siblings

Nodes Method

-shreds XML into relational data

-requires the APPLY operator with XML columns

Introducing XQuery

-is a query language that we use to identify nodes in an xml fragment

It uses FLOWR statements:

Expressions

Primary: ???

Sequence: [1]

Path: Relative Child::Address/child::Country

Absolute /Address/Country

Example:
declare @x xml

Set @x = '<root>

</root>'

--Select @x.query('/root/abc[atteAbc]')

Select @x.query('/root/abc[3]')