Click here to Skip to main content
14,032,830 members
Click here to Skip to main content
Add your own
alternative version

Stats

6K views
5 bookmarked
Posted 29 May 2017
Licenced CPOL

FOR XML Basics (AUTO Mode): 2 of 4

, 2 Jun 2017
Rate this:
Please Sign up or sign in to vote.
A brief introduction on how to use FOR XML clause in AUTO mode in Microsoft SQL Server to return data in XML format

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:

  • RAW
  • AUTO
  • EXPLICIT
  • PATH

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

AhsanAhmed
Software Developer
Pakistan Pakistan
Software Engineer and a clean code proponent, working on various .NET, Oracle and front end technologies including Web Forms, MVC, Web API, LINQ, EF, T-SQL, PL/SQL, SSIS, JavaScript, jQuery.

You may also be interested in...

Comments and Discussions

 
-- There are no messages in this forum --
Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web01 | 2.8.190423.1 | Last Updated 2 Jun 2017
Article Copyright 2017 by AhsanAhmed
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid