Storing XSLT in SQL Server 2005 with xml type?

Question

I have a lot of XSL files in my ASP.NET web app. A lot. I generate a bunch of AJAX HTML responses using this kind of generic transform method:

public void Transform(XmlDocument xml, string xslPath) 
{
    ...
    XslTransform myXslTrans = new XslTransform();
    myXslTrans.Load(xslPath);
    myXslTrans.Transform(xml,null, HttpContext.Current.Response.Output);        
}

I'd like to move the XSL definitions into SQL Server, using a column of type xml. I would store an entire XSL file in a single row in SQL, and each XSL is self-contained (no imports). I would read out the XSL definition from SQL into my XslTransform object.

Something like this:

public void Transform(XmlDocument xml, string xslKey) 
{
    ...

    SqlCommand cmd = new SqlCommand("GetXslDefinition");
    cmd.AddParameter("@xslKey", SqlDbType.VarChar).Value = xslKey;
    // where the result set has a single column of XSL: "<xslt:stylesheet>..." 
    ...

    SqlDataReader dr = cmd.ExecuteReader();
    if(dr.Read()) {
        SqlXml xsl = dr.GetSqlXml(0);
        XslTransform myXslTrans = new XslTransform();
        myXslTrans.Load(xsl.CreateReader());
        myXslTrans.Transform(xml,null, HttpContext.Current.Response.Output);     
    }   
}

It seems like a straightforward way to:

• add metadata to each XSL, like lastUsed, useCount, etc.
• bulk update/search capabilities
• prevent lots of disk access
• avoid referencing relative paths and organizing files
• allow XSL changes without redeploying (I could even write an admin page that selects/updates the XSL in the database)

Has anyone tried this before? Are there any caveats?

EDIT

Caveats that responders have listed:

• disk access isn't guaranteed to diminish
• this will break xsl:includes

Answer 1

The two big issues I can see are:

1. We use a lot of includes to ensure that we only do things once, storing the XSLT in the database would stop us from doing that.
2. It makes updating XSLs more interesting - we've been quite happy to dump new .xsl files into deployed sites without doing a full update of the site. For that matter we've got bits of code that look for client specific xsl in a folder and those bits of code can reach back up to common code (templates) in the root - so I'm not sure about the redeploy thing at all, but this will depend very much on the particular use case, yours is certainly different to ours.

In terms of disk access, hmm... the db still has to go access the disk to pull the data and if you're talking about caching then the db isn't a requirement for enabling caching.

Have to agree about the update/search options - you can do stuff with Powershell but that needs to be run on the server and that's not always a good idea.

Technically I can see no reason why not (excepting the wish to do includes as above) but practically it seems to be fairly balanced with good arguments either way.

Answer 2

I store XSLTs in a database in my application dbscript. (However I keep them in an NVARCHAR column, since it also runs on SQL Server 2000)

Since users are able to edit their XSLTs, I needed to write a custom validator which loads the text of TextBox in a .Net XslCompiledTransform object like this:

    args.IsValid = true;

    if (args.Value.Trim() == "")
        return;

    try
    {
        System.IO.TextReader rd = new System.IO.StringReader(args.Value);
        System.Xml.XmlReader xrd = System.Xml.XmlReader.Create(rd);
        System.Xml.Xsl.XslCompiledTransform xslt = new System.Xml.Xsl.XslCompiledTransform();
        System.Xml.Xsl.XsltSettings xslts = new System.Xml.Xsl.XsltSettings(false, false);
        xslt.Load(xrd, xslts, new System.Xml.XmlUrlResolver());
        xrd.Close();
    }
    catch (Exception ex)
    {
        this.ErrorMessage = (string.IsNullOrEmpty(sErrorMessage) ? "" : (sErrorMessage + "<br/>") +
            ex.Message);
        if (ex.InnerException != null)
        {
            ex = ex.InnerException;
            this.ErrorMessage += "<br />" + ex.Message;
        }
        args.IsValid = false;
    }

As for your points:

• file I/O will be replaced by database-generated disk I/O, so no gains there

• deployment changes to providing an INSERT/UPDATE script containing the new data