Click here to Skip to main content
15,937,447 members
Articles / Web Development / ASP.NET

Save an XML File to Database and Send an Email using XSLT

Rate me:
Please Sign up or sign in to vote.
3.40/5 (13 votes)
20 May 2009CPOL4 min read 122.5K   1.8K   87   8
Retrieve data from a webform into an XML file, save data to database via OPENXML and send and transform XML to HTML via XSLT


It is a common requirement in a website to have the ability to send an email to the organisation via a contact form. Depending on the organisation, they may need this information saved to a database or sent directly to a pre defined email account where this information will be processed. This article will illustrate the basics of retrieving the information from the contact page to an XML file, using the XML file to perform an insert into a database table, and then transform the XML to the required format for sending to a specified account.


By performing XSL transformations on XML documents, one can transform the document to any format one would require the information in. There are many excellent resources to assist you in learning XSLT, therefore I will not be going into much depth on that in this article. I have also only included rudimentary basics in the source of this application. The included XSL file is not a perfect example of XSL template, just barely enough to give an idea of how to use it. Using the information contained in this article, one can easily build upon it.

Using the Code

In the attached source code, I have included a very simple database, SQL 2000, consisting of one table tblContact, and one Stored Procedure spAddContact. The application is a very simple application consisting of just two pages built in ASP.NET 2.0. I make use of several .NET namespaces. The most notable for the purpose of this article are:

  • System.Data.SqlClient
  • System.IO
  • System.Xml
  • System.Xml.Xsl
  • System.Net.Mail
  • System.Text
  • System.IO

Step 1

When the send button is clicked, we create an XML file. In the example, I achieve this by creating an in memory XML file, then iterate through the Pages control collection and then depending on the controls type, I append the data to the XML File.


As force of habit, when I add controls to a page I carried over the habit by adding the three letters of the controls type, i.e. DropDownList = ddl.

When iterating through the control collection and creating the XML File I take off the first three letters of the control type by performing a substring(3) on the control ID and then use the remaining portion of the name to create the name of the XML tag, and then place whatever value of the control into the tag.

private XmlDocument GetEmailDocument()
       // We create an xmlDocument with a root node
       XmlDocument xmlDoc = new XmlDocument();
       XmlNode xmlnode;
       xmlnode = xmlDoc.CreateElement("Root");
       GetFormData(pnlEmail, xmlDoc);
       return xmlDoc;

A very basic XML file is created. The GetFormData method is a method that iterates through the page control collection. The GetFormData method checks to see if a control is able to contain more controls, if this is possible then we recursively iterate through that control. This is an example of how to use recursion, a programming concept that is often neglected.

protected void GetFormData(Control Parent, XmlDocument xmlDoc)
        XmlNode xmlnode;
        foreach (Control wc in Parent.Controls)
        /* Depending on which version of the Framework you are using
       One can choose which version of this line of code you want to use
        for 2.0 You can use if(wc.HasControls())GetFormData(wc, xmlDoc);
        Or alternatively for 1.1 you can count the controls as shown below */

    if (wc.Controls.Count > 0) GetFormData(wc, xmlDoc);
            switch (wc.GetType().FullName.ToString())
                case "System.Web.UI.WebControls.TextBox":
                    TextBox txt = (TextBox)wc;
                    xmlnode = xmlDoc.CreateElement(txt.ID.Substring(3).ToString());
                    xmlnode.InnerText = Server.HtmlEncode(txt.Text);
                case "System.Web.UI.WebControls.DropDownList":
                    DropDownList ddl = (DropDownList)wc;
                    xmlnode = xmlDoc.CreateElement(ddl.ID.Substring(3).ToString());
                    xmlnode.InnerText = ddl.SelectedValue.ToString();

Step 2

Once the data has been collected to the XML file, we will save the data to the database. I demonstrate how to pass an XML file to SQL Server 2000. The C# code to perform this is nothing out of the ordinary here other than I pass the XML document through to SQL as SQL Text. You could pass this parameter through as either Char, Varchar, nVarchar, or nText. It makes no difference other than limitation on the size of the document you wish to pass through I have chosen to pass it through as Text purely out of habit (It could be a bad one :-))

          oComm.Connection = oConn;
          oComm.CommandType = CommandType.StoredProcedure;
          oComm.CommandText = "spAddEmailContact";
          oComm.Parameters.Add("@xmlDoc",SqlDbType.Text).Value =
         string sRes= oComm.ExecuteScalar().ToString();

Step 3

The real logic to saving this file is processed inside the SQL Stored Proc. There is a lot that is actually going on in this stored proc. First of all, we need to convert the text passed in the Incoming Parameter to an In Memory XML file. We achieve this by using the system stored Procedure sp_xml_preparedocument and pass it @xmlDoc as an input, and @DocHandle as an output which returns a reference to the XMLDocument. Then we start what looks like an ordinary insert statement. We may be accepting some empty strings in our XMl file. We don't want to save an empty string so we just want to save it as null. We now come to the more slightly complicated part of the stored proc, the OPENXML where we pass in the Dochandle we got earlier and then the start node we would like to start from. We then supply a value of 2 means to access the XPATH as elements.

ALTER   PROCEDURE dbo.spAddEmailContact

        @xmlDoc text
    DECLARE @DocHandle int
    EXEC sp_xml_preparedocument @DocHandle OUTPUT, @xmlDoc

INSERT INTO tblContact
(FirstName, LastName , Email,Street, Town, Postcode,subject, Comment )
   a.Lastname ,,
   CASE LTRIM(RTRIM(a.Street))
      WHEN '' THEN NULL 
       ELSE a.Street
    END ,
    CASE LTRIM(RTRIM(a.Postcode))
    ELSE a.Postcode
    a.subject, a.Comment
FROM OPENXML(@DocHandle, '/Root',2) WITH (FirstName varchar(50), _
	Lastname varchar(50) ,  Email varchar(50), Street varchar(50),
Town varchar(50), Postcode varchar(50),  Subject varchar(50), _
	Comment varchar(250) ) AS a

EXEC sp_xml_removedocument @DocHandle

 Select @@identity

Step 4

We now do XML/XSLT transformation and send the email. The XSLT file that we will be using in the transformation is kept in the App_Data folder of the application. We retrieve it and make use of the XSLCompiledTransform to transform the XML into an HTML file that we will send as the body of the email.

private void SendEmail(XmlDocument xmlDoc)
        System.Text.StringBuilder sb = new System.Text.StringBuilder();
        XslCompiledTransform xslt = new XslCompiledTransform();
        using (XmlTextWriter xtw = new XmlTextWriter(new StringWriter(sb)))
            xslt.Transform(xmlDoc, xtw);
           SmtpClient oMail = new SmtpClient();
           MailMessage msg = new MailMessage();
                MailAddress Madd = new MailAddress(txtEmail.Text, txtFirstName.Text);
                oMail.Host = "localhost";
                oMail.Port = 25;
                msg.From = Madd;
                msg.Subject = ddlSubject.SelectedValue.ToString();
                msg.IsBodyHtml = true;
                msg.Body = sb.ToString();
            catch (Exception ex)
                //This step is not good practice but seen as though this is just a Demo
                //app I thought I would place this here so we can see if any errors occur
                lblFail.Visible = true;
                lblFail.Text = "An Error occurred:" + ex.Message;


  • 14-09-06 First upload with intention to further update the article later


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Web Developer Three Nine Consulting
United Kingdom United Kingdom
Gary Woodfine is a freelance software developer based in Swindon, Wiltshire, UK. Experienced in Financial Services, Security and Utilities covering all areas of software development including Solution Architecture, software design and product development. Expertise in full stack software development.

Comments and Discussions

GeneralPossible Memory Leak Pin
Dexter Legaspi21-May-09 3:56
Dexter Legaspi21-May-09 3:56 
GeneralRe: Possible Memory Leak Pin
GaryWoodfine 21-May-09 10:26
professionalGaryWoodfine 21-May-09 10:26 
Generalxml Pin
chanchalmajumdar115-Jul-08 21:06
chanchalmajumdar115-Jul-08 21:06 
GeneralI want to update the Article Pin
GaryWoodfine 18-Apr-07 5:25
professionalGaryWoodfine 18-Apr-07 5:25 
GeneralDid anyone find this article helpful or do you have some criticism Pin
GaryWoodfine 11-Oct-06 5:28
professionalGaryWoodfine 11-Oct-06 5:28 
GeneralRe: Did anyone find this article helpful or do you have some criticism Pin
sides_dale27-Oct-06 20:27
sides_dale27-Oct-06 20:27 
GeneralRe: Did anyone find this article helpful or do you have some criticism Pin
GaryWoodfine 28-Oct-06 5:33
professionalGaryWoodfine 28-Oct-06 5:33 
GeneralRe: Did anyone find this article helpful or do you have some criticism Pin
sides_dale28-Oct-06 16:03
sides_dale28-Oct-06 16:03 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.