How it Works

We begin our page with all the necessary namespaces importing we need to access our data, enabling us to open up our connection and executing our sql statement. Then, we create our dataset object, and fill it with the data from our datasource like so:

string strConnect = "server=(local);uid=sa;pwd=;database=pubs;";
SqlConnection objConnect = new SqlConnection(strConnect);
SqlDataAdapter objCommand = new SqlDataAdapter("SELECT * FROM authors order by au_lname asc", objConnect);

objConnect.Open();

DataSet objDS = new DataSet();

To digress for a moment, there are other methods for retrieving XML data from a database. SQL Server 2000 offers direct XML output, which can be used with Net's ExecuteXMLReader method. This sets up the XMLReader object that views the XML returned from SQL Server. At any rate, in this article we stick to getting XML from any database as typical data.

Moving on, we next setup the means of writing our results as XML. But before we do this we use the objDS.DataSetName method to set the name of our DataSet which it turn will be our XML document's parent node. You'll notice I've added some conditional file checking for our not yet created XML file. I simply have done this because I want to also show the ability of writing XML to a file as well as checking for one and reading an already existing one as well, thus:

if (!File.Exists(Server.MapPath("pubs.xml"))) {

and if not we write a new one directly to disk with the DataSet's WriteXML method.

objDS.WriteXml(Server.MapPath("pubs.xml"), XmlWriteMode.IgnoreSchema);

The XMLWriteMode.IgnoreSchema is telling the WriteXML method that no schema is to be written, since the XML structure isn't that constant over time, new schemas are generated each time automatically.

We then proceed to read it and transform it. The XMLDocument method below loads the XML file and holds it in memory. The XSL Transform method then retrieves your XSL Stylesheet and transforms the XML data into readable HTML.

XmlDocument XMLdoc = new XmlDocument();
XMLdoc.Load(Server.MapPath("pubs.xml"));

XslTransform XMLtrans = new XslTransform();
XMLtrans.Load(Server.MapPath("pubs.xsl"));

This will all become apparent as soon as the XML Web control's properties are assigned. The XML Web control's Document property sets up the XML document and the Transform property formats it using the specified XSL stylesheet, and out it goes.

xmloutput.Document = XMLdoc;
xmloutput.Transform = XMLtrans;

<asp:xml id="xmloutput" runat="server" />

Now isn't that too cool!