Sample Header Ad - 728x90

how to convert nvarchar(max) or varchar(max) to xml in sql server

0 votes
2 answers
433 views
I am trying to convert msdb jobs messages to xml . I am using Ola Hallengren backup routines . when I run the following routine it works amazing and converts everything. now, if I change the number 3333 to 3702 it fails. I cannot identify what is after the character 3701 in the message, but that is holding me back to convert the full message to xml If you use Ola's you can test on your own system. let me know if you find out how to change my function so that I can successfully convert nvarchar(max) or varchar(max) to 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;
Asked by Marcello Miorelli (17274 rep)
May 29, 2024, 09:04 PM
Last activity: Jul 9, 2024, 03:35 PM