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
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
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 */
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 */
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 */
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 */
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 */
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 */
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 */
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 */
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 */
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.
