Converting Datatable to xml string and viceversa

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.

It is very common to use dataset in .NET applications. A dataset may contain a single table or multiple tables that are related though primery and foreign keys. We can also use XML to completely reconstruct the dataset. 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 have a look how we can get xml data for a sql server table.

STEP 1 : Our first step is to create a table having some columns. Name the table as CustomerTable.

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.

Now our CustomerTable looks as illustrated below

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')

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.