Introduction
In the last 2 sessions on XML processing, we have seen various examples of reading and generating XML data. In case you have not seen the last two sessions, you could find them here.
- Advanced XML ProcessingThis article shows several examples that generates XML data using the FOR XML keyword. This article demonstrated examples using AUTO and RAW modes.
- More Advanced XML Processing ExamplesThis article shows several examples that queries/retrieves values from an XML variable/column
In this session, we will see XML processing examples using PATH mode. I would suggest you read the previous article which presented several examples using RAW and AUTO modes.
FOR XML PATH
RAW and AUTO modes are good enough for most of the XML formatting requirements. PATH mode provides a few additional formatting capabilities that we will examine through the examples given below.
Example 1: Generating a hierarchy of XML nodes
1 /*
2 Using the PATH mode, you can generate hierarchies of XML nodes. The XML generation is
3 controlled by the ALIAS of the column. In the example given below, a new node <item> is
4 created which holds "ItemNumber" and "Quantity". Note the slash ("/") used within the
5 column name, which really controls the XML generation.
6 */
7
8 SELECT
9 OrderNumber AS 'orderNumber',
10 ItemNumber AS 'item/itemNumber',
11 Qty AS 'item/Quantity'
12 FROM OrderDetails FOR XML PATH('orderInfo'), TYPE, ELEMENTS, ROOT('order')
13
14 /*
15 OUTPUT:
16
17 <order>
18 <orderInfo>
19 <orderNumber>00001</orderNumber>
20 <item>
21 <itemNumber>A001</itemNumber>
22 <Quantity>10</Quantity>
23 </item>
24 </orderInfo>
25 <orderInfo>
26 <orderNumber>00001</orderNumber>
27 <item>
28 <itemNumber>A002</itemNumber>
29 <Quantity>20</Quantity>
30 </item>
31 </orderInfo>
32 </order>
33 */
Example 2: Generating a List of Values
1 /*
2 We just saw how the PATH keyword can be used to re-structure the XML
3 generated by FOR XML. Let us now look at a few other scenarios where
4 the PATH keyword is useful.
5
6 One of the situations, where the PATH keyword can come handy is to create a list
7 of values. The following query returns a list of Item Numbers from the Order table.
8 */
9
10 SELECT
11 ItemNumber AS 'data()'
12 FROM
13 OrderDetails
14 FOR XML PATH('')
15
16 /*
17 Output:
18
19 A001 A002 A003
20 */
21
22 /*
23 You will note that the above query returns the results as a SPACE separated
24 value. Most of the times we work with COMMA separated values, not SPACE separated
25 values. So let us see how to generate a COMMA separated value list.
26
27 There is no direct way to do this. But we can do it with a small trick. Look at
28 the following example
29 */
30
31 SELECT
32 ',' + ItemNumber AS 'data()'
33 FROM
34 OrderDetails
35 FOR XML PATH('')
36
37 /*
38 Output:
39
40 ,A001 ,A002 ,A003
41 */
42
43 /*
44 Well, we are almost done. But it is not really good still. You will note that the
45 string starts with a COMMA. Lets use the STUFF function to remove this. Here is the
46 final query.
47 */
48
49 SELECT STUFF(
50 (SELECT
51 ',' + ItemNumber AS 'data()'
52 FROM
53 OrderDetails
54 FOR XML PATH('')),1,1,'')
55
56 /*
57 Output:
58
59 A001 ,A002 ,A003
60 */
61
62 /*
63 I just found Jamie's blog which suggests that using the above approach is
64 better than the other options to create a list of values. You can find the blog
65 here: http://blogs.conchango.com/jamiethomson/archive/2007/04/05/T_2D00_SQL_3A00_-A-T_2D00_SQL-Poser--_2D00_--Part-3.aspx
66 */
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.
1 <customersByRegion>
2 <country name="USA" currency="US Dollars">
3 <city name="NY">
4 <customer id="MK" name="John Mark" phone="111-111-1111"/>
5 <customer id="WS" name="Will Smith" phone="222-222-2222"/>
6 </city>
7 <city name="NJ">
8 <customer id="EN" name="Elizabeth Lincoln" phone="333-333-3333"/>
9 </city>
10 </country>
11 <country name="England" currency="Pound Sterling">
12 <city name="London">
13 <customer id="TH" name="Thomas Hardy" phone="444-444-4444"/>
14 </city>
15 </country>
16 <country name="India" currency="Rupees">
17 <city name="New Delhi">
18 <customer id="JS" name="Jacob Sebastian" phone="555-555-5555"/>
19 </city>
20 </country>
21 </customersByRegion>
Let us run the script to generate the tables and insert the data that we need.
1 CREATE TABLE Countries (CountryID INT, CountryName VARCHAR(20), Currency VARCHAR(20))
2 CREATE TABLE Cities (CityID INT, CityName VARCHAR(20), CountryID INT)
3 CREATE TABLE Customers (CustomerNumber VARCHAR(2), CustomerName VARCHAR(40), Phone VARCHAR(20), CityID INT)
4
5 INSERT INTO Countries(CountryID, CountryName, Currency)
6 SELECT 1 AS CountryID, 'USA' AS CountryName, 'US Dollars' as Currency UNION
7 SELECT 2, 'England', 'Pound Sterling' UNION
8 SELECT 3, 'India', 'Rupee'
9
10 INSERT INTO Cities(CityID, CityName, CountryID)
11 SELECT 1 AS CityID, 'NY' AS CityName, 1 AS CountryID UNION
12 SELECT 2, 'NJ', 1 UNION
13 SELECT 3, 'London', 2 UNION
14 SELECT 4, 'New Delhi', 3
15
16 INSERT INTO Customers(CustomerNumber, CustomerName, Phone, CityID)
17 SELECT 'MK' AS CustomerNumber, 'John Mark' AS CustomerName, '111-111-1111' AS Phone, 1 AS CityID UNION
18 SELECT 'WS', 'Will Smith', '222-222-2222', 1 UNION
19 SELECT 'EN', 'Elizabeth Lincoln', '333-333-3333', 2 UNION
20 SELECT 'TH', 'Thomas Hardy', '444-444-4444', 3 UNION
21 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.
1 SELECT
2 Country.CountryName AS 'country/name',
3 Country.Currency AS 'country/currency',
4 City.CityName AS 'country/city/name',
5 Customer.CustomerNumber AS 'country/city/customer/id',
6 Customer.CustomerName AS 'country/city/customer/name',
7 Customer.Phone AS 'country/city/customer/phone'
8 FROM
9 Customers Customer
10 INNER JOIN Cities City ON (City.CityID = Customer.CityID)
11 INNER JOIN Countries Country ON (Country.CountryID = City.CountryID)
12 ORDER BY CountryName, CityName
13 FOR XML PATH
14
15 /*
16 OUTPUT:
17
18 <row>
19 <country>
20 <name>England</name>
21 <currency>Pound Sterling</currency>
22 <city>
23 <name>London</name>
24 <customer>
25 <id>TH</id>
26 <name>Thomas Hardy</name>
27 <phone>444-444-4444</phone>
28 </customer>
29 </city>
30 </country>
31 </row>
32 <row>
33 <country>
34 <name>India</name>
35 <currency>Rupee</currency>
36 <city>
37 <name>New Delhi</name>
38 <customer>
39 <id>JS</id>
40 <name>Jacob Sebastian</name>
41 <phone>555-555-5555</phone>
42 </customer>
43 </city>
44 </country>
45 </row>
46 <row>
47 ...
48 </row>
49 ...
50 */
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.
1 SELECT
2 Country.CountryName AS 'country/@name',
3 Country.Currency AS 'country/@currency',
4 City.CityName AS 'country/city/@name',
5 Customer.CustomerNumber AS 'country/city/customer/@id',
6 Customer.CustomerName AS 'country/city/customer/@name',
7 Customer.Phone AS 'country/city/customer/@phone'
8 FROM
9 Customers Customer
10 INNER JOIN Cities City ON (City.CityID = Customer.CityID)
11 INNER JOIN Countries Country ON (Country.CountryID = City.CountryID)
12 ORDER BY CountryName, CityName
13 FOR XML PATH(''), ROOT('CustomersByRegion')
14
15 /*
16 <CustomersByRegion>
17 <country name="England" currency="Pound Sterling">
18 <city name="London">
19 <customer id="TH" name="Thomas Hardy" phone="444-444-4444" />
20 </city>
21 </country>
22 <country name="India" currency="Rupee">
23 <city name="New Delhi">
24 <customer id="JS" name="Jacob Sebastian" phone="555-555-5555" />
25 </city>
26 </country>
27 <country name="USA" currency="US Dollars">
28 <city name="NJ">
29 <customer id="EN" name="Elizabeth Lincoln" phone="333-333-3333" />
30 </city>
31 </country>
32 <country name="USA" currency="US Dollars">
33 <city name="NY">
34 <customer id="MK" name="John Mark" phone="111-111-1111" />
35 </city>
36 </country>
37 <country name="USA" currency="US Dollars">
38 <city name="NY">
39 <customer id="WS" name="Will Smith" phone="222-222-2222" />
40 </city>
41 </country>
42 </CustomersByRegion>
43 */
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.
1 SELECT
2 Country.CountryName AS [name],
3 Country.Currency,
4 City.CityName AS [name],
5 Customer.CustomerNumber AS [id],
6 Customer.CustomerName AS [name],
7 Customer.Phone
8 FROM
9 Customers Customer
10 INNER JOIN Cities City ON (City.CityID = Customer.CityID)
11 INNER JOIN Countries Country ON (Country.CountryID = City.CountryID)
12 ORDER BY CountryName, CityName
13 FOR XML AUTO
14
15 /*
16 OUTPUT:
17
18 <Country name="England" Currency="Pound Sterling">
19 <City name="London">
20 <Customer id="TH" name="Thomas Hardy" Phone="444-444-4444" />
21 </City>
22 </Country>
23 <Country name="India" Currency="Rupee">
24 <City name="New Delhi">
25 <Customer id="JS" name="Jacob Sebastian" Phone="555-555-5555" />
26 </City>
27 </Country>
28 <Country name="USA" Currency="US Dollars">
29 <City name="NJ">
30 <Customer id="EN" name="Elizabeth Lincoln" Phone="333-333-3333" />
31 </City>
32 <City name="NY">
33 <Customer id="MK" name="John Mark" Phone="111-111-1111" />
34 <Customer id="WS" name="Will Smith" Phone="222-222-2222" />
35 </City>
36 </Country>
37 */
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.
1 SELECT CAST ('<CustomersByRegion>' + (SELECT
2 Country.CountryName AS [name],
3 Country.Currency,
4 City.CityName AS [name1],
5 Customer.CustomerNumber AS [id],
6 Customer.CustomerName AS [name2],
7 Customer.Phone
8 FROM
9 Customers Customer
10 INNER JOIN Cities City ON (City.CityID = Customer.CityID)
11 INNER JOIN Countries Country ON (Country.CountryID = City.CountryID)
12 ORDER BY CountryName, CityName
13 FOR XML AUTO) + '</CustomersByRegion>' AS XML)
14
15 /*
16 <CustomersByRegion>
17 <Country name="England" Currency="Pound Sterling">
18 <City name1="London">
19 <Customer id="TH" name2="Thomas Hardy" Phone="444-444-4444" />
20 </City>
21 </Country>
22 <Country name="India" Currency="Rupee">
23 <City name1="New Delhi">
24 <Customer id="JS" name2="Jacob Sebastian" Phone="555-555-5555" />
25 </City>
26 </Country>
27 <Country name="USA" Currency="US Dollars">
28 <City name1="NJ">
29 <Customer id="EN" name2="Elizabeth Lincoln" Phone="333-333-3333" />
30 </City>
31 <City name1="NY">
32 <Customer id="MK" name2="John Mark" Phone="111-111-1111" />
33 <Customer id="WS" name2="Will Smith" Phone="222-222-2222" />
34 </City>
35 </Country>
36 </CustomersByRegion>
37 */
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.
1 SELECT CAST(
2 (SELECT
3 Country.CountryName AS [name],
4 Country.Currency,
5 City.CityName AS [name],
6 Customer.CustomerNumber AS [id],
7 Customer.CustomerName AS [name],
8 Customer.Phone
9 FROM
10 Customers Customer
11 INNER JOIN Cities City ON (City.CityID = Customer.CityID)
12 INNER JOIN Countries Country ON (Country.CountryID = City.CountryID)
13 ORDER BY CountryName, CityName
14 FOR XML AUTO) AS XML)
15 FOR XML RAW('CustomersByRegion')
16
17 /*
18 OUTPUT:
19
20 <CustomersByRegion>
21 <Country name="England" Currency="Pound Sterling">
22 <City name="London">
23 <Customer id="TH" name="Thomas Hardy" Phone="444-444-4444" />
24 </City>
25 </Country>
26 <Country name="India" Currency="Rupee">
27 <City name="New Delhi">
28 <Customer id="JS" name="Jacob Sebastian" Phone="555-555-5555" />
29 </City>
30 </Country>
31 <Country name="USA" Currency="US Dollars">
32 <City name="NJ">
33 <Customer id="EN" name="Elizabeth Lincoln" Phone="333-333-3333" />
34 </City>
35 <City name="NY">
36 <Customer id="MK" name="John Mark" Phone="111-111-1111" />
37 <Customer id="WS" name="Will Smith" Phone="222-222-2222" />
38 </City>
39 </Country>
40 </CustomersByRegion>
41 */
Conclusions
We have seen several examples of AUTO, RAW and PATH modes and I guess the usage of the above modes might be clearly demonstrated in the examples we have seen so far. There is one more mode, EXPLICIT, which I have not presented yet. The EXPLICIT mode provides much more control over the structure of the generated XML. However, it is much more complex to use. In the next article, I will present a step-by-step tutorial to understand the usage of the EXPLICIT mode.
