Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

XML Workshop IV - FOR XML EXPLICIT

By Jacob Sebastian,

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.

Total article views: 10593 | Views in the last 30 days: 31
 
Related Articles
FORUM

Trying to understand Currency conversion

Trying to understand Currency conversion

FORUM

Pls help Understanding Entity Framework generated Query vs Custom Query

What is difference between the two queries. EF generated Query & custom Query

FORUM

Where to find world data database? Like Currency/ countries

Hi, I'm a Sql Server Programmer and I'm new in area of Data Mining. I was wondering if there's an...

FORUM

Global Customers, Global Data, Different Logic/Columns per country

Hi Guys, We are working on a project to redesign our existing database. We have customers whic...

BLOG

SSAS: Currency Conversion Using Measure Expressions

Currency conversions can be implemented several ways as part of a business intelligence solution.  I...

Tags
sql server 2005    
xml    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones