主页 / dba / 问题 / 339889
Accepted
Marcello Miorelli
Asked: 2024-05-30 05:04:04 +0800 CST

如何在 SQL Server 中将 nvarchar(max) 或 varchar(max) 转换为 xml

  • 772

我正在尝试将msdb 作业消息转换为 xml。

我正在使用Ola Hallengren 备份程序。

当我运行以下例程时，它运行惊人并转换所有内容。

现在，如果我将数字 3333 更改为 3702，则会失败。

我无法识别消息中字符 3701 后面的内容，但这阻碍了我将完整消息转换为 xml

如果您使用Ola，您可以在自己的系统上进行测试。如果您知道如何更改我的函数以便我可以成功地将 nvarchar(max) 或varchar(max)转换 为 xml ，请告诉我。

create or alter  FUNCTION dbo.fnCleanString (@InputString VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @CleanedString VARCHAR(MAX) = '';
    DECLARE @Char VARCHAR(1);
    DECLARE @Index INT = 1;
    DECLARE @Len INT = 3333-- LEN(@InputString);

    WHILE @Index <= @Len
    BEGIN
        SET @Char = SUBSTRING(@InputString, @Index, 1);
        IF (ASCII(@Char) NOT BETWEEN 0 AND 31 OR ASCII(@Char) IN (9, 10, 13))
        BEGIN
            SET @CleanedString += @Char;
        END
        SET @Index += 1;
    END

    RETURN @CleanedString;
END
go

SELECT TOP 50
    SysJobs.name,
    SysJobs.enabled,
    Job.message AS Message_Text,
    TRY_CAST(
        (SELECT dbo.fnCleanString(Job.message) AS [text()]
         FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') AS XML
    ) AS Message_XML
FROM msdb.dbo.sysjobhistory Job WITH (NOLOCK)
INNER JOIN msdb.dbo.sysjobs SysJobs WITH (NOLOCK)
    ON Job.job_id = SysJobs.job_id
WHERE SysJobs.name = 'DatabaseBackup - USER_DATABASES - FULL'
ORDER BY Job.instance_id DESC;
sql-server

2 个回答

  1. 数字 3701 和 3702 会有所不同，具体取决于作业历史记录 [ message] 中出现左 V 形括号的位置。转换失败，因为该代码构成了模块 [ ] 中IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1)使用 [ ] 创建文件夹的代码的一部分。xp_create_subdirDatabaseBackup

    我并不承认自己是 XML 或编码专家，但解决此特定问题的一种简单/肮脏方法是用 替换（<>在函数顶部设置为之前）。您可以创建一个拉取请求以在 Ola 的维护解决方案中全局更改代码 - 但我不建议这样做，因为这会成为一场类似于制表符与空格之间的哲学战争（并且会带来很多麻烦/风险）。!=@InputString@LenLEN(@InputString)

    修复函数的更具扩展性的方法是将函数中SET @CleanedString += @Char;的替换为专门处理左 V 形括号。这会将返回的 XML 中的SET @CleanedString += CASE @Char WHEN '<' THEN '&lt;' ELSE @Char END;输出更改为。IF @ReturnCode &lt;&gt; 0 RAISERROR('Error creating directory.', 16, 1)

    CASE 语句可以扩展以处理可能出现在作业历史记录 [消息] 中的其他控制字符。有处理 XML 中所有控制字符的列表和方法（例如您提供的第一个链接，这让我很头疼）。

    • 2
  2. Best Answer

    我得到了一个在我所做的所有测试中都运行良好的功能。我主要查询了 msdb 数据库中的表和视图。

    这是函数：

    IF OBJECT_ID('dbo.SplitStringXML') IS NULL
  EXEC ('
  
CREATE FUNCTION dbo.SplitStringXML
(
  @TXT VARCHAR(1008)
 )
    RETURNS VARCHAR(1008)
    AS
    BEGIN
       RETURN  (select [radhe] = ''Hare Krishna - foo bar'' );  
    END
  
  ')
GO
--DROP FUNCTION dbo.SplitStringXML  

ALTER FUNCTION dbo.SplitStringXML(@List NVARCHAR(MAX))
RETURNS XML
WITH SCHEMABINDING, ENCRYPTION
AS
BEGIN
    RETURN (
        SELECT TRY_CAST(
            (
                SELECT 
                    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                        SUBSTRING(@List, n, 1), 
                        '&', '&amp;'), 
                        '<', '&lt;'), 
                        '>', '&gt;'), 
                        '''', '&apos;'), 
                        '"', '&quot;')
                FROM (
                    SELECT 
                        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
                    FROM 
                        (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS D1(n),
                        (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS D2(n),
                        (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS D3(n),
                        (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS D4(n)
                ) AS Numbers
                WHERE n <= LEN(@List)
                  AND (ASCII(SUBSTRING(@List, n, 1)) NOT BETWEEN 0 AND 31
                       OR ASCII(SUBSTRING(@List, n, 1)) IN (9, 10, 13))
                FOR XML PATH('')
            ) AS NVARCHAR(MAX)
        ) AS XML
    )
END
GO

    这是我使用此功能的一个示例：

       -----  the way this function has been used:

            SELECT TOP 5000
                SysJobs.name,
                SysJobs.enabled,
                Job.message AS Message_Text,
                Message_XML = dbo.SplitStringXML(Job.message)
            FROM msdb.dbo.sysjobhistory Job WITH (NOLOCK)
            INNER JOIN msdb.dbo.sysjobs SysJobs WITH (NOLOCK)
                ON Job.job_id = SysJobs.job_id
            --WHERE SysJobs.name = 'DatabaseBackup - USER_DATABASES - FULL'
            ORDER BY Job.instance_id DESC;

    挑战：

    如果您发现在使用此功能时将 a 转换为nvarchar(max)会xml导致出现问题，请告诉我。

    • 0

相关问题