Início / dba / Perguntas / 339889
Accepted
Marcello Miorelli
Asked: 2024-05-30 05:04:04 +0800 CST

como converter nvarchar(max) ou varchar(max) para xml no sql server

  • 772

Estou tentando converter mensagens de trabalhos msdb para xml .

Estou usando rotinas de backup de Ola Hallengren .

quando executo a rotina a seguir, ela funciona de maneira incrível e converte tudo.

agora, se eu mudar o número 3333 para 3702 ele falha.

Não consigo identificar o que vem depois do caractere 3701 na mensagem, mas isso está me impedindo de converter a mensagem completa para xml

Se você usa o Ola, pode testar em seu próprio sistema. deixe-me saber se você descobrir como alterar minha função para que eu possa converter nvarchar(max) ou varchar(max) em xml com sucesso .

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 respostas

  1. Os números 3701 e 3702 variam dependendo de onde no histórico do trabalho [ message] um colchete de divisa esquerdo é encontrado. A conversão falha no IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1)que faz parte do código para criação de pastas usando [ xp_create_subdir] no módulo [ DatabaseBackup].

    Não confesso ser um especialista em XML ou codificação, mas uma maneira simples/suja de contornar esse problema específico seria substituir <>por !=no @InputString(no topo da sua função antes de definir @Lencomo LEN(@InputString)). Você poderia criar uma solicitação pull para alterar o código globalmente na solução de manutenção do Ola - mas eu não recomendo isso, pois seria uma guerra filosófica semelhante a guias versus espaços (além de ser muito complicada/risco).

    Uma maneira mais extensível de corrigir sua função seria substituí-la SET @CleanedString += @Char;por SET @CleanedString += CASE @Char WHEN '<' THEN '&lt;' ELSE @Char END;dentro de sua função para lidar especificamente com colchetes esquerdos. Isso mudará a saída para IF @ReturnCode &lt;&gt; 0 RAISERROR('Error creating directory.', 16, 1)o XML retornado.

    A instrução CASE pode ser estendida para lidar com caracteres de controle adicionais que podem aparecer no histórico do trabalho [mensagem]. Existem listas e abordagens para lidar com todos os caracteres de controle em XML (por exemplo, o primeiro link que você forneceu, que me dá dor de cabeça).

    • 2
  2. Best Answer

    Consegui uma função que funcionou bem para mim em todos os testes que fiz. principalmente, consultei tabelas e visualizações do banco de dados msdb.

    aqui está a função:

    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

    este é um exemplo de como tenho usado esta função:

       -----  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;

    desafio:

    Se você encontrar alguma situação em que a conversão de um nvarchar(max)esteja xmlcausando problemas ao usar esta função, me avise.

    • 0

relate perguntas