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

Advanced XML Processing

By Jacob Sebastian,

Introduction

In the previous article, we have seen some examples which demonstrate the XML processing capabilities of SQL Server 2005. We had seen several examples which shows how to read values from an XML variable/field.

In this installment, we will look into the different ways to generate an XML buffer/variable with the results of a query. Most of the times you would need this if you need to call a function/SP which takes an XML variable as a parameter.

The results of a query can be transformed to XML format by using the FOR XML TSQL keyword. FOR XML should always be used with any of the following keywords: AUTO, RAW, PATH or EXPLICIT. In this article we will see the usages of AUTO and RAW.

Examples

Step 1

    1 /*

    2     Let us create the sample table and populate it.

    3 */

    4 

    5 CREATE TABLE [dbo].[OrderDetails](

    6     [OrderDetailID] [int] IDENTITY(1,1) NOT NULL,

    7     [OrderNumber] VARCHAR(10) NOT NULL,

    8     [ItemNumber] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    9     [Qty] [int] NULL,

   10     [Rate] FLOAT NULL,

   11     [QtyPicked] INT NULL

   12 ) ON [PRIMARY]

   13 

   14 INSERT INTO OrderDetails (OrderNumber, ItemNumber, Qty, Rate, QtyPicked)

   15     SELECT '00001', 'A001', 10, 11.25, 0

   16     UNION SELECT '00001', 'A002', 20, 15, 0

   17     UNION SELECT '00001', 'A003', 30, 23.75, 0

Step 2

    1 /*

    2     The simplest way to return values in XML format is to use the keyword

    3     FOR XML with AUTO.

    4 */

    5 

    6 SELECT OrderNumber, ItemNumber, Qty FROM OrderDetails FOR XML AUTO

    7 

    8 /*

    9 OUTPUT:

   10 

   11 <OrderDetails OrderNumber="00001" ItemNumber="A001" Qty="10" />

   12 <OrderDetails OrderNumber="00001" ItemNumber="A002" Qty="20" />

   13 <OrderDetails OrderNumber="00001" ItemNumber="A003" Qty="30" />

   14 */

Step 3

    1 /*

    2     Though the query returns the results in XML format,

    3     it is not an XML data type. The result is NVARCHAR.

    4     To return the results as an XML data type, use the

    5     keyword TYPE.

    6 */

    7 

    8 SELECT OrderNumber, ItemNumber, Qty FROM OrderDetails FOR XML AUTO, TYPE

    9 

   10 /*

   11 OUTPUT:

   12 

   13 <OrderDetails OrderNumber="00001" ItemNumber="A001" Qty="10" />

   14 <OrderDetails OrderNumber="00001" ItemNumber="A002" Qty="20" />

   15 <OrderDetails OrderNumber="00001" ItemNumber="A003" Qty="30" />

   16 */

Step 4

    1 /*

    2     By default, SQL SERVER returns the values as attributes.

    3     Some times you might need the values as nodes. Use the

    4     ELEMENTS keyword for that.

    5 */

    6 

    7 SELECT OrderNumber, ItemNumber, Qty FROM OrderDetails FOR XML AUTO, TYPE, ELEMENTS

    8 

    9 /*

   10 OUTPUT:

   11 

   12 <OrderDetails>

   13   <OrderNumber>00001</OrderNumber>

   14   <ItemNumber>A001</ItemNumber>

   15   <Qty>10</Qty>

   16 </OrderDetails>

   17 <OrderDetails>

   18   <OrderNumber>00001</OrderNumber>

   19   <ItemNumber>A002</ItemNumber>

   20   <Qty>20</Qty>

   21 </OrderDetails>

   22 <OrderDetails>

   23   <OrderNumber>00001</OrderNumber>

   24   <ItemNumber>A003</ItemNumber>

   25   <Qty>30</Qty>

   26 </OrderDetails>

   27 */

Step 5

    1 /*

    2     You will notice that the result does not have a ROOT element.

    3     A correct XML document/fragment should always have a ROOT element.

    4     Let us have this added by using the ROOT keyword.

    5 */

    6 

    7 SELECT OrderNumber, ItemNumber, Qty FROM OrderDetails FOR XML AUTO, TYPE, ELEMENTS, ROOT

    8 

    9 /*

   10 OUTPUT:

   11 

   12 <root>

   13   <OrderDetails>

   14     <OrderNumber>00001</OrderNumber>

   15     <ItemNumber>A001</ItemNumber>

   16     <Qty>10</Qty>

   17   </OrderDetails>

   18   <OrderDetails>

   19     <OrderNumber>00001</OrderNumber>

   20     <ItemNumber>A002</ItemNumber>

   21     <Qty>20</Qty>

   22   </OrderDetails>

   23   <OrderDetails>

   24     <OrderNumber>00001</OrderNumber>

   25     <ItemNumber>A003</ItemNumber>

   26     <Qty>30</Qty>

   27   </OrderDetails>

   28 </root>

   29 */

Step 6

    1 /*

    2     Well, we have a <root> element now. However, the name is not good.

    3     Let us change the name of the root element.

    4 */

    5 

    6 SELECT OrderNumber, ItemNumber, Qty FROM OrderDetails FOR XML AUTO, TYPE, ELEMENTS, ROOT('orderInfo')

    7 

    8 /*

    9 OUTPUT:

   10 

   11 <orderInfo>

   12   <OrderDetails>

   13     <OrderNumber>00001</OrderNumber>

   14     <ItemNumber>A001</ItemNumber>

   15     <Qty>10</Qty>

   16   </OrderDetails>

   17   <OrderDetails>

   18     <OrderNumber>00001</OrderNumber>

   19     <ItemNumber>A002</ItemNumber>

   20     <Qty>20</Qty>

   21   </OrderDetails>

   22   <OrderDetails>

   23     <OrderNumber>00001</OrderNumber>

   24     <ItemNumber>A003</ItemNumber>

   25     <Qty>30</Qty>

   26   </OrderDetails>

   27 </orderInfo>

   28 */

Step 7

    1 /*

    2     So far, we have seen how to assign a custom name to the <root> element as

    3     well as a custom name to each row. it is also possible to give a custom

    4     name to each element by using a column alias. The following example shows that.

    5 */

    6 

    7 SELECT

    8     OrderNumber as 'OrderNum',

    9     ItemNumber as ItemCode,

   10     Qty as Quantity

   11 FROM OrderDetails FOR XML AUTO, TYPE, ELEMENTS, ROOT('itemInfo')

   12 

   13 /*

   14 OUTPUT:

   15 

   16 <itemInfo>

   17   <OrderDetails>

   18     <OrderNum>00001</OrderNum>

   19     <ItemCode>A001</ItemCode>

   20     <Quantity>10</Quantity>

   21   </OrderDetails>

   22   <OrderDetails>

   23     <OrderNum>00001</OrderNum>

   24     <ItemCode>A002</ItemCode>

   25     <Quantity>20</Quantity>

   26   </OrderDetails>

   27   <OrderDetails>

   28     <OrderNum>00001</OrderNum>

   29     <ItemCode>A003</ItemCode>

   30     <Quantity>30</Quantity>

   31   </OrderDetails>

   32 </itemInfo>

   33 */

Step 8

    1 /*

    2     So far, we have seen, how to rename the <root> element

    3     as well as the columns. Now let us see how to rename the

    4     names of rows. By default the AUTO keyword generates rows

    5     with the name of the table/alias.

    6 */

    7 

    8 SELECT

    9     OrderNumber,

   10     ItemNumber,

   11     Qty

   12 FROM OrderDetails itemInfo FOR XML AUTO, TYPE, ELEMENTS, ROOT('orderInfo')

   13 

   14 /*

   15 OUTPUT:

   16 

   17 <orderInfo>

   18   <itemInfo>

   19     <OrderNumber>00001</OrderNumber>

   20     <ItemNumber>A001</ItemNumber>

   21     <Qty>10</Qty>

   22   </itemInfo>

   23   <itemInfo>

   24     <OrderNumber>00001</OrderNumber>

   25     <ItemNumber>A002</ItemNumber>

   26     <Qty>20</Qty>

   27   </itemInfo>

   28   <itemInfo>

   29     <OrderNumber>00001</OrderNumber>

   30     <ItemNumber>A003</ItemNumber>

   31     <Qty>30</Qty>

   32   </itemInfo>

   33 </orderInfo>

   34 */

Step 9

    1 /*

    2     In the previous example, we had assigned an alias to the table

    3     in order to customize the element names of each row. This approach works well.

    4     However, if the query is complex, some times, it will be very confusing to

    5     use alias names just to format the XML node names.

    6 

    7     The AUTO keyword does not provide a way to customize the name of rows. By using

    8     the RAW keyword, instead of AUTO, we can easily customize the rows. The following

    9     example demonstrates this by using the RAW keyword.

   10 */

   11 

   12 SELECT

   13     OrderNumber,

   14     ItemNumber,

   15     Qty

   16 FROM OrderDetails FOR XML RAW('itemInfo'), TYPE, ELEMENTS, ROOT('orderInfo')

   17 

   18 /*

   19 OUTPUT:

   20 

   21 <orderInfo>

   22   <itemInfo>

   23     <OrderNumber>00001</OrderNumber>

   24     <ItemNumber>A001</ItemNumber>

   25     <Qty>10</Qty>

   26   </itemInfo>

   27   <itemInfo>

   28     <OrderNumber>00001</OrderNumber>

   29     <ItemNumber>A002</ItemNumber>

   30     <Qty>20</Qty>

   31   </itemInfo>

   32   <itemInfo>

   33     <OrderNumber>00001</OrderNumber>

   34     <ItemNumber>A003</ItemNumber>

   35     <Qty>30</Qty>

   36   </itemInfo>

   37 </orderInfo>

   38 */

Step 10

    1 /*

    2     So far, we are able to format the XML results in the way we wish.

    3     We are able to rename the root node, rows and element names.

    4 

    5     Now let us look at a different case. When SQL Server generates the

    6     XML results, it will skip the columns which has NULL values. Let us

    7     look at an example.

    8 

    9     The code below, updates a column to NULL. Look at the results. The

   10     third row does not have <Qty> element.

   11 */

   12 

   13 UPDATE OrderDetails SET Qty = NULL WHERE OrderDetailID = 3

   14 

   15 SELECT

   16     OrderNumber,

   17     ItemNumber,

   18     Qty

   19 FROM OrderDetails FOR XML RAW('itemInfo'), TYPE, ELEMENTS, ROOT('orderInfo')

   20 

   21 /*

   22 OUTPUT:

   23 

   24 <orderInfo>

   25   <itemInfo>

   26     <OrderNumber>00001</OrderNumber>

   27     <ItemNumber>A001</ItemNumber>

   28     <Qty>10</Qty>

   29   </itemInfo>

   30   <itemInfo>

   31     <OrderNumber>00001</OrderNumber>

   32     <ItemNumber>A002</ItemNumber>

   33     <Qty>20</Qty>

   34   </itemInfo>

   35   <itemInfo>

   36     <OrderNumber>00001</OrderNumber>

   37     <ItemNumber>A003</ItemNumber>

   38   </itemInfo>

   39 </orderInfo>

   40 */

Step 11

    1 /*

    2     In the previous example, we have seen that, if a column is

    3     NULL, then the element will not be present in the generated XML.

    4 

    5     This can create problems most of the times. For example, if we

    6     need to pass this XML fragment to another function/SP and if

    7     that function/SP expects the element to be present in all the rows

    8     the function/SP will fail.

    9 

   10     So, we need a way to generate an empty element when the value is NULL.

   11     The keyword XSINIL does the trick.

   12 */

   13 

   14 SELECT

   15     OrderNumber,

   16     ItemNumber,

   17     Qty

   18 FROM OrderDetails FOR XML RAW('itemInfo'), TYPE, ELEMENTS XSINIL, ROOT('orderInfo')

   19 

   20 /*

   21 OUTPUT:

   22 

   23 <orderInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

   24   <itemInfo>

   25     <OrderNumber>00001</OrderNumber>

   26     <ItemNumber>A001</ItemNumber>

   27     <Qty>10</Qty>

   28   </itemInfo>

   29   <itemInfo>

   30     <OrderNumber>00001</OrderNumber>

   31     <ItemNumber>A002</ItemNumber>

   32     <Qty>20</Qty>

   33   </itemInfo>

   34   <itemInfo>

   35     <OrderNumber>00001</OrderNumber>

   36     <ItemNumber>A003</ItemNumber>

   37     <Qty xsi:nil="true" />

   38   </itemInfo>

   39 </orderInfo>

   40 */

Conclusions

In this article, we have seen the usage of keywords AUTO and RAW for formatting the results as XML. In the next article we will see the usage PATH and EXPLICIT.

Total article views: 27875 | Views in the last 30 days: 92
 
Related Articles
FORUM

Advanced XML Processing

Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jSeb...

FORUM

Stored Proc - Dealing with Orders and OrderDetails...

In my database, I have 2 tables, Orders and OrderDetails. This may be obvious, but Orders holds i...

ARTICLE

SQL Server 2008 - The Power of Merge

We have a first look at one of the features in SQL Server 2008 that was cut from SQL Server 2005, bu...

FORUM

SSIS Export to Flat File with a blank line b/w each Order

Anyone used MYOB for Orders/OrderDetails records? Need a line break between each OrderID?

ARTICLE

Sales Order Workshop Part IV

In the fourth installment of this series, Jacob Sebastian moves on to SQL Server 2005 and the new XM...

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