使用xml类型在SQL Server 2005中存储XSLT？

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:

我的ASP.NET Web应用程序中有很多XSL文件。很多。我使用这种通用转换方法生成一堆AJAX HTML响应:

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.

我想使用xml类型的列将XSL定义移动到SQL Server中。我将整个XSL文件存储在SQL中的单行中,并且每个XSL都是自包含的(无导入)。我会将SQL中的XSL定义读出到我的XslTransform对象中。

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.

向每个XSL添加元数据,如lastUsed,useCount等。

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)

允许XSL更改而无需重新部署(我甚至可以编写一个管理页面来选择/更新数据库中的XSL)

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

这将打破xsl:includes

2 个解决方案

#1

The two big issues I can see are:

我能看到的两大问题是:

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.

我们使用了很多包含来确保我们只执行一次,将XSLT存储在数据库中会阻止我们这样做。

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.

它使更新XSL更有趣 - 我们非常乐意将新的.xsl文件转储到已部署的站点,而无需对站点进行全面更新。就此而言,我们有一些代码可以在文件夹中查找客户端特定的xsl,而这些代码可以回溯到根目录中的公共代码(模板) - 所以我不确定重新部署的东西是什么,但这在很大程度上取决于具体的用例,你的肯定与我们的不同。

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.

必须同意更新/搜索选项 - 您可以使用Powershell进行操作但需要在服务器上运行,这并不总是一个好主意。

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.

从技术上讲,我没有理由不这样做(除了希望做的包括如上所述),但实际上它似乎与任何方式的良好论据相当平衡。

#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)

我将XSLT存储在我的应用程序dbscript中的数据库中。 (但是我将它们保存在NVARCHAR列中,因为它也在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:

由于用户能够编辑他们的XSLT,我需要编写一个自定义验证器,它在.Net XslCompiledTransform对象中加载TextBox的文本,如下所示:

    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

文件I / O将被数据库生成的磁盘I / O替换,因此没有增益
deployment changes to providing an INSERT/UPDATE script containing the new data

部署更改为提供包含新数据的INSERT / UPDATE脚本

#1