NuRoN Consulting, INC.

Blog > SQL Tools > Template Scripts > XML > FOR XML Path

Template Script: XML\FOR XML Path.sql

-- PATH v/s AUTO and RAW
-- PATH provides more control over the hierarchy by manipulating the column alias. This would be very handy most of the times. EXPLICIT provides more control over the XML generation, but the usage is pretty complex. I will present a step by step approach to master the EXPLICIT usage in the next article.
-- Let us take a trial assignment and see how each XML mode (AUTO, RAW and PATH) can be used to accomplish the given task. Let us assume that we have 3 tables: Customer, City and Country. We need to retrieve data from the above tables, in the XML structure given below.
/*

*/
--Let us run the script to generate the tables and insert the data that we need.
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
--Let us start with PATH and see if we can generate the above XML structure with it. Let us write the first version of our TSQL as follows.
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

/*
OUTPUT:


    England
    Pound Sterling

      London

        TH
        Thomas Hardy
        444-444-4444





    India
    Rupee

      New Delhi

        JS
        Jacob Sebastian
        555-555-5555




...

...
*/
--Well, not very good. You must have noticed that the format is not what we needed. We need the values as Attributes, but this query returns values as XML nodes. So, let us look at the following query which generates results where values are presented as Attributes.
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')

/*

*/
--We have results very close to what we need. But we are missing something. You will notice that the results are not grouped. �USA� has 3 nodes in the XML data generated by the query. This should be grouped into a single node. However, PATH does not provide a way to do that. Let us look into other modes to see if they can help in this scenario.
--Let us move on to AUTO mode. The following code tries to generate the required XML structure using the AUTO mode.
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

/*
OUTPUT:

--Here again, we are able to get perfect XML except the ROOT element. XML is not always useful without the REQUIRED root element. AUTO mode does not provide a way to specify a ROOT element for the resultant XML. Let us see if we can find a workaround to get this done. The following code shows a workaround to fix the above limitation.
SELECT CAST ('' + (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) + '' AS XML)

/*

*/
--Now let us see if we can use RAW mode for generating the result that we need. The RAW mode does not provide a way to generate a hierarchy of XML nodes. However, a combination of RAW and AUTO can be used to generate the results that we need. The following code demonstrates it.
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')

/*
OUTPUT:

Description for Template Script: XML\FOR XML Path.sql

Todo