Usage of SQLXML to Filter, Retrieve & Present data

• Download demo - 3.98 Kb
• Download source - 6.87 Kb

Introduction

Microsoft SQLXML is used in this application to access XML data from an instance of Microsoft SQL Server, and the data is processed in the .NET environment, and presented in the desired format using the XSL stylesheets. The main purpose of illustrating this application is to exhibit the usage of managed classes in the namespace Microsoft.Data.SqlXml to get the data in the form of XML from Microsoft SQL Server™ 2000 and use the appropriate XSL to present the same.

Using the code

The Sql Server 2000 can return the data in the form of XML using the features FOR XML AUTO, RAW & EXPLICIT. We are using FOR XML Clause to demonstrate the application. The most efficient way to retrieve the XML data is using the managed classes of SQLXML. The application, uses the SqlXmlCommand object methods like ExecuteStream, etc, to retrieve the XML data from the Sql Server 2000 in the form of stream. The application uses the the Northwind database to get the employee details. Let us say, we have a requirement of getting the employee details woking in the given country 'UK' with the list of Territories. So, we have a sql query that gets the employee details in the XML format using the FOR XML Clause.

The SQL Query that is specified in the config file (App.config) looks like this:

SELECT   
       [Emp].[EmployeeID] AS EmpID, 
       [Emp].[FirstName] AS FNAME, 
       [Emp].[LastName] AS LNAME, 
       [TerritoryDescription] AS TerDesc  
FROM 
       [Employees] [Emp]
LEFT OUTER JOIN
       [EmployeeTerritories] [EmpTer]
ON
       [EmpTer].[EmployeeID] = [Emp].[EmployeeID]
LEFT OUTER JOIN
       [Territories] [Ter]
ON
       [EmpTer].[TerritoryID] = [Ter].[TerritoryID]
WHERE
       [Emp].[Country] = ? 
FOR XML AUTO    

The connection string, the XSL file name, target HTML file name and the sql query are available and part of the config file. A StreamReader object is used to read the XML data retrieved from the Sql Server 2000 to a string. The SqlXmlParameter uses the method CreateParameter to create a parameter for filtering data by the specified country 'UK'. The XML retrieved from the database doesn't have a root. So, a root tag is appeneded explicitly to the retrieved string.

The C# code snippet that retrieves the XML data as a stream & converts it to a string:

#region Get the data using FOR XML CLAUSE
// Get the connection string
strConnString = ConfigurationSettings.AppSettings["DBConnString"];
// Create a new SqlXmlCommand object 
xmlCmd = new SqlXmlCommand(strConnString);      
// Set the text of the command 
xmlCmd.CommandText = ConfigurationSettings.AppSettings["GetEmpDetails"];
// Create a new xmlParam object 
xmlParam = xmlCmd.CreateParameter();
// Assign the value for the parameter defined
xmlParam.Value = "UK";
// Execute the command to retrieve the xml from sql in the form of stream
strmObject = xmlCmd.ExecuteStream();
// Set the position of the stream object to 0
strmObject.Position = 0;
strResult = "<ROOT>"; 
// Assign the stream to the StreamReader
using(StreamReader srdrObject = new StreamReader(strmObject))
{
  strResult += srdrObject.ReadToEnd();
}
strResult += "</ROOT>"; 
#endregion

The XSLT engine is used to transform the data to a desired output. The XslTransform object is then created to which the stylesheet XSL file is loaded. A XmlTextWriter object is used to give output in a HTML file. The Transform method of the XslTransform object is used to transform the data and send the output to a html file specified. Once the output file is created an entry is made in the EventLog. The exception that occurs in the In case of any exception the same

The C# code snippet that transforms the XML data and outputs it to a HTML file:

#region Load the xml & xsl & Transform the same
// Create a new XmlDocument object 
objXmlDoc = new XmlDocument();
// Load XML to the xml document
objXmlDoc.LoadXml(strResult);
// Get the XML file name
strXMLFileName = ConfigurationSettings.AppSettings["SourceXMLFileName"];
// Get the XSL file name
strXSLFileName = ConfigurationSettings.AppSettings["SourceXSLFileName"];
// Get the HTML file name
strHTMLFileName = ConfigurationSettings.AppSettings["TargetHTMLFileName"];
// Save the XML file
objXmlDoc.Save(strXMLFileName);
//
//Create a new XPathDocument and load the XML data to be transformed.
objPathDoc = new XPathDocument(strXMLFileName);
//
objTransform = new XslTransform();
objTransform.Load(strXSLFileName);
//
//Create an XmlTextWriter which outputs to a html file.
writer = new XmlTextWriter(strHTMLFileName,null);
//
//Transform the data and send the output to a html file.
objTransform.Transform(objPathDoc, null, writer);
//
// Make an entry in the Eventlog - Successful
EventLog.WriteEntry("ForXMLSupport", strHTMLFileName + 
    " file is created successfully", EventLogEntryType.Information);
#endregion

The XSL code that styles the data is specified here:

<xsl:for-each select="ROOT/Emp">
<xsl:variable name="EmpName"><xsl:value-of select="./@FNAME"/> 
<xsl:value-of select="./@LNAME"/></xsl:variable>
<xsl:variable name="EmpFirstName">
<xsl:value-of select="./@FNAME"/></xsl:variable>
<table border="1" width="100%" bgcolor="chocalate">
<tr>
<td><b>Employee ID : </b><xsl:value-of select="./@EmpID"/></td>
<td><b>Name : </b><xsl:value-of select="$EmpName"/></td>
</tr>
<tr>
<tr>
<td colspan="2"><b>Territories managed by <i>
<xsl:value-of select="$EmpFirstName"/></i>  : </b></td>
</tr>
<table border="1" width="100%" bgcolor="lightblue">
<xsl:for-each select="./Ter">
<tr>
<td><xsl:value-of select="./@TerDesc"/></td>
</tr>
</xsl:for-each>
</table>
</tr>        
</table>
</xsl:for-each>

Output File:

In Summary

The objective of presenting this article is to give a basic idea of using the Microsoft SqlXml Managed Classes to retrieve the XML data from the Microsoft Sql Server 2000 and to process the same and present it in the desired format.