Introduction
This is the fourth installment of my XML workshop which aims at explaining/demonstrating XML processing in SQL Server 2005. If you have not read the previous articles in this series, I would suggest that you read them before proceeding. Here are the links to the previous articles.
- Part I focuses on generating XML data from the results of a query using FOR XML directive. It explains the usage of AUTO and RAW.
- Part II focuses on retrieving values from the elements and attributes of an XML variable.
- Part III focuses on performing more advanced XML generation using the PATH directive along with FOR XML.
In the previous articles we had seen several examples which demonstrated different ways to generate XML data. Using FOR XML directive along with AUTO, RAW and PATH we can achieve almost all XML formatting/processing requirements. In this article, I would present a few examples which show the power of the keyword EXPLICIT.
FOR XML EXPLICIT
Using EXPLICIT is much more complex than using AUTO, RAW and PATH. Almost all XML formatting requirements can be achieved by using AUTO, RAW and PATH. Their usage is pretty simple and basic. However, there are times when we need more complex XML formatting requirements which AUTO, RAW and PATH cannot handle. EXPLICIT supports very complex XML formatting and gives you more control over how the output is generated.
EXPLICIT expects that the query results will be in a specific structure. All the information needed for the formatting is provided in the query results. The following examples will explain this in detail.
Let us try to generate the XML structure we created previously with FOR XML PATH. As I have mentioned earlier, using EXPLICIT is much more complex than using the other directives. To present it as simple as possible, I will take a step by step approach to generate the sample XML structure that we need to generate.
Here is the output that we need.
1 <CustomersByRegion>
2 <Country name="England" currency="Pound Sterling">
3 <City name="London">
4 <Customer id="TH" name="Thomas Hardy" phone="444-444-4444" />
5 </City>
6 </Country>
7 <Country name="India" currency="Rupee">
8 <City name="New Delhi">
9 <Customer id="JS" name="Jacob Sebastian" phone="555-555-5555" />
10 </City>
11 </Country>
12 <Country name="USA" currency="US Dollars">
13 <City name="NJ">
14 <Customer id="EN" name="Elizabeth Lincoln" phone="333-333-3333" />
15 <Customer id="MK" name="John Mark" phone="111-111-1111" />
16 </City>
17 <City name="NY">
18 <Customer id="WS" name="Will Smith" phone="222-222-2222" />
19 </City>
20 </Country>
21 </CustomersByRegion>
Let us now start generating the above XML structure using the EXPLICIT mode with FOR XML. As I have mentioned earlier, the usage of EXPLICIT is a bit complex. So I will present a step by step example which will explain the usage in detail.
Step 1: Generate the Country Node
Let us see, how we could generate the Country node. EXPLICIT expects the query results to be in a specific format. The following example shows the structure of the query results and the XML structure generated from the query results.
1 /*
2 Let us first generate the results in the required structure. Once the
3 results are generated in the required format, we shall go ahead and
4 generate the XML.
5 */
6
7 SELECT
8 1 AS Tag,
9 NULL AS Parent,
10 c.CountryName AS 'Country!1!name',
11 c.Currency AS 'Country!1!currency'
12 FROM
13 Countries c
14 /*
15 OUTPUT:
16
17 Tag Parent Country!1!name Country!1!currency
18 ----------- ----------- -------------------- --------------------
19 1 NULL USA US Dollars
20 1 NULL England Pound Sterling
21 1 NULL India Rupee
22
23 "Tag" is a mandatory column. It tells the XML generator the level of the element
24 in the XML hierarchy. In the example, I have put "1" to tell the XML
25 generator that it is the top LEVEL node.
26 "Parent" is the second mandatory column. It tells the XML generator about the
27 parent of the current node. I have put NULL to tell the XML generator
28 that the current element does not have a parent node.
29
30 After the first 2 mandatory (pre-defined) columns, I have put the data that I need.
31
32 "Country!1!name"
33 "Country" is the name of the element
34 "1" specifies the LEVEL of the node in the hierarchy
35 "name" is the name of the attribute
36 "Country!1!currency"
37 "Country" is the name of the element
38 "1" specifies the LEVEL of the node in the hierarchy
39 "currency" is the name of the attribute
40 */
41
42 /*
43 Now let us generate the XML using FOR XML EXPLICIT
44 */
45 SELECT
46 1 AS Tag,
47 NULL AS Parent,
48 c.CountryName AS 'Country!1!name',
49 c.Currency AS 'Country!1!currency'
50 FROM
51 Countries c
52 FOR XML EXPLICIT
53
54 /*
55 OUTPUT:
56
57 <Country name="USA" currency="US Dollars" />
58 <Country name="England" currency="Pound Sterling" />
59 <Country name="India" currency="Rupee" />
60 */
Step 2: Generate the City Node
Now let us modify our query and generate the next level node. The following query generates an XML structure with the first 2 nodes that we require.
1 /*
2 Just like what we did in the previous example, let us first generate
3 the result set and have a close look at its structure. Here is the result set
4 that we need to generate the first two nodes (country and city).
5 */
6
7 SELECT
8 1 AS Tag,
9 NULL AS Parent,
10 c.CountryName AS 'Country!1!name',
11 c.Currency AS 'Country!1!currency',
12 NULL AS 'City!2!name'
13 FROM
14 Countries c
15
16 UNION ALL
17
18 SELECT
19 2 AS Tag,
20 1 AS Parent,
21 Country.CountryName,
22 Country.Currency,
23 City.CityName
24 FROM Cities City
25 INNER JOIN Countries Country ON (Country.CountryID = City.CountryID)
26 ORDER BY 'Country!1!name', 'City!2!name'
27
28 /*
29 OUTPUT:
30
31 Tag Parent Country!1!name Country!1!currency City!2!name
32 ----------- ----------- -------------------- -------------------- --------------------
33 1 NULL England Pound Sterling NULL
34 2 1 England Pound Sterling London
35 1 NULL India Rupee NULL
36 2 1 India Rupee New Delhi
37 1 NULL USA US Dollars NULL
38 2 1 USA US Dollars NJ
39 2 1 USA US Dollars NY
40
41 "Tag"
42 Note that, this time we have a few records with value "2" in the results
43 The records with tag "2" are the second level nodes.
44 "Parent"
45 Note that we have records with value "1" which says that those records have
46 a parent node. The value in the column "Parent" refers to "Tag" of the parent
47 record.
48 "City!2!name"
49 "City" is the name of the second level element
50 "2" refers to the "Tag" which specifies that it is the second level node.
51 "name" is the name of the attribute.
52 */
53
54 /*
55 Now let us generate the XML and see the results.
56 */
57
58 SELECT
59 1 AS Tag,
60 NULL AS Parent,
61 c.CountryName AS 'Country!1!name',
62 c.Currency AS 'Country!1!currency',
63 NULL AS 'City!2!name'
64 FROM
65 Countries c
66
67 UNION ALL
68
69 SELECT
70 2 AS Tag,
71 1 AS Parent,
72 Country.CountryName,
73 Country.Currency,
74 City.CityName
75 FROM Cities City
76 INNER JOIN Countries Country ON (Country.CountryID = City.CountryID)
77 ORDER BY 'Country!1!name', 'City!2!name'
78 FOR XML EXPLICIT
79
80 /*
81 OUTPUT:
82
83 <Country name="England" currency="Pound Sterling">
84 <City name="London" />
85 </Country>
86 <Country name="India" currency="Rupee">
87 <City name="New Delhi" />
88 </Country>
89 <Country name="USA" currency="US Dollars">
90 <City name="NJ" />
91 <City name="NY" />
92 </Country>
93 */
Step 3: Generate the Customer element
So far we had been progressing steadily. Now let us generate the third level nodes. The following example does that.
1 /*
2 As usual, let us first generate the result set and understand its structure
3 before proceeding with XML generation.
4 */
5
6 SELECT
7 1 AS Tag,
8 NULL AS Parent,
9 c.CountryName AS 'Country!1!name',
10 c.Currency AS 'Country!1!currency',
11 NULL AS 'City!2!name',
12 NULL AS 'Customer!3!id',
13 NULL AS 'Customer!3!name',
14 NULL AS 'Customer!3!phone'
15 FROM
16 Countries c
17 UNION ALL
18 SELECT
19 2 AS Tag,
20 1 AS Parent,
21 Country.CountryName,
22 Country.Currency,
23 City.CityName,
24 NULL,
25 NULL,
26 NULL
27 FROM Cities City
28 INNER JOIN Countries Country ON (Country.CountryID = City.CountryID)
29 UNION ALL
30 SELECT
31 3 AS Tag,
32 2 AS Parent,
33 Country.CountryName AS [name],
34 Country.Currency,
35 City.CityName AS [name],
36 Customer.CustomerNumber AS [id],
37 Customer.CustomerName AS [name],
38 Customer.Phone
39 FROM
40 Customers Customer
41 INNER JOIN Cities City ON (City.CityID = Customer.CityID)
42 INNER JOIN Countries Country ON (Country.CountryID = City.CountryID)
43 ORDER BY 'Country!1!name', 'City!2!name'
44
45 /*
46 OUTPUT:
47
48 Tag Parent Country!1!name Country!1!currency City!2!name Customer!3!id Customer!3!name Customer!3!phone
49 ---- ------ -------------- -------------------- ----------- ------------- ----------------- ----------------
50 1 NULL England Pound Sterling NULL NULL NULL NULL
51 2 1 England Pound Sterling London NULL NULL NULL
52 3 2 England Pound Sterling London TH Thomas Hardy 444-444-4444
53 1 NULL India Rupee NULL NULL NULL NULL
54 2 1 India Rupee New Delhi NULL NULL NULL
55 3 2 India Rupee New Delhi JS Jacob Sebastian 555-555-5555
56 1 NULL USA US Dollars NULL NULL NULL NULL
57 2 1 USA US Dollars NJ NULL NULL NULL
58 3 2 USA US Dollars NJ EN Elizabeth Lincoln 333-333-3333
59 3 2 USA US Dollars NY MK John Mark 111-111-1111
60 2 1 USA US Dollars NY NULL NULL NULL
61 3 2 USA US Dollars NY WS Will Smith 222-222-2222
62
63 "Tag"
64 Note that, this time we have a few records with value "3" which refers to the third level
65 in the XML hierarchy.
66 "Parent"
67 The new records (Tag = 3) have their parent set to "2" to indicate that the parent of this
68 element is the record with "Tag" having a value of "2"
69 "Customer!3!*"
70 These three columns contain the information needed for the third level node.
71 */
72
73 /*
74 Let us GENERATE the XML now.
75 */
76
77 SELECT
78 1 AS Tag,
79 NULL AS Parent,
80 c.CountryName AS 'Country!1!name',
81 c.Currency AS 'Country!1!currency',
82 NULL AS 'City!2!name',
83 NULL AS 'Customer!3!id',
84 NULL AS 'Customer!3!name',
85 NULL AS 'Customer!3!phone'
86 FROM
87 Countries c
88 UNION ALL
89 SELECT
90 2 AS Tag,
91 1 AS Parent,
92 Country.CountryName,
93 Country.Currency,
94 City.CityName,
95 NULL,
96 NULL,
97 NULL
98 FROM Cities City
99 INNER JOIN Countries Country ON (Country.CountryID = City.CountryID)
100 UNION ALL
101 SELECT
102 3 AS Tag,
103 2 AS Parent,
104 Country.CountryName AS [name],
105 Country.Currency,
106 City.CityName AS [name],
107 Customer.CustomerNumber AS [id],
108 Customer.CustomerName AS [name],
109 Customer.Phone
110 FROM
111 Customers Customer
112 INNER JOIN Cities City ON (City.CityID = Customer.CityID)
113 INNER JOIN Countries Country ON (Country.CountryID = City.CountryID)
114 ORDER BY 'Country!1!name', 'City!2!name'
115 FOR XML EXPLICIT
116
117 /*
118 OUTPUT:
119
120 <Country name="England" currency="Pound Sterling">
121 <City name="London">
122 <Customer id="TH" name="Thomas Hardy" phone="444-444-4444" />
123 </City>
124 </Country>
125 <Country name="India" currency="Rupee">
126 <City name="New Delhi">
127 <Customer id="JS" name="Jacob Sebastian" phone="555-555-5555" />
128 </City>
129 </Country>
130 <Country name="USA" currency="US Dollars">
131 <City name="NJ">
132 <Customer id="EN" name="Elizabeth Lincoln" phone="333-333-3333" />
133 <Customer id="MK" name="John Mark" phone="111-111-1111" />
134 </City>
135 <City name="NY">
136 <Customer id="WS" name="Will Smith" phone="222-222-2222" />
137 </City>
138 </Country>
139 */
Step 4: Generate the Root Node
We are almost done. We have all the data that we need in the desired XML format. However we are missing the root node, CustomersByRegion. Unlike AUTO and RAW modes, EXPLICIT does not provide a way to generate a root node. To generate a root node, let us generate a dummy result set and UNION it with our data. The dummy result set will be the TOP MOST node and other nodes will be pushed downwards by one level. The following example shows it in detail.
1 SELECT
2 1 AS Tag,
3 NULL AS Parent,
4 NULL AS 'CustomersByRegion!1', -- empty root element
5 NULL AS 'Country!2!name',
6 NULL AS 'Country!2!currency',
7 NULL AS 'City!3!name',
8 NULL AS 'Customer!4!id',
9 NULL AS 'Customer!4!name',
10 NULL AS 'Customer!4!phone'
11 UNION ALL
12 SELECT
13 2 AS Tag,
14 1 AS Parent,
15 NULL,
16 c.CountryName,
17 c.Currency,
18 NULL,
19 NULL,
20 NULL,
21 NULL
22 FROM
23 Countries c
24 UNION ALL
25 SELECT
26 3 AS Tag,
27 2 AS Parent,
28 NULL,
29 Country.CountryName,
30 Country.Currency,
31 City.CityName,
32 NULL,
33 NULL,
34 NULL
35 FROM Cities City
36 INNER JOIN Countries Country ON (Country.CountryID = City.CountryID)
37 UNION ALL
38 SELECT
39 4 AS Tag,
40 3 AS Parent,
41 NULL,
42 Country.CountryName AS [name],
43 Country.Currency,
44 City.CityName AS [name],
45 Customer.CustomerNumber AS [id],
46 Customer.CustomerName AS [name],
47 Customer.Phone
48 FROM
49 Customers Customer
50 INNER JOIN Cities City ON (City.CityID = Customer.CityID)
51 INNER JOIN Countries Country ON (Country.CountryID = City.CountryID)
52 ORDER BY 'Country!2!name', 'City!3!name', Parent
53 FOR XML EXPLICIT
54
55 /*
56 OUTPUT:
57
58 <CustomersByRegion>
59 <Country name="England" currency="Pound Sterling">
60 <City name="London">
61 <Customer id="TH" name="Thomas Hardy" phone="444-444-4444" />
62 </City>
63 </Country>
64 <Country name="India" currency="Rupee">
65 <City name="New Delhi">
66 <Customer id="JS" name="Jacob Sebastian" phone="555-555-5555" />
67 </City>
68 </Country>
69 <Country name="USA" currency="US Dollars">
70 <City name="NJ">
71 <Customer id="EN" name="Elizabeth Lincoln" phone="333-333-3333" />
72 </City>
73 <City name="NY">
74 <Customer id="MK" name="John Mark" phone="111-111-1111" />
75 <Customer id="WS" name="Will Smith" phone="222-222-2222" />
76 </City>
77 </Country>
78 </CustomersByRegion>
79 */
Note that I have added a dummy result set as the first level node. This result set returns a row with all NULL values except the Tag. Note that I did not specify an attribute name with the element (CustomersByRegion!1). This creates an element without any attribute.
Conclusions
In this article, I had tried to explain the usage of EXPLICIT directive along with FOR XML. Most of the XML formatting requirements can be done with keywords AUTO, RAW and PATH. Using those keywords are very simple. You would need EXPLICIT only when a given requirement cannot be fulfilled by those directives.
