Chapter 11 - Querying and Managing XML Data

Ky leksion 120 minutesh perfshin faqet 221 - 264 te librit te kursit.

USE TSQL2012;

GO

SET NOCOUNT ON;

GO

Returning Results as XML with FOR XML

-- Create XML example with FOR XML AUTO option, atttribute-centric

SELECT Customer.custid, Customer.companyname,

[Order].orderid, [Order].orderdate

FROM Sales.Customers AS Customer

INNER JOIN Sales.Orders AS [Order]

ON Customer.custid = [Order].custid

WHERE Customer.custid <= 2

AND [Order].orderid %2 = 0

ORDER BY Customer.custid, [Order].orderid

FOR XML AUTO, ROOT('CustomersOrders');

-- XML with AUTO option, element-centric, with namespace

WITH XMLNAMESPACES('TK461-CustomersOrders' AS co)

SELECT [co:Customer].custid AS [co:custid],

[co:Customer].companyname AS [co:companyname],

[co:Order].orderid AS [co:orderid],

[co:Order].orderdate AS [co:orderdate]

FROM Sales.Customers AS [co:Customer]

INNER JOIN Sales.Orders AS [co:Order]

ON [co:Customer].custid = [co:Order].custid

WHERE [co:Customer].custid <= 2

AND [co:Order].orderid %2 = 0

ORDER BY [co:Customer].custid, [co:Order].orderid

FOR XML AUTO, ELEMENTS, ROOT('CustomersOrders');

GO

-- Schema

-- XML with AUTO option, element-centric, with namespace and XMLSCHEMA

SELECT [Customer].custid AS [custid],

[Customer].companyname AS [companyname],

[Order].orderid AS [orderid],

[Order].orderdate AS [orderdate]

FROM Sales.Customers AS [Customer]

INNER JOIN Sales.Orders AS [Order]

ON [Customer].custid = [Order].custid

WHERE 1 = 2

FOR XML AUTO, ELEMENTS,

XMLSCHEMA('TK461-CustomersOrders');

GO

FOR XML

FOR XML RAW

-- Basic

SELECT Customer.custid, Customer.companyname,

[Order].orderid, [Order].orderdate

FROM Sales.Customers AS Customer

INNER JOIN Sales.Orders AS [Order]

ON Customer.custid = [Order].custid

WHERE Customer.custid <= 2

AND [Order].orderid %2 = 0

ORDER BY Customer.custid, [Order].orderid

FOR XML RAW;

-- Enhanced

SELECT Customer.custid, Customer.companyname,

[Order].orderid, [Order].orderdate

FROM Sales.Customers AS Customer

INNER JOIN Sales.Orders AS [Order]

ON Customer.custid = [Order].custid

WHERE Customer.custid <= 2

AND [Order].orderid %2 = 0

ORDER BY Customer.custid, [Order].orderid

FOR XML RAW('Order'), ROOT('CustomersOrders');

GO

FOR XML AUTO

-- Element-centric, with namespace, root element

WITH XMLNAMESPACES('TK461-CustomersOrders' AS co)

SELECT [co:Customer].custid AS [co:custid],

[co:Customer].companyname AS [co:companyname],

[co:Order].orderid AS [co:orderid],

[co:Order].orderdate AS [co:orderdate]

FROM Sales.Customers AS [co:Customer]

INNER JOIN Sales.Orders AS [co:Order]

ON [co:Customer].custid = [co:Order].custid

WHERE [co:Customer].custid <= 2

AND [co:Order].orderid %2 = 0

ORDER BY [co:Customer].custid, [co:Order].orderid

FOR XML AUTO, ELEMENTS, ROOT('CustomersOrders');

-- XML schema

SELECT [Customer].custid AS [custid],

[Customer].companyname AS [companyname],

[Order].orderid AS [orderid],

[Order].orderdate AS [orderdate]

FROM Sales.Customers AS [Customer]

INNER JOIN Sales.Orders AS [Order]

ON [Customer].custid = [Order].custid

WHERE 1 = 2

FOR XML AUTO, ELEMENTS,

XMLSCHEMA('TK461-CustomersOrders');

GO

FOR XML PATH

SELECT Customer.custid AS [@custid],

Customer.companyname AS [companyname]

FROM Sales.Customers AS Customer

WHERE Customer.custid <= 2

ORDER BY Customer.custid

FOR XML PATH ('Customer'), ROOT('Customers');

GO

OPENXML

-- Rowset description in WITH clause, different mappings

DECLARE @DocHandle AS INT;

DECLARE @XmlDocument AS NVARCHAR(1000);

SET @XmlDocument = N'

<CustomersOrders>

<Customer custid="1">

<companyname>Customer NRZBB</companyname>

<Order orderid="10692">

<orderdate>2007-10-03T00:00:00</orderdate>

</Order>

<Order orderid="10702">

<orderdate>2007-10-13T00:00:00</orderdate>

</Order>

<Order orderid="10952">

<orderdate>2008-03-16T00:00:00</orderdate>

</Order>

</Customer>

<Customer custid="2">

<companyname>Customer MLTDN</companyname>

<Order orderid="10308">

<orderdate>2006-09-18T00:00:00</orderdate>

</Order>

<Order orderid="10926">

<orderdate>2008-03-04T00:00:00</orderdate>

</Order>

</Customer>

</CustomersOrders>';

-- Create an internal representation

EXEC sys.sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument;

-- Attribute-centric mapping

SELECT *

FROM OPENXML (@DocHandle, '/CustomersOrders/Customer',1)

WITH (custid INT,

companyname NVARCHAR(40));

-- Element-centric mapping

SELECT *

FROM OPENXML (@DocHandle, '/CustomersOrders/Customer',2)

WITH (custid INT,

companyname NVARCHAR(40));

-- Attribute- and element-centric mapping

-- Combining flag 8 with flags 1 and 2

SELECT *

FROM OPENXML (@DocHandle, '/CustomersOrders/Customer',11)

WITH (custid INT,

companyname NVARCHAR(40));

-- Remove the DOM

EXEC sys.sp_xml_removedocument @DocHandle;

GO

Querying XML Data with XQuery

-- Sequences

DECLARE @x AS XML;

SET @x=N'

<root>

<a>1<c>3</c><d>4</d></a>

<b>2</b>

</root>';

SELECT

@x.query('*') AS Complete_Sequence,

@x.query('data(*)') AS Complete_Data,

@x.query('data(root/a/c)') AS Element_c_Data;

GO

-- Namespace declaration

DECLARE @x AS XML;

SET @x='

<CustomersOrders xmlns:co="TK461-CustomersOrders">

<co:Customer co:custid="1" co:companyname="Customer NRZBB">

<co:Order co:orderid="10692" co:orderdate="2007-10-03T00:00:00" />

<co:Order co:orderid="10702" co:orderdate="2007-10-13T00:00:00" />

<co:Order co:orderid="10952" co:orderdate="2008-03-16T00:00:00" />

</co:Customer>

<co:Customer co:custid="2" co:companyname="Customer MLTDN">

<co:Order co:orderid="10308" co:orderdate="2006-09-18T00:00:00" />

<co:Order co:orderid="10926" co:orderdate="2008-03-04T00:00:00" />

</co:Customer>

</CustomersOrders>';

-- Namespace in prolog of XQuery

SELECT @x.query('

(: explicit namespace :)

declare namespace co="TK461-CustomersOrders";

//co:Customer[1]/*') AS [Explicit namespace];

-- Default namespace for all elements in prolog of XQuery

SELECT @x.query('

(: default namespace :)

declare default element namespace "TK461-CustomersOrders";

//Customer[1]/*') AS [Default element namespace];

-- Namespace defined in WITH clause of T-SQL SELECT

WITH XMLNAMESPACES('TK461-CustomersOrders' AS co)

SELECT @x.query('

(: namespace declared in T-SQL :)

//co:Customer[1]/*') AS [Namespace in WITH clause];

GO

-- XQuery Functions

DECLARE @x AS XML;

SET @x='

<CustomersOrders>

<Customer custid="1" companyname="Customer NRZBB">

<Order orderid="10692" orderdate="2007-10-03T00:00:00" />

<Order orderid="10702" orderdate="2007-10-13T00:00:00" />

<Order orderid="10952" orderdate="2008-03-16T00:00:00" />

</Customer>

<Customer custid="2" companyname="Customer MLTDN">

<Order orderid="10308" orderdate="2006-09-18T00:00:00" />

<Order orderid="10926" orderdate="2008-03-04T00:00:00" />

</Customer>

</CustomersOrders>';

SELECT @x.query('

for $i in //Customer

return

<OrdersInfo>

{ $i/@companyname }

<NumberOfOrders>

{ count($i/Order) }

</NumberOfOrders>

<LastOrder>

{ max($i/Order/@orderid) }

</LastOrder>

</OrdersInfo>

');

GO

Predicates on Sequences

-- General comparison operators

DECLARE @x AS XML = N'';

SELECT @x.query('(1, 2, 3) = (2, 4)'); -- true

SELECT @x.query('(5, 6) < (2, 4)'); -- false

SELECT @x.query('(1, 2, 3) = 1'); -- true

SELECT @x.query('(1, 2, 3) != 1'); -- true

GO

-- Value comparison operators

DECLARE @x AS XML = N'';

SELECT @x.query('(5) lt (2)'); -- false

SELECT @x.query('(1) eq 1'); -- true

SELECT @x.query('(1) ne 1'); -- false

GO

DECLARE @x AS XML = N'';

SELECT @x.query('(2, 2) eq (2, 2)'); -- error

GO

-- Conditional expressions

DECLARE @x AS XML = N'

<Employee empid="2">

<FirstName>fname</FirstName>

<LastName>lname</LastName>

</Employee>

';

DECLARE @v AS NVARCHAR(20) = N'FirstName';

SELECT @x.query('

if (sql:variable("@v")="FirstName") then

/Employee/FirstName

else

/Employee/LastName

') AS FirstOrLastName;

GO

-- FLWOR Expressions

DECLARE @x AS XML;

SET @x = N'

<CustomersOrders>

<Customer custid="1">

<!-- Comment 111 -->

<companyname>Customer NRZBB</companyname>

<Order orderid="10692">

<orderdate>2007-10-03T00:00:00</orderdate>

</Order>

<Order orderid="10702">

<orderdate>2007-10-13T00:00:00</orderdate>

</Order>

<Order orderid="10952">

<orderdate>2008-03-16T00:00:00</orderdate>

</Order>

</Customer>

<Customer custid="2">

<!-- Comment 222 -->

<companyname>Customer MLTDN</companyname>

<Order orderid="10308">

<orderdate>2006-09-18T00:00:00</orderdate>

</Order>

<Order orderid="10952">

<orderdate>2008-03-04T00:00:00</orderdate>

</Order>

</Customer>

</CustomersOrders>';

SELECT @x.query('for $i in CustomersOrders/Customer/Order

let $j := $i/orderdate

where $i/@orderid < 10900

order by ($j)[1]

return

<Order-orderid-element>

<orderid>{data($i/@orderid)}</orderid>

{$j}

</Order-orderid-element>')

AS [Filtered, sorted and reformatted orders with let clause];

GO

Using the XML Data Type

USE TSQL2012;

-- Using the XML Data Type for Dynamic Schema

ALTER TABLE Production.Products

ADD additionalattributes XML NULL;

GO

-- Auxiliary tables

CREATE TABLE dbo.Beverages

(

percentvitaminsRDA INT

);

CREATE TABLE dbo.Condiments

(

shortdescription NVARCHAR(50)

);

GO

-- Store the Schemas in a Variable and Create the Collection

DECLARE @mySchema NVARCHAR(MAX);

SET @mySchema = N'';

SET @mySchema = @mySchema +

(SELECT *

FROM Beverages

FOR XML AUTO, ELEMENTS, XMLSCHEMA('Beverages'));

SET @mySchema = @mySchema +

(SELECT *

FROM Condiments

FOR XML AUTO, ELEMENTS, XMLSCHEMA('Condiments'));

SELECT CAST(@mySchema AS XML);

CREATE XML SCHEMA COLLECTION dbo.ProductsAdditionalAttributes AS @mySchema;

GO

-- Drop Auxiliary Tables

DROP TABLE dbo.Beverages, dbo.Condiments;

GO

-- Validate XML instances

ALTER TABLE Production.Products

ALTER COLUMN additionalattributes

XML(dbo.ProductsAdditionalAttributes);

GO

-- Function to Retrieve the Namespace

CREATE FUNCTION dbo.GetNamespace(@chkcol XML)

RETURNS NVARCHAR(15)

AS

BEGIN

RETURN @chkcol.value('namespace-uri((/*)[1])','NVARCHAR(15)')

END;

GO

-- Function to Retrieve the Category Name

CREATE FUNCTION dbo.GetCategoryName(@catid INT)

RETURNS NVARCHAR(15)

AS

BEGIN

RETURN

(SELECT categoryname

FROM Production.Categories

WHERE categoryid = @catid)

END;

GO

-- Add the Constraint

ALTER TABLE Production.Products ADD CONSTRAINT ck_Namespace

CHECK (dbo.GetNamespace(additionalattributes) =

dbo.GetCategoryName(categoryid));

GO

-- Valid Data

-- Beverage

UPDATE Production.Products

SET additionalattributes = N'

<Beverages xmlns="Beverages">

<percentvitaminsRDA>27</percentvitaminsRDA>

</Beverages>'

WHERE productid = 1;

-- Condiment

UPDATE Production.Products

SET additionalattributes = N'

<Condiments xmlns="Condiments">

<shortdescription>very sweet</shortdescription>

</Condiments>'

WHERE productid = 3;

GO

-- Invalid Data

-- String instead of int

UPDATE Production.Products

SET additionalattributes = N'

<Beverages xmlns="Beverages">

<percentvitaminsRDA>twenty seven</percentvitaminsRDA>

</Beverages>'

WHERE productid = 1;

-- Wrong namespace

UPDATE Production.Products

SET additionalattributes = N'

<Condiments xmlns="Condiments">

<shortdescription>very sweet</shortdescription>

</Condiments>'

WHERE productid = 2;

-- Wrong element

UPDATE Production.Products

SET additionalattributes = N'

<Condiments xmlns="Condiments">

<unknownelement>very sweet</unknownelement>

</Condiments>'

WHERE productid = 3;

GO

-- Clean up

ALTER TABLE Production.Products

DROP CONSTRAINT ck_Namespace;

ALTER TABLE Production.Products

DROP COLUMN additionalattributes;

DROP XML SCHEMA COLLECTION dbo.ProductsAdditionalAttributes;

DROP FUNCTION dbo.GetNamespace;

DROP FUNCTION dbo.GetCategoryName;

GO

Ushtrime

1. Gjeneroni per XML Auto, Path dhe Raw informacion mbi Categorite dhe produktet qe i perkasin kategorive.