Geek asks and answers

How to check VARCHAR (n) for well-formed XML before CAST / CONVERT

My company has a logging table that contains a column that contains a VARCHAR(N)row that should be XML, but as it turned out, it is not always well formed. To perform log analysis (to identify error trends, etc.), I used the operator LIKE. However, this is remarkably slow.

I recently discovered that SQL Server supports XQuery, so I started playing with it. The problem that I am facing is that I cannot understand the error handling method CAST/CONVERTin my instruction SELECT. The closest I have come requires SQL Server 2012 as it has a feature TRY_CONVERT, but upgrading from 2008 R2 is not an option now.

Here's what I have (what will work if my company works in 2012):

CREATE FUNCTION IsMatch(
    @message AS VARCHAR(MAX),
    @match AS VARCHAR(MAX),
    @default AS VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @xml XML = TRY_CONVERT(XML, @message)
    DECLARE @result VARCHAR(MAX) =
        CASE WHEN @xml IS NOT NULL
             THEN CASE WHEN @xml.exist('(/FormattedMessage)[contains(.,sql:variable("@match"))]') = 1
                       THEN @match 
                       ELSE @default 
                       END
             ELSE CASE WHEN @message LIKE '%' + @match + '%'
                       THEN @match
                       ELSE @default
                       END
             END
    RETURN @result
END

GO

DECLARE @search VARCHAR(MAX) = 'a substring of my xml error message'

SELECT Error, COUNT(*) as 'Count'
FROM ( SELECT TOP 319 [LogID]
          ,[Severity]
          ,[Title]
          ,[Timestamp]
          ,[MachineName]
          ,[FormattedMessage]
          --,CAST([formattedmessage] as xml)
          ,IsMatch(@search, 'Other') as 'Error'
       FROM [MyDatabase].[dbo].[Log] (NOLOCK) ) a
GROUP BY Error

Commented out CAST(or alternately a CONVERT) will result in a request error as soon as it encounters malformed XML. If I restrict TOP (N), I can guarantee that there are no errors, and the operator SELECTis incredibly fast. I just need a way to handle errors for each line.

I have considered using TRY/CATCHin IsMatch(), but this cannot be used in a function. As an alternative to use TRY/CATCH, I looked at the stored procedure, but I cannot figure out how to include it in the sentence SELECT.

+5
source share
1 answer

2008 R2, , . TRY CATCH WHILE @@FETCH_STATUS = 0.

DECLARE logcursor CURSOR FORWARD_ONLY READ_ONLY FOR 
SELECT TOP 319 [LogId]
          ,[formattedmessage]
       FROM [GenesisLogging].[dbo].[Log] (NOLOCK)

OPEN logcursor

FETCH NEXT FROM logcursor
INTO @id, @formattedmessage

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
    SET @xml = CONVERT(xml, @formattedmessage)
    END TRY
    BEGIN CATCH
    PRINT @id
    END CATCH; 
    FETCH NEXT FROM logcursor 
    INTO @id, @formattedmessage
END 
CLOSE logcursor;
DEALLOCATE logcursor;
+4

More articles:


All Articles