/*
As usual, let us first generate the resultset and understand its structure
before proceeding with XML generation. 
*/

SELECT
	1 AS Tag,
	NULL AS Parent,
	c.CountryName AS 'Country!1!name',
	c.Currency AS 'Country!1!currency',
	NULL AS 'City!2!name',
	NULL AS 'Customer!3!id',
	NULL AS 'Customer!3!name',
	NULL AS 'Customer!3!phone'
FROM 
	Countries c
UNION ALL
SELECT 
	2 AS Tag,
	1 AS Parent,
	Country.CountryName,
	Country.Currency,
	City.CityName,
	NULL,
	NULL,
	NULL
FROM Cities City 
INNER JOIN Countries Country ON (Country.CountryID = City.CountryID)
UNION ALL
SELECT 
		3 AS Tag,
		2 AS Parent,
		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 'Country!1!name', 'City!2!name'

/*
OUTPUT:

Tag  Parent Country!1!name Country!1!currency   City!2!name Customer!3!id Customer!3!name   Customer!3!phone
---- ------ -------------- -------------------- ----------- ------------- ----------------- ----------------
1    NULL   England        Pound Sterling       NULL        NULL          NULL              NULL
2    1      England        Pound Sterling       London      NULL          NULL              NULL
3    2      England        Pound Sterling       London      TH            Thomas Hardy      444-444-4444
1    NULL   India          Rupee                NULL        NULL          NULL              NULL
2    1      India          Rupee                New Delhi   NULL          NULL              NULL
3    2      India          Rupee                New Delhi   JS            Jacob Sebastian   555-555-5555
1    NULL   USA            US Dollars           NULL        NULL          NULL              NULL
2    1      USA            US Dollars           NJ          NULL          NULL              NULL
3    2      USA            US Dollars           NJ          EN            Elizabeth Lincoln 333-333-3333
3    2      USA            US Dollars           NY          MK            John Mark         111-111-1111
2    1      USA            US Dollars           NY          NULL          NULL              NULL
3    2      USA            US Dollars           NY          WS            Will Smith        222-222-2222

"Tag"
	Note that, this time we have a few records with value "3" which refers to the third level
	in the XML hierarchy. 
"Parent"
	The new records (Tag = 3) have their parent set to "2" to indicate that the parent of this
	element is the record with "Tag" having a value of "2"
"Customer!3!*"
	These three columns contain the information needed for the third level node.
*/

/*
Let us GENERATE the XML now.
*/

SELECT
	1 AS Tag,
	NULL AS Parent,
	c.CountryName AS 'Country!1!name',
	c.Currency AS 'Country!1!currency',
	NULL AS 'City!2!name',
	NULL AS 'Customer!3!id',
	NULL AS 'Customer!3!name',
	NULL AS 'Customer!3!phone'
FROM 
	Countries c
UNION ALL
SELECT 
	2 AS Tag,
	1 AS Parent,
	Country.CountryName,
	Country.Currency,
	City.CityName,
	NULL,
	NULL,
	NULL
FROM Cities City 
INNER JOIN Countries Country ON (Country.CountryID = City.CountryID)
UNION ALL
SELECT 
		3 AS Tag,
		2 AS Parent,
		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 'Country!1!name', 'City!2!name'
FOR XML EXPLICIT

/*
OUTPUT:

<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="Rupee">
  <City name="New Delhi">
    <Customer id="JS" name="Jacob Sebastian" phone="555-555-5555" />
  </City>
</Country>
<Country name="USA" currency="US Dollars">
  <City name="NJ">
    <Customer id="EN" name="Elizabeth Lincoln" phone="333-333-3333" />
    <Customer id="MK" name="John Mark" phone="111-111-1111" />
  </City>
  <City name="NY">
    <Customer id="WS" name="Will Smith" phone="222-222-2222" />
  </City>
</Country>
*/