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.
Here are a couple of techniques that can fulfill our need:
• Using XMLDataDocument
• Using XMLDocument
I will provide VB.Net samples.
SOLUTION 1: (Using SQLDataAdapter)
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
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.
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”.
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.