SQL Server 2016大表HTML数据高效解析方案咨询

针对大表场景，以下几种方法可以解决你遇到的UDF性能差、XML解析失效的问题：

一、使用CLR用户定义函数（最优大表方案） #

T-SQL标量UDF在大表上逐行执行性能极差，而CLR函数利用.NET的高效字符串/HTML处理能力，能大幅提升处理速度。

实现步骤： #

1. 编写C# CLR函数：使用轻量HTML解析库HtmlAgilityPack可靠提取文本，避免正则表达式的局限性：

using System;
using System.Data.SqlTypes;
using HtmlAgilityPack;

public class HtmlParser
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString StripHtml(SqlString htmlInput)
    {
        if (htmlInput.IsNull) return SqlString.Null;
        
        var doc = new HtmlDocument();
        doc.LoadHtml(htmlInput.Value);
        return new SqlString(doc.DocumentNode.InnerText.Trim());
    }
}

2. 编译并部署到SQL Server：
   • 启用CLR：

     sp_configure 'show advanced options', 1; RECONFIGURE;
     sp_configure 'clr enabled', 1; RECONFIGURE;
     

   • 注册DLL（假设DLL名为HtmlParser.dll）：

     CREATE ASSEMBLY HtmlParserAssembly FROM 'C:\Path\To\HtmlParser.dll' WITH PERMISSION_SET = SAFE;
     CREATE FUNCTION dbo.StripHtml(@html NVARCHAR(MAX)) RETURNS NVARCHAR(MAX)
     EXTERNAL NAME HtmlParserAssembly.HtmlParser.StripHtml;
     

3. 批量调用：直接在查询中使用，性能远优于T-SQL UDF：

SELECT ID, dbo.StripHtml(Valuen) AS CleanedValue
FROM YourLargeTable;

二、T-SQL批量替换方案（无需CLR，适合快速实现） #

如果无法启用CLR，可以用批量字符串替换移除HTML标签，避免逐行UDF的性能损耗，适合标签格式相对规范的场景：

递归替换版本（覆盖嵌套标签）： #

WITH RecursiveReplace AS (
    SELECT 
        ID, 
        Valuen,
        1 AS ReplaceLevel
    FROM YourLargeTable
    UNION ALL
    SELECT 
        ID,
        REPLACE(REPLACE(Valuen, SUBSTRING(Valuen, CHARINDEX('<', Valuen), CHARINDEX('>', Valuen) - CHARINDEX('<', Valuen) + 1), ''), '  ', ' ') AS Valuen,
        ReplaceLevel + 1
    FROM RecursiveReplace
    WHERE CHARINDEX('<', Valuen) > 0 AND ReplaceLevel < 10 -- 限制递归次数，覆盖多数嵌套场景
)
SELECT 
    ID,
    LTRIM(RTRIM(Valuen)) AS CleanedValue
FROM RecursiveReplace
WHERE ReplaceLevel = 10 OR CHARINDEX('<', Valuen) = 0
GROUP BY ID, Valuen;

非递归多次替换版本（性能更优）： #

SELECT
    ID,
    LTRIM(RTRIM(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
            Valuen, '<t>', ''), '</t>', ''), '<some>', ''), '</some>', ''), '<none>', ''), '</none>', '')
        -- 可继续添加需要处理的其他标签
    )) AS CleanedValue
FROM YourLargeTable;

三、修复XML解析方案（解决转换失效问题） #

XML解析失效通常是因为原始HTML包含未转义的特殊字符（如&、<）或不规范标签，预处理后即可正常解析：

SELECT
    ID,
    LTRIM(RTRIM(
        CAST(
            '<root>' + REPLACE(REPLACE(REPLACE(Valuen, '&', '&amp;'), '<', '&lt;'), '>', '&gt;') + '</root>'
            AS XML).value('.', 'NVARCHAR(MAX)')
    )) AS CleanedValue
FROM YourLargeTable;

注：此方法对严重不规范的HTML（如未闭合标签）仍可能失效，但能解决大部分转换失败的情况。

内容的提问来源于stack exchange，提问作者Avi