/*
Let us create the sample table and populate it.
*/
CREATE TABLE #OrderDetails(
[OrderDetailID] [int] IDENTITY(1,1) NOT NULL,
[OrderNumber] VARCHAR(10) NOT NULL,
[ItemNumber] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Qty] [int] NULL,
[Rate] FLOAT NULL,
[QtyPicked] INT NULL
) ON [PRIMARY]
INSERT INTO #OrderDetails (OrderNumber, ItemNumber, Qty, Rate, QtyPicked)
SELECT '00001', 'A001', 10, 11.25, 0
UNION SELECT '00001', 'A002', 20, 15, 0
UNION SELECT '00001', 'A003', 30, 23.75, 0
/*
The simplest way to return values in XML format is to use the keyword
FOR XML with AUTO.
*/
SELECT OrderNumber, ItemNumber, Qty FROM #OrderDetails FOR XML AUTO
/*
OUTPUT:
<#OrderDetails OrderNumber="00001" ItemNumber="A001" Qty="10" />
<#OrderDetails OrderNumber="00001" ItemNumber="A002" Qty="20" />
<#OrderDetails OrderNumber="00001" ItemNumber="A003" Qty="30" />
*/
/*
Though the query returns the results in XML format,
it is not an XML data type. The result is NVARCHAR.
To return the results as an XML data type, use the
keyword TYPE.
*/
SELECT OrderNumber, ItemNumber, Qty FROM #OrderDetails FOR XML AUTO, TYPE
/*
OUTPUT:
<#OrderDetails OrderNumber="00001" ItemNumber="A001" Qty="10" />
<#OrderDetails OrderNumber="00001" ItemNumber="A002" Qty="20" />
<#OrderDetails OrderNumber="00001" ItemNumber="A003" Qty="30" />
*/
/*
By default, SQL SERVER returns the values as attributes.
Some times you might need the values as nodes. Use the
ELEMENTS keyword for that.
*/
SELECT OrderNumber, ItemNumber, Qty FROM #OrderDetails FOR XML AUTO, TYPE, ELEMENTS
/*
OUTPUT:
<#OrderDetails>
00001
A001
10
<#OrderDetails>
00001
A002
20
<#OrderDetails>
00001
A003
30
*/
/*
You will notice that the result does not have a ROOT element.
A corret XML document/fragment should always have a ROOT element.
Let us have this added by using the ROOT keyword.
*/
SELECT OrderNumber, ItemNumber, Qty FROM #OrderDetails FOR XML AUTO, TYPE, ELEMENTS, ROOT
/*
OUTPUT:
<#OrderDetails>
00001
A001
10
<#OrderDetails>
00001
A002
20
<#OrderDetails>
00001
A003
30
*/
/*
Well, we have a element now. However, the name is not good.
Let us change the name of the root element.
*/
SELECT OrderNumber, ItemNumber, Qty FROM #OrderDetails FOR XML AUTO, TYPE, ELEMENTS, ROOT('orderInfo')
/*
OUTPUT:
<#OrderDetails>
00001
A001
10
<#OrderDetails>
00001
A002
20
<#OrderDetails>
00001
A003
30
*/
/*
So far, we have seen how to assign a custom name to the element as
well as a custom name to each row. it is also possible to give a custom
name to each element by using a column alias. The following example shows that.
*/
SELECT
OrderNumber as 'OrderNum',
ItemNumber as ItemCode,
Qty as Quantity
FROM #OrderDetails FOR XML AUTO, TYPE, ELEMENTS, ROOT('itemInfo')
/*
OUTPUT:
<#OrderDetails>
00001
A001
10
<#OrderDetails>
00001
A002
20
<#OrderDetails>
00001
A003
30
*/
/*
So far, we have seen, how to rename the element
as well as the columns. Now let us see how to rename the
names of rows. By default the AUTO keyword generates rows
with the name of the table/alias.
*/
SELECT
OrderNumber,
ItemNumber,
Qty
FROM #OrderDetails itemInfo FOR XML AUTO, TYPE, ELEMENTS, ROOT('orderInfo')
/*
OUTPUT:
00001
A001
10
00001
A002
20
00001
A003
30
*/
/*
In the previous example, we had assigned an alias to the table
in order to customize the element names of each row. This approach works well.
However, if the query is complex, some times, it will be very confusing to
use alias names just to format the XML node names.
The AUTO keyword does not provide a way to customize the name of rows. By using
the RAW keyword, instead of AUTO, we can easily customize the rows. The following
example demonstrates this by using the RAW keyword.
*/
SELECT
OrderNumber,
ItemNumber,
Qty
FROM #OrderDetails FOR XML RAW('itemInfo'), TYPE, ELEMENTS, ROOT('orderInfo')
/*
OUTPUT:
00001
A001
10
00001
A002
20
00001
A003
30
*/
/*
So far, we are able to format the XML results in the way we wish.
We are able to rename the root node, rows and element names.
Now let us look at a different case. When SQL Server generates the
XML results, it will skip the columns which has NULL values. Let us
look at an example.
The code below, updates a column to NULL. Look at the results. The
third row does not have element.
*/
UPDATE #OrderDetails SET Qty = NULL WHERE OrderDetailID = 3
SELECT
OrderNumber,
ItemNumber,
Qty
FROM #OrderDetails FOR XML RAW('itemInfo'), TYPE, ELEMENTS, ROOT('orderInfo')
/*
OUTPUT:
00001
A001
10
00001
A002
20
00001
A003
*/
/*
In the previous example, we have seen that, if a column is
NULL, then the element will not be present in the generated XML.
This can create problems most of the times. For example, if we
need to pass this XML fragment to another function/SP and if
that function/SP expects the element to be present in all the rows
the function/SP will fail.
So, we need a way to generate an empty element when the value is NULL.
The keyword XSINIL does the trick.
*/
SELECT
OrderNumber,
ItemNumber,
Qty
FROM #OrderDetails FOR XML RAW('itemInfo'), TYPE, ELEMENTS XSINIL, ROOT('orderInfo')
/*
OUTPUT:
00001
A001
10
00001
A002
20
00001
A003
*/
-- Example 1: Generating a hierarchy of XML nodes
/*
Using the PATH mode, you can generate hierarchies of XML nodes. The XML generation is
controlled by the ALIAS of the column. In the example given below, a new node - is
created which holds "ItemNumber" and "Quantity". Note the slash ("/") used within the
column name, which really controls the XML generation.
*/
SELECT
OrderNumber AS 'orderNumber',
ItemNumber AS 'item/itemNumber',
Qty AS 'item/Quantity'
FROM #OrderDetails FOR XML PATH('orderInfo'), TYPE, ELEMENTS, ROOT('order')
/*
OUTPUT:
00001
A001
10
00001
A002
20
*/
-- Example 2: Generating a List of Values
/*
We just saw how the PATH keyword can be used to re-structure the XML
generated by FOR XML. Let us now look at a few other scenarios where
the PATH keyword is useful.
One of the situations, where the PATH keyword can come handy is to create a list
of values. The following query returns a list of Item Numbers from the Order table.
*/
SELECT
ItemNumber AS 'data()'
FROM
#OrderDetails
FOR XML PATH('')
/*
Output:
A001 A002 A003
*/
/*
You will note that the above query returns the results as a SPACE separated
value. Most of the times we work with COMMA separated values, not SPACE separated
values. So let us see how to generate a COMMA separated value list.
There is no direct way to do this. But we can do it with a small trick. Look at
the following example
*/
SELECT
',' + ItemNumber AS 'data()'
FROM
#OrderDetails
FOR XML PATH('')
/*
Output:
,A001 ,A002 ,A003
*/
/*
Well, we are almost done. But it is not really good still. You will note that the
string starts with a COMMA. Lets use the STUFF function to remove this. Here is the
final query.
*/
SELECT STUFF(
(SELECT
',' + ItemNumber AS 'data()'
FROM
#OrderDetails
FOR XML PATH('')),1,1,'')
/*
Output:
A001 ,A002 ,A003
*/