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 1 of a 4 part series. Other articles are listed below:
Contents
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:
RAW Mode
We will be using the below query to return customer information along with orders.
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 RAW mode, simply append FOR XML RAW 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 RAW
The above query returns:
As you can see, each record returned by the query was converted to an XML element "row" and columns into attributes with attribute values set to values returned by query.
To change the element name, append the desired element name after RAW keyword as below:
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 RAW('Customer')
The above query returns:
This changed each element's name from row to Customer.
To change attribute's name, simply provide alias to columns as below:
SELECT C.FIRST_NAME AS 'FirstName',
C.LAST_NAME AS 'LastName',
C.POSTAL_CODE AS 'PostalCode',
O.ORDER_ID AS 'OrderId',
O.TOTAL_ITEMS AS 'Items',
O.TOTAL_AMOUNT AS 'Amount'
FROM [CUSTOMER] C
INNER JOIN [ORDER] O
ON C.CUSTOMER_ID = O.CUSTOMER_ID
FOR XML RAW('Customer')
The above query returns data with new attribute names:
To nest each element inside a root element, append ROOT keyword as below:
SELECT C.FIRST_NAME AS 'FirstName',
C.LAST_NAME AS 'LastName',
C.POSTAL_CODE AS 'PostalCode',
O.ORDER_ID AS 'OrderId',
O.TOTAL_ITEMS AS 'Items',
O.TOTAL_AMOUNT AS 'Amount'
FROM [CUSTOMER] C
INNER JOIN [ORDER] O
ON C.CUSTOMER_ID = O.CUSTOMER_ID
FOR XML RAW('Customer'), ROOT
The data returned is now nested inside a parent element named "root".
To change root element's name, append the desired name after ROOT keyword as below:
SELECT C.FIRST_NAME AS 'FirstName',
C.LAST_NAME AS 'LastName',
C.POSTAL_CODE AS 'PostalCode',
O.ORDER_ID AS 'OrderId',
O.TOTAL_ITEMS AS 'Items',
O.TOTAL_AMOUNT AS 'Amount'
FROM [CUSTOMER] C
INNER JOIN [ORDER] O
ON C.CUSTOMER_ID = O.CUSTOMER_ID
FOR XML RAW('Customer'), ROOT('Customers')
The above query returns data with root element's name changed to "Customers".
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 C.FIRST_NAME AS 'FirstName',
C.LAST_NAME AS 'LastName',
C.POSTAL_CODE AS 'PostalCode',
O.ORDER_ID AS 'OrderId',
O.TOTAL_ITEMS AS 'Items',
O.TOTAL_AMOUNT AS 'Amount'
FROM [CUSTOMER] C
INNER JOIN [ORDER] O
ON C.CUSTOMER_ID = O.CUSTOMER_ID
FOR XML RAW('Customer'), ROOT('Customers'), 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 RAW mode in FOR XML clause. In the next article, we will learn about AUTO mode.
