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

XML Workshop I - Generating XML output using FOR XML with AUTO and RAW

By Jacob Sebastian,

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.

  1. Advanced XML Processing
    This article shows several examples that generates XML data using the FOR XML keyword. This article demonstrated examples using AUTO and RAW modes.
  2. More Advanced XML Processing Examples
    This 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.

Total article views: 19592 | Views in the last 30 days: 33
 
Related Articles
FORUM

select clients with two billing currencies

need a list of clients that have more than one default currency from a table of sales data

FORUM

Total indian currency format

number format in indian currency

FORUM

Trying to understand Currency conversion

Trying to understand Currency conversion

FORUM

Currency Format

Cut out Decimal for Currency format without rounding up

FORUM

Customize SQL Query

Customize

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