C#.Net- How to Convert SQL to XML String and render output using XslCompiledTransform in Asp.net

Here I will explain how to convert datatable to xml string in asp.net using C#

Code for faching data from sql database

using (SqlConnection objcon = new SqlConnection(ConfigurationManager.ConnectionStrings[“TestDB”].ConnectionString))
{
objcon.Open();
SqlDataAdapter da = new SqlDataAdapter(“select * from Emp_Info”, objcon);
DataTable dt = new DataTable(“empInfo”);
da.Fill(dt);

using (TextWriter writer = new StringWriter())
{
dt.WriteXml(writer);
Response.Write(new XMLHelper().GetValue(@”C:\Tushar\SQLToXMLExample\XSLT\Demo.xslt”, writer.ToString()));
}
}
XMLHelper that generate Output stringĀ using XSLT file and XML String

public class XMLHelper
{
public XMLHelper()
{ }
public string GetValue(string templatePath, string xmlString)
{
XDocument xmlObj = XDocument.Parse(xmlString);
XDocument result = GetResultXml(templatePath, xmlObj);

return (result == null) ? string.Empty : result.Document.ToString();
}
public XDocument GetResultXml(string templatePath, XDocument xmlObj)
{
XDocument result = new XDocument();
using (XmlWriter writer = result.CreateWriter())
{
XslCompiledTransform xslt = new XslCompiledTransform();
xslt.Load(templatePath);
xslt.Transform(xmlObj.CreateReader(), writer);
}
return result;
}
}

XSLT File — Sample Demo

<?xml version=”1.0″ encoding=”utf-8″?>
<xsl:stylesheet version=”1.0″ xmlns:xsl=”http://www.w3.org/1999/XSL/Transform&#8221;
xmlns:msxsl=”urn:schemas-microsoft-com:xslt” exclude-result-prefixes=”msxsl”
>
<xsl:output method=”html” omit-xml-declaration=”yes” indent=”yes”/>

<xsl:template match=”@* | node()”>
<html>
<body>
<table>
<tr>
<xsl:for-each select=”/*/node()”>
<xsl:if test=”position()=1″>
<xsl:for-each select=”*”>
<td>
<xsl:value-of select=”local-name()”/>
</td>
</xsl:for-each>
</xsl:if>
</xsl:for-each>
</tr>
<xsl:for-each select=”*”>
<tr>
<xsl:for-each select=”*”>
<td>
<xsl:value-of select=”.”/>
</td>
</xsl:for-each>
</tr>
</xsl:for-each>
</table>
</body>
</html>
</xsl:template>
</xsl:stylesheet>

Advertisements