Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

XML Workshop XV - Accessing FOR XML results with ADO.NET

By Jacob Sebastian,

Introduction

In a few of the early sessions of the XML Workshop, we have seen how to generate XML results from TSQL. We have examined the FOR XML clause and have seen the usages of RAW, AUTO, PATH and EXPLICIT.

XML Workshop 1 - Explains FOR XML with AUTO and RAW

XML Workshop 3 - Explains FOR XML with PATH

XML Workshop 4 - Explains FOR XML with EXPLICIT

 

Accessing results of FOR XML from a .NET application

Most of the times the results of FOR XML queries are expected to be consumed by client applications. In this session, let us see how to access the results of FOR XML queries from VB.NET and C#.NET applications using ADO.NET.

Sample Table and Stored Procedure

Let us create a sample table and populate it with some data. Let us then create a stored procedure which generates an XML document using FOR XML.

-- Let us create a new database

CREATE DATABASE XmlTest

GO

 

-- Let us now create a table for our example

USE XmlTest

GO

 

CREATE TABLE Employee (EmpID INT IDENTITY(1,1), EmpName VARCHAR(50) )

GO

 

-- let us insert some data

INSERT INTO Employee (EmpName)

SELECT 'Jacob' UNION ALL

SELECT 'Michael' UNION ALL

SELECT 'Richard'

Let us now create a stored procedure which generates the XML document that we need.

CREATE PROCEDURE GetEmployeeData

AS

 

SELECT * FROM Employee

FOR XML AUTO, ROOT('Employees')

Here is the result of the stored procedure.

<Employees>

  <Employee EmpID="1" EmpName="Jacob" />

  <Employee EmpID="2" EmpName="Michael" />

  <Employee EmpID="3" EmpName="Richard" />

</Employees>

VB.NET Sample Code

Let us create a VB.NET console application which executes the above stored procedure and fetches the XML document.  Here is the code which does that.

' references

Imports System.Data.SqlClient

Imports System.Xml

Imports System.Text

Module Module1

 

    Sub Main()

        'let us make a connection first

        Dim str As String

        str = "Data Source=TOSHIBA-USER\SQL2005;Initial Catalog=XmlTest;"

        str = str + "Persist Security Info=True;User ID=sa;Password=sa2005"

        Dim cn As New SqlConnection(str)

        cn.Open()

 

        'Let us make a command

        Dim cmd As New SqlCommand()

        cmd.Connection = cn

        cmd.CommandText = "GetEmployeeData"

        cmd.CommandType = CommandType.StoredProcedure

 

        'What we need next is an XMLReader and call

        'ExecuteXMLReader method of SqlCommand.

        Dim r As XmlReader

        r = cmd.ExecuteXmlReader()

 

        'Read the data from XMLReader and Load into

        'the String Builder

        Dim xmlData As New StringBuilder

        Do While r.Read()

            xmlData.Append(r.ReadOuterXml())

        Loop

 

        'Print the output

        Console.WriteLine(xmlData.ToString)

 

        'Close the Reader and DB Connection

        r.Close()

        cn.Close()

 

    End Sub

 

End Module

C#.NET Sample Code

Now, let us write the C#.NET version of the above code.

using System;

using System.Data.SqlClient;

using System.Data;

using System.Xml;

using System.Text;

 

namespace ConsoleApplication2

{

    class Program

    {

        static void Main(string[] args)

        {

 

            //let us make a connection first

            String str;

            str = "Data Source=TOSHIBA-USER\\SQL2005;Initial Catalog=XmlTest;";

            str = str + "Persist Security Info=True;User ID=sa;Password=sa2005";

            SqlConnection cn = new SqlConnection(str);

            cn.Open();

 

            //Let us make a command

            SqlCommand cmd = new SqlCommand();

            cmd.Connection = cn;

            cmd.CommandText = "GetEmployeeData";

            cmd.CommandType = CommandType.StoredProcedure;

 

            //What we need next is an XMLReader and call

            //ExecuteXMLReader method of SqlCommand.

            XmlReader r;

            r = cmd.ExecuteXmlReader();

 

            //Read the data from XMLReader and Load into

            //the String Builder

            StringBuilder xmlData = new StringBuilder();

            while (r.Read())

            {

                xmlData.Append(r.ReadOuterXml());

            }

 

            //Print the output

            Console.WriteLine(xmlData.ToString());

 

            //Close the Reader and DB Connection

            r.Close();

            cn.Close();

 

        }

    }

}

Conclusions

This article presents an example of accessing FOR XML results from a .NET application. I guess there must be other ways of doing this too. This sample code is created for the purpose of demonstrating the basic usage. The sample applications are tested with the sample data and found to be working correctly. However, please note that the code is not highly optimized. You might need slightly different code for a production level application. For example, you might need to dispose() the database connection after reading the information. I leave that to the .NET developer in you.

Total article views: 4685 | Views in the last 30 days: 7
 
Related Articles
FORUM

create Procedure within Procedure

create Procedure within Procedure

BLOG

Service Broker sample application from SQLskills Immersion Event

You can find my Service Broker sample application which I presented during the SQLskills Immersion ...

FORUM

Database Create from Application (VB & C#)

Creating Database from an Front End Application

SCRIPT

Create Sample Data Script

in this scripts there are two methods of creating sample data for a gambling application. i have us...

ARTICLE

Review: Loading Sample Data with DataTect 1.6

So, you've created a database and application and want to see how it operates with a substantial loa...

Tags
ado.net    
xml    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones