Contents
USE TSQL2012;
GO
SET NOCOUNT ON;
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');
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');
SELECT [Customer].custid AS [custid],
[Customer].companyname AS [companyname],
[Order].orderid AS [orderid],
[Order].orderdate AS [orderdate]
FROM Sales.Customers AS [Customer]
ON [Customer].custid = [Order].custid
WHERE 1 = 2
FOR XML AUTO, ELEMENTS,
XMLSCHEMA('TK461-CustomersOrders');
FOR XML RAW;
FOR XML RAW('Order'), ROOT('CustomersOrders');
SELECT Customer.custid AS [@custid],
Customer.companyname AS [companyname]
ORDER BY Customer.custid
FOR XML PATH ('Customer'), ROOT('Customers');
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 orderid="10952">
<orderdate>2008-03-16T00:00:00</orderdate>
</Customer>
<Customer custid="2">
<companyname>Customer MLTDN</companyname>
<Order orderid="10308">
<orderdate>2006-09-18T00:00:00</orderdate>
<Order orderid="10926">
<orderdate>2008-03-04T00:00:00</orderdate>
</CustomersOrders>';
EXEC sys.sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument;
SELECT *
FROM OPENXML (@DocHandle, '/CustomersOrders/Customer',1)
WITH (custid INT,
companyname NVARCHAR(40));
FROM OPENXML (@DocHandle, '/CustomersOrders/Customer',2)
FROM OPENXML (@DocHandle, '/CustomersOrders/Customer',11)
EXEC sys.sp_xml_removedocument @DocHandle;
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;
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" />
SELECT @x.query('
(: explicit namespace :)
declare namespace co="TK461-CustomersOrders";
//co:Customer[1]/*') AS [Explicit namespace];
(: default namespace :)
declare default element namespace "TK461-CustomersOrders";
//Customer[1]/*') AS [Default element namespace];
(: namespace declared in T-SQL :)
//co:Customer[1]/*') AS [Namespace in WITH clause];
<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 custid="2" companyname="Customer MLTDN">
<Order orderid="10308" orderdate="2006-09-18T00:00:00" />
<Order orderid="10926" orderdate="2008-03-04T00:00:00" />
for $i in //Customer
return
<OrdersInfo>
{ $i/@companyname }
<NumberOfOrders>
{ count($i/Order) }
</NumberOfOrders>
<LastOrder>
{ max($i/Order/@orderid) }
</LastOrder>
</OrdersInfo>
');
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');
SELECT @x.query('(1, 2, 3) != 1');
SELECT @x.query('(5) lt (2)');
SELECT @x.query('(1) eq 1');
SELECT @x.query('(1) ne 1');
SELECT @x.query('(2, 2) eq (2, 2)');
-- error
DECLARE @x AS XML = N'
<Employee empid="2">
<FirstName>fname</FirstName>
<LastName>lname</LastName>
</Employee>
';
DECLARE @v AS NVARCHAR(20) = N'FirstName';
if (sql:variable("@v")="FirstName") then
/Employee/FirstName
else
/Employee/LastName
') AS FirstOrLastName;
SET @x = N'
<!-- Comment 111 -->
<!-- Comment 222 -->
SELECT @x.query('for $i in CustomersOrders/Customer/Order
let $j := $i/orderdate
where $i/@orderid < 10900
order by ($j)[1]
<Order-orderid-element>
<orderid>{data($i/@orderid)}</orderid>
{$j}
</Order-orderid-element>')
AS [Filtered, sorted and reformatted orders with let clause];
ALTER TABLE Production.Products
ADD additionalattributes XML NULL;
CREATE TABLE dbo.Beverages
(
percentvitaminsRDA INT
);
CREATE TABLE dbo.Condiments
shortdescription NVARCHAR(50)
DECLARE @mySchema NVARCHAR(MAX);
SET @mySchema = N'';
SET @mySchema = @mySchema +
(SELECT *
FROM Beverages
FOR XML AUTO, ELEMENTS, XMLSCHEMA('Beverages'));
FROM Condiments
FOR XML AUTO, ELEMENTS, XMLSCHEMA('Condiments'));
SELECT CAST(@mySchema AS XML);
CREATE XML SCHEMA COLLECTION dbo.ProductsAdditionalAttributes AS @mySchema;
DROP TABLE dbo.Beverages, dbo.Condiments;
ALTER COLUMN additionalattributes
XML(dbo.ProductsAdditionalAttributes);
CREATE FUNCTION dbo.GetNamespace(@chkcol XML)
RETURNS NVARCHAR(15)
AS
BEGIN
RETURN @chkcol.value('namespace-uri((/*)[1])','NVARCHAR(15)')
END;
CREATE FUNCTION dbo.GetCategoryName(@catid INT)
RETURN
(SELECT categoryname
FROM Production.Categories
WHERE categoryid = @catid)
ALTER TABLE Production.Products ADD CONSTRAINT ck_Namespace
CHECK (dbo.GetNamespace(additionalattributes) =
dbo.GetCategoryName(categoryid));
-- Beverage
UPDATE Production.Products
SET additionalattributes = N'
<Beverages xmlns="Beverages">
<percentvitaminsRDA>27</percentvitaminsRDA>
</Beverages>'
WHERE productid = 1;
-- Condiment
<Condiments xmlns="Condiments">
<shortdescription>very sweet</shortdescription>
</Condiments>'
WHERE productid = 3;
-- String instead of int
<percentvitaminsRDA>twenty seven</percentvitaminsRDA>
-- Wrong namespace
WHERE productid = 2;
-- Wrong element
<unknownelement>very sweet</unknownelement>
DROP CONSTRAINT ck_Namespace;
DROP COLUMN additionalattributes;
DROP XML SCHEMA COLLECTION dbo.ProductsAdditionalAttributes;
DROP FUNCTION dbo.GetNamespace;
DROP FUNCTION dbo.GetCategoryName;