Introduction
A while ago, I was given a task to generate an XML file from database using stored procedure and was given a stored procedure as reference. The reference stored procedure fetched data into a cursor and then processed each record and created XML elements by appending string literals and then returned the generated string "XML". After looking at that stored procedure, I thought to myself that there must be a better way to do this and there was. Using FOR XML clause, one can return table records as XML data. After learning about it, I decided to write an article about it.
Article Series Roadmap
This is article 2 of 4 part series. Other articles are listed below:
Content
Background
Basic understanding of SQL Joins is required to follow along.
Using the Code
The article below will use the following database. You can copy/paste the following code to follow along or create your own database and tweak the queries.
Create database as shown below:
CREATE DATABASE FOR_XML_TUTORIAL;
Execute the below statements to create CUSTOMER and ORDER tables and populate it with data.
USE [FOR_XML_TUTORIAL];
CREATE TABLE [CUSTOMER]
(
CUSTOMER_ID INT PRIMARY KEY NOT NULL,
FIRST_NAME VARCHAR(25) NOT NULL,
LAST_NAME VARCHAR(25) NOT NULL,
POSTAL_CODE VARCHAR(2) NOT NULL,
);
CREATE TABLE [ORDER]
(
ORDER_ID INT PRIMARY KEY NOT NULL,
CUSTOMER_ID INT NOT NULL REFERENCES CUSTOMER(CUSTOMER_ID),
TOTAL_ITEMS INT NOT NULL,
TOTAL_AMOUNT NUMERIC(18,2) NOT NULL
);
INSERT INTO CUSTOMER VALUES (1, 'John', 'Michaels', 'TX');
INSERT INTO CUSTOMER VALUES (2, 'Shawn', 'Cena', 'MA');
INSERT INTO CUSTOMER VALUES (3, 'Dwayne', 'Austin', 'TX');
INSERT INTO CUSTOMER VALUES (4, 'Steve', 'Johnson', 'FL');
INSERT INTO [ORDER] VALUES (1, 1, 5, 32.50);
INSERT INTO [ORDER] VALUES (2, 1, 2, 21.36);
INSERT INTO [ORDER] VALUES (3, 2, 7, 59.00);
INSERT INTO [ORDER] VALUES (4, 3, 2, 18.24);
INSERT INTO [ORDER] VALUES (5, 4, 3, 30.00);
INSERT INTO [ORDER] VALUES (6, 4, 6, 66.00);
FOR XML Modes
When using FOR XML clause, a mode must be specified which returns XML accordingly. Following is a list of available modes:
AUTO Mode
AUTO mode in FOR XML clause is used when the XML required contains multi level nesting.
We will be using the below query to return customer information along with orders for demonstration.
SELECT C.FIRST_NAME,
C.LAST_NAME,
C.POSTAL_CODE,
O.ORDER_ID,
O.TOTAL_ITEMS,
O.TOTAL_AMOUNT
FROM [CUSTOMER] C
INNER JOIN [ORDER] O
ON C.CUSTOMER_ID = O.CUSTOMER_ID
The query returns:
To return XML data in AUTO mode, append FOR XML AUTO in the above query.
SELECT C.FIRST_NAME,
C.LAST_NAME,
C.POSTAL_CODE,
O.ORDER_ID,
O.TOTAL_ITEMS,
O.TOTAL_AMOUNT
FROM [CUSTOMER] C
INNER JOIN [ORDER] O
ON C.CUSTOMER_ID = O.CUSTOMER_ID
FOR XML AUTO
The above query returns:
As you can see, the returned XML contains 2 element types C and O. These elements are named as such because of the aliases provided to table names in query. Also notice the elements in XML have a parent child relation. Parent or outer element is C containing customer information as attribute/value pair and child or inner element is O containing order information as attribute/value pair. This relation ordering is determined by the first column in select query. In the above query, first column is from Customer table, therefore Customer became parent element. To reverse this order, simply put any column from Orders table in first position in select clause as shown below:
SELECT O.ORDER_ID,
C.FIRST_NAME,
C.LAST_NAME,
C.POSTAL_CODE,
O.TOTAL_ITEMS,
O.TOTAL_AMOUNT
FROM [CUSTOMER] C
INNER JOIN [ORDER] O
ON C.CUSTOMER_ID = O.CUSTOMER_ID
FOR XML AUTO
The above query returns:
As mentioned above, now O elements containing orders information is parent element and C element containing customer information is child.
To change elements and attributes name, simply provide meaningful aliases in the above query.
SELECT Customers.FIRST_NAME,
Customers.LAST_NAME,
Customers.POSTAL_CODE,
Orders.ORDER_ID,
Orders.TOTAL_ITEMS,
Orders.TOTAL_AMOUNT
FROM [CUSTOMER] Customers
INNER JOIN [ORDER] Orders
ON Customers.CUSTOMER_ID = Orders.CUSTOMER_ID
FOR XML AUTO
The above query returns:
To nest the returned XML inside root element, append ROOT keyword with desired root element name in parenthesis as shown below:
SELECT Customers.FIRST_NAME,
Customers.LAST_NAME,
Customers.POSTAL_CODE,
Orders.ORDER_ID,
Orders.TOTAL_ITEMS,
Orders.TOTAL_AMOUNT
FROM [CUSTOMER] Customers
INNER JOIN [ORDER] Orders
ON Customers.CUSTOMER_ID = Orders.CUSTOMER_ID
FOR XML AUTO, ROOT('CustomerList')
The above query returns:
Till now, all the queries we executed returned XML data in a format in which each column was converted to an attribute. To change this format and return XML in which each column is mapped to its own element, append ELEMENTS keyword as below:
SELECT Customers.FIRST_NAME,
Customers.LAST_NAME,
Customers.POSTAL_CODE,
Orders.ORDER_ID,
Orders.TOTAL_ITEMS,
Orders.TOTAL_AMOUNT
FROM [CUSTOMER] Customers
INNER JOIN [ORDER] Orders
ON Customers.CUSTOMER_ID = Orders.CUSTOMER_ID
FOR XML AUTO, ROOT('CustomerList'), ELEMENTS
Now each record's attribute is converted into an element with its name set to its alias and value set to the value returned by query.
Note: I reduced the number of rows to make the below image smaller.
Wrapping Up
That is all for AUTO mode in FOR XML clause. In the next article, we will learn about EXPLICIT mode.
