The use of PATH is amazing.

Just as on previous post of this serie, here is the code with comments in spanish.

PS: the links to the code files are broken, maybe where not corrected as on previous posts

--usando PATH se pueden crear jerarquías en los nodos XML

--la creacion de los nodos la controlamos mediante alias en las columnas, en este caso: <item> contendrá ItemNumber y Quantity

--notar el uso de '/' dentro del nombre de la columna que es el que controla la creación del XML en definitiva

SELECT OrderNumber AS 'orderNumber',

ItemNumber AS 'item/itemNumber',

Qty AS 'item/Quantity'

FROM OrderDetails FOR XML PATH('orderInfo'), TYPE, ELEMENTS, ROOT('order')

--a veces simplemente queremos una lista de valores, PATH también sirve para esto, aqui devolvemos la lista de los ItemNumbers de la tabla Order

SELECT ItemNumber AS 'data()' FROM OrderDetails FOR XML PATH('')

--la query anterior devuelve la lista usando espacio como separador, pero seguramente una ',' será mejor

SELECT ',' + ItemNumber AS 'data()' FROM OrderDetails FOR XML PATH('')

--pero en el ejemplo anterior tenemos una coma al inicio de la cadena, no nos sirve, tenemos que quitarla

--el comando STUFF nos ayudará

--de hecho esta manera de concatenar valores resulta mucho más rápida que el método normal usando querys, ver:

--http://blogs.conchango.com/jamiethomson/archive/2007/04/05/T_2D00_SQL_3A00_-A-T_2D00_SQL-Poser--_2D00_--Part-3.aspx

SELECT STUFF((SELECT ',' + ItemNumber AS 'data()' FROM OrderDetails FOR XML PATH('')),1,1,'')

--como hemos visto PATH provee una buena herramienta para crar los XML manipulando los alias de las columnas

--la mayor parte de las veces PATH será suficiente pero a veces necesitarmos EXPLICIT que nos dará aún más control, pero su uso es mas complicado

--veamos como podemos usar diferentes modos del comando XML usando AUTO,RAW y PATH para crear un fichero XML como el siguiente

/*

<customersByRegion>

<country name="USA" currency="US Dollars">

<city name="NY">

<customer id="MK" name="John Mark" phone="111-111-1111"/>

<customer id="WS" name="Will Smith" phone="222-222-2222"/>

</city>

<city name="NJ">

<customer id="EN" name="Elizabeth Lincoln" phone="333-333-3333"/>

</city>

</country>

<country name="England" currency="Pound Sterling">

<city name="London">

<customer id="TH" name="Thomas Hardy" phone="444-444-4444"/>

</city>

</country>

<country name="India" currency="Rupees">

<city name="New Delhi">

<customer id="JS" name="Jacob Sebastian" phone="555-555-5555"/>

</city>

</country>

</customersByRegion>

*/

CREATE TABLE Countries (CountryID INT, CountryName VARCHAR(20), Currency VARCHAR(20))

CREATE TABLE Cities (CityID INT, CityName VARCHAR(20), CountryID INT)

CREATE TABLE Customers (CustomerNumber VARCHAR(2), CustomerName VARCHAR(40), Phone VARCHAR(20), CityID INT)

INSERT INTO Countries(CountryID, CountryName, Currency)

SELECT 1 AS CountryID, 'USA' AS CountryName, 'US Dollars' as Currency UNION

SELECT 2, 'England', 'Pound Sterling' UNION

SELECT 3, 'India', 'Rupee'

INSERT INTO Cities(CityID, CityName, CountryID)

SELECT 1 AS CityID, 'NY' AS CityName, 1 AS CountryID UNION

SELECT 2, 'NJ', 1 UNION

SELECT 3, 'London', 2 UNION

SELECT 4, 'New Delhi', 3

INSERT INTO Customers(CustomerNumber, CustomerName, Phone, CityID)

SELECT 'MK' AS CustomerNumber, 'John Mark' AS CustomerName, '111-111-1111' AS Phone, 1 AS CityID UNION

SELECT 'WS', 'Will Smith', '222-222-2222', 1 UNION

SELECT 'EN', 'Elizabeth Lincoln', '333-333-3333', 2 UNION

SELECT 'TH', 'Thomas Hardy', '444-444-4444', 3 UNION

SELECT 'JS', 'Jacob Sebastian', '555-555-5555', 4

SELECT * FROM Countries

SELECT * FROM Cities

SELECT * FROM Customers

--intentemos usando PATH a ver si podemos generar el mismo XML

SELECT Country.CountryName AS 'country/name',

Country.Currency AS 'country/currency',

City.CityName AS 'country/city/name',

Customer.CustomerNumber AS 'country/city/customer/id',

Customer.CustomerName AS 'country/city/customer/name',

Customer.Phone AS 'country/city/customer/phone'

FROM

Customers Customer

INNER JOIN Cities City ON (City.CityID = Customer.CityID)

INNER JOIN Countries Country ON (Country.CountryID = City.CountryID)

ORDER BY CountryName, CityName

FOR XML PATH

--si miramos el resultado vemos que no es correcto, los valores los queremos tener como atributos, no como nodos en el XML

--modifiequemos el PATH y vamos a añadir un ROOT para nombrar la raíz del XML

SELECT Country.CountryName AS 'country/@name',

Country.Currency AS 'country/@currency',

City.CityName AS 'country/city/@name',

Customer.CustomerNumber AS 'country/city/customer/@id',

Customer.CustomerName AS 'country/city/customer/@name',

Customer.Phone AS 'country/city/customer/@phone'

FROM

Customers Customer

INNER JOIN Cities City ON (City.CityID = Customer.CityID)

INNER JOIN Countries Country ON (Country.CountryID = City.CountryID)

ORDER BY CountryName, CityName

FOR XML PATH(''), ROOT('CustomersByRegion')

--ya casi tenemos lo que queremos, pero si miramos el resultado vemos que no tenemos los customer agrupados por país

--usemos ahora el modificador AUTO

SELECT Country.CountryName AS [name],

Country.Currency,

City.CityName AS [name],

Customer.CustomerNumber AS [id],

Customer.CustomerName AS [name],

Customer.Phone

FROM

Customers Customer

INNER JOIN Cities City ON (City.CityID = Customer.CityID)

INNER JOIN Countries Country ON (Country.CountryID = City.CountryID)

ORDER BY CountryName, CityName

FOR XML AUTO

--casi lo tenemos, pero nos falta el elemento ROOT

--con AUTO no hay manera de hacerlo, veamos como mediante un método alternativo

SELECT CAST ('<CustomersByRegion>' + (SELECT

Country.CountryName AS [name],

Country.Currency,

City.CityName AS [name1],

Customer.CustomerNumber AS [id],

Customer.CustomerName AS [name2],

Customer.Phone

FROM

Customers Customer

INNER JOIN Cities City ON (City.CityID = Customer.CityID)

INNER JOIN Countries Country ON (Country.CountryID = City.CountryID)

ORDER BY CountryName, CityName

FOR XML AUTO) + '</CustomersByRegion>' AS XML)

--ya lo tenemos, pero veamos como hacerlo mediante el modificador RAW

--RAW no tiene manera de generar la jerarquía de los nodos XML pero combinandolo con AUTO si podemos hacerlo

SELECT CAST((SELECT

Country.CountryName AS [name],

Country.Currency,

City.CityName AS [name],

Customer.CustomerNumber AS [id],

Customer.CustomerName AS [name],

Customer.Phone

FROM

Customers Customer

INNER JOIN Cities City ON (City.CityID = Customer.CityID)

INNER JOIN Countries Country ON (Country.CountryID = City.CountryID)

ORDER BY CountryName, CityName

FOR XML AUTO) AS XML)

FOR XML RAW('CustomersByRegion')