//
You're reading...
SQL, VB.Net, XML

For XML Path in .Net – Ups and Downs

INTRODUCTION:

We often write ‘FOR XML PATH’ queries in SQL to get data in XML form. Also we can extract unlimited data from a table via XML queries. The problem I am going to discuss today is how to read FULL contents of “FOR XML Path” sql query. I will describe various techniques to retrieve data, step by step.

A simple FOR XML Path query is something like this:

SELECT * from DoctorsTable FOR XML path

At first look it seems to be the case of ExecuteScalar() method where we can get the first cell of the first row.

But once we do so, we realized that incomplete result set is obtained…. Oooops!

But from now, there is nothing to worry about how to get rid of this issue.

THE RESOLUTION:

Here are a couple of techniques that can fulfill our need:

    Using SQLDataAdapter
    • Using XMLDataDocument
    • Using XMLDocument
    Using SQLDataReader
    Using XMLReader

I will provide VB.Net samples.

SOLUTION 1: (Using SQLDataAdapter)

Using XMLDataDocument:


Dim ds As New DataSet()
Dim sqlCmd As String = "Select * from DoctorsTable for XML path"
ds.Tables.Add(SqlHandler.FillDataTable(sqlCmd))
Dim xDoc As New Xml.XmlDataDocument(ds)
Dim xmlData As String = xDoc.InnerText
TextBox1.Text = xmlData

Here FillDataTable() uses SqlDataAdapter to fill datatable.

So you have a solution which is working now ?

Ooooops! Compiler generated a warning……!

According to XMLDataDocument documentation, this API get obsolete in framework 4 or later. So what if the framework is above or equal to 4? Is there any equivalent ?

Yes, have a look at the rest of the materials.

Using XMLDocument:

Here is how XMLDocument works:


Dim xDocument As New XmlDocument()
Dim ms As New IO.MemoryStream()
Dim sqlCmd As String = "Select * from DoctorsTable for XML path "
Dim dt As DataTable = SqlHandler.FillDataTable(sqlCmd)
dt.WriteXml(ms, XmlWriteMode.IgnoreSchema, False)
xDocument.LoadXml(System.Text.Encoding.ASCII.GetString(ms.GetBuffer()))
Dim XDocData As String = xDocument.InnerXml.Replace("&", "&").Replace(">", ">").Replace("<", "<").Replace(""", """")

We first fill Datatable with records. Uses WriteXML() method to get its xml equivalent into a temporary buffer. The serialized data will be the desired result. The additional step we need to perform in this case is to call Replace() methods.

I have also discussed a related issue with this solution.

SOLUTION 2: (Using SqlDataReader)

Here is the work around for SQLDataReader:


Dim reader As SqlClient.SqlDataReader
Dim sqlCmd As String = "Select * from DoctorsTable for XML path"
reader = SqlHandler.ExecuteReader(sqlCmd)
Dim data As String = String.Empty
If (reader IsNot Nothing) Then
      While reader.Read
            data &= reader(0)
      End While
      If (reader IsNot Nothing) Then
            reader.Close()
      End If
End If

We have created a SqlDataReader object, that holds the xml data stream coming from SQL. Each time we read the stream from its current position and collect data in a variable.

Here ExecuteReader() uses SqlCommand.ExecuteReader() method to create a SqlDataReader.

SOLUTION 3: (Using XmlReader)

We have written a method ExecuteXMLReader() which uses SqlCommand.ExecuteXmlReader(), that prepares a XmlReader in response to the FOR XML query. Here we have two possiblities to read this stream of data.

Either we can iterate over the stream or uses XmlDocument to load XMLReader object. I have demonstrate XmlDocument approach.


Dim sqlCmd As String = " SELECT * from DoctorsTable FOR XML path ('Doctor'), Root('Doctors')"
Dim rdr As XmlReader = SqlHandler.ExecuteXMLReader(sqlCmd)
If (rdr IsNot Nothing) Then
      rdr.Read()
      Dim doc As New XmlDocument()
      doc.Load(rdr)
      doc.Save("abc.xml")  ' save data in current domain
      TextBox1.Text = IO.File.ReadAllText("abc.xml")  ' Reload saved xml data
      If (rdr IsNot Nothing) Then
  	   rdr.Close()
      End If
End If

Notice that we have changed query this time, i.e., we have changed this part:

FOR XML path ('Doctor'), Root('Doctors')

I have discussed a related issue that, why there is a need to change query. This suffix will assign a name to the root node and each entry that comes under selection, which will solve our problem of getting duplicate name of the node “DocumentElement”.

Conclusion:

We know that there are several possible solution to this problem, but my intention is to provide viable solutions to this problem along with their pros and cons. I hope you will find this really helpful.

Your feedback and suggestion is welcome.

Regards,
Shahan

Advertisements

About Shahan Ayyub

Shahan is a Microsoft Certified Professional of .Net. Mostly self taught programmer and a quick learner, who just love to code and always eager to find unique solutions to the common problems. Currently working as an iOS developer.

Discussion

2 thoughts on “For XML Path in .Net – Ups and Downs

  1. What is ‘SqlHandler’?

    Posted by Anon Ymous | October 8, 2012, 9:59 pm
    • “SqlHandler” is a class written at DataAccessLayer. The purpose of this class is to just fetch data from Sql. You can use SqlCommand, SqlDataAdapter,SqlConnection classes to develop equivalent functionality and can call ExecuteReader(), ExecuteXmlReader, SqlDataAdapter.Fill() methods. Have you seen the links in each solution ? They points to MSDN samples.

      Posted by Shahan Ayyub | October 9, 2012, 6:23 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: