Reading XML Data Directly from SQL Server

by Neon Quach 20. November 2009 18:46

SQL Server 2000 introduced support for retrieving data in XML format using the FOR XML clause. The .NET SQL Server data provider SqlCommand object has an ExecuteXmlReader() that allows you to retrieve an XML value directly from SQL Server. The method returns an XmlReader , which contains the XML value that is the result of the SQL query. The ExecuteXmlReader() method returns a single XML value and can only be used with SQL statements that return XML data: SQL statements with a FOR XML clause, ntext or nvarchar data type fields containing valid XML, or XML data type fields.

Now, start with creating an example with your favorite ASP.NET C# or VB.NET programming.
Using or import some namespace below:

C#
using System;

using System.Data;

using System.Data.SqlClient;

using System.Xml;
VB
Imports System.Data

Imports System.Data.SqlClient

Imports System.Xml

In this example, I get CustomerID, CompanyName and ContactName from Customers table in Northwind database.

C#
string sqlConnectString = @"Data Source=.\sqlexpress;Initial Catalog=Northwind;Integrated Security=True;";

string sqlSelect = "select CustomerID,CompanyName,ContactName from customers for xml auto, XMLDATA";

DataSet ds = new DataSet();

using (SqlConnection connection = new SqlConnection(sqlConnectString))

{

// Create the command.

using (SqlCommand command = new SqlCommand(sqlSelect, connection))

{

// Read the XML data into a XML reader.

connection.Open();

using (XmlReader xr = command.ExecuteXmlReader())

{

// Read the data from the XML reader into the DataTable.

ds.ReadXml(xr, XmlReadMode.Fragment);

xr.Close();

}

foreach (DataRow rows in ds.Tables[0].Rows)

{

Response.Write(rows["CustomerID"].ToString() + "\t"

+ "" + rows["CompanyName"].ToString() + "" + "\t"

+ rows["ContactName"].ToString() + "");

}

}

VB.NET
Dim sqlConnectString As String = "Data Source=.\sqlexpress;Initial Catalog=Northwind;Integrated Security=True;"

Dim sqlSelect As String = "select CustomerID,CompanyName,ContactName from customers for xml auto, XMLDATA"

Dim ds As New DataSet()

Using connection As New SqlConnection(sqlConnectString)

' Create the command.

Using command As New SqlCommand(sqlSelect, connection)

' Read the XML data into a XML reader.

connection.Open()

Using xr As XmlReader = command.ExecuteXmlReader()

' Read the data from the XML reader into the DataTable.

ds.ReadXml(xr, XmlReadMode.Fragment)

xr.Close()

End Using

For Each rows As DataRow In ds.Tables(0).Rows

Response.Write(rows("CustomerID").ToString() + vbTab + "" + rows("CompanyName").ToString() + "" + vbTab + rows("ContactName").ToString() + "")

Next

Put above code on page load event, you will the result like:

ALFKI Alfreds Futterkiste Maria Anders
ANATR Ana Trujillo Emparedados y helados Ana Trujillo
ANTON Antonio Moreno Taquería Antonio Moreno
AROUT Around the Horn Thomas Hardy
…………………………….
AUTO: Returns query results in a simple, nested XML tree. Each table in the FROM clause for which at least one column is listed in the SELECT clause is represented as an XML element. The columns listed in the SELECT clause are mapped to the appropriate element attributes.

XMLDATA: Specifies that an inline XML-Data Reduced (XDR) schema should be returned. The schema is prepended to the document as an inline schema.
When System.Data.XmlReadMode is set to Fragment, which Reads XML fragments, such as those generated by executing FOR XML queries, against an instance of SQL Server. the default namespace is read as the inline schema.

For more information about the FOR XML clause, see Microsoft SQL Server Books Online.