MSDN WebCast Demo: FOR XML and UDTs, FOR XML PATH
-- FOR XML
-- Execute every statement in the sample in order
-- (c) 2004, 2005 Microsoft Corp.
-- The following sample queries assume that you have created the Northwind sample database
USE
master
USE msdn2005
GO
-- FOR XML and UDTs
--------------------
-- not directly supported in FOR XML
select * from Math for xml raw('doc'), elements
-- Workaround: Explicit CAST
select CAST(c as XML) as c from Math for xml raw('doc'), elements
USE
Northwind
GO
-- Explicit and PATH modes
--------------------------
-- new: PATH mode to mix and match
SELECT CustomerID as "@CustomerID",
(SELECT OrderID as "@OrderID"
FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID
FOR XML PATH('Order'), TYPE),
(SELECT DISTINCT LastName as "LastName"
FROM Employees
JOIN Orders ON Orders.EmployeeID = Employees.EmployeeID
WHERE Customers.CustomerID = Orders.CustomerID
FOR XML PATH('Employee'), TYPE)
FROM Customers
FOR XML PATH('Customer')
go
-- old: same using existing EXPLICIT mode query
SELECT 1 as TAG, NULL as Parent,
CustomerID as "Customer!1!CustomerID",
NULL as "Order!2!OrderID",
NULL as "Employee!3!LastName!element"
FROM Customers
UNION ALL
SELECT 2, 1,
Customers.CustomerID,
Orders.OrderID,
NULL
FROM Orders
JOIN Customers ON Orders.CustomerID = Customers.CustomerID
UNION ALL
SELECT DISTINCT 3, 1,
Customers.CustomerID,
NULL,
Employees.LastName
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID
JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY "Customer!1!CustomerID","Employee!3!LastName!element","Order!2!OrderID"
FOR XML Explicit
go
-- new: PATH mode: Complex subelements, wrapper elements and adding a root element
SELECT CustomerID as "@CustomerID",
CompanyName,
Address as "address/street",
City as "address/city",
Region as "address/region",
PostalCode as "address/zip",
Country as "address/country",
ContactName as "contact/name",
ContactTitle as "contact/title",
Phone as "contact/phone",
Fax as "contact/fax"
FROM Customers
FOR XML PATH('customer'), ROOT('doc')
go
-- old: Explicit to achieve the above
SELECT TOP 1
1 as TAG, NULL as Parent,
1 as "doc!1!dummy!hide",
NULL as "Customer!2!CustomerID", NULL as "Customer!2!CompanyName!element",
NULL as "address!3!street!element", NULL as "address!3!city!element", NULL as "address!3!region!element",
NULL as "address!3!zip!element", NULL as "address!3!country!element",
NULL as "contact!4!name!element", NULL as "contact!4!title!element",
NULL as "contact!4!phone!element", NULL as "contact!4!fax!element"
FROM Customers
UNION ALL
SELECT 2, 1,
1,
CustomerID, CompanyName,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL
FROM Customers
UNION ALL
SELECT 3, 2,
1,
CustomerID, NULL,
Address, City, Region, PostalCode, Country,
NULL,NULL,NULL,NULL
FROM Customers
UNION ALL
SELECT 4,2,
1,
CustomerID, NULL,
NULL, NULL, NULL, NULL, NULL,
ContactName, ContactTitle, Phone, Fax
FROM Customers
ORDER BY "doc!1!dummy!hide","Customer!2!CustomerID"
FOR XML EXPLICIT
go
-- new: WITH Clause (requires April CTP or later)
WITH XMLNAMESPACES(DEFAULT 'urn:customer', 'urn:address' as "ad")
SELECT CustomerID as "@CustomerID",
CompanyName,
Address as "ad:address/ad:street",
City as "ad:address/ad:city",
Region as "ad:address/ad:region",
PostalCode as "ad:address/ad:zip",
Country as "ad:address/ad:country",
ContactName as "contact/name",
ContactTitle as "contact/title",
Phone as "contact/phone",
Fax as "contact/fax"
FROM Customers
FOR XML PATH('customer'), ROOT('doc')
go