XML is a simple and flexible system for defining data formats. This is completely platform independent and adopted everywhere for representing complex documents and data structures. For data transmission on web, its having significant contribution.
Now days it’s been the major use of XML to store both rowset (single table) and hierarchical (multiple-table) data in it.
Let’s take a look at how we can get xml data for a SQL Server table.
STEP 1: Our first step is to create a table (CustomerTable) with some columns
CREATE TABLE [CustomerTable] ( CustomerID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, CustomerName NVARCHAR(50) NOT NULL, UserName NVARCHAR(25), Password NVARCHAR(25) )
STEP 2: Now insert some data to the table rows
INSERT INTO CustomerTable(CustomerName, UserName, Password)VALUES('Deidra', '1209', '9902')
INSERT INTO CustomerTable(CustomerName, UserName, Password)VALUES('Rena', '1264', '9611')
INSERT INTO CustomerTable(CustomerName, UserName, Password)VALUES('Ann L', '1290', '9548')
INSERT INTO CustomerTable(CustomerName, UserName, Password)VALUES('Robert', '1302', '9083')
--Lets insert a row having some null data
INSERT INTO CustomerTable(CustomerName)VALUES('NoRecord')
Now our CustomerTable looks as illustrated below
| CustomerID | CustomerName | UserName | Password |
| 1 | Deidra | 1209 | 9902 |
| 2 | Rena | 1264 | 9611 |
| 3 | Ann L | 1290 | 9548 |
| 4 | Robert | 1302 | 9083 |
| 5 | NoRecord | NULL | NULL |
Step : 3 Using FOR XML
The FOR XML extension allows the creation of XML, from relational data. This clause does most of the work for us and it can be used in various ways.
So the basic syntaxes to get the the above table data in XML format are as below
SELECT * FROM CustomerTable FOR XML AUTO SELECT * FROM CustomerTable FOR XML RAW
“FOR XML AUTO”: Returns XML elements that are nested, based on which tables are listed in the “from” part of the query, and which fields are listed in the “select” part.