如何在CAST/CONVERT之前检查VARCHAR(n)中格式正确的XML [英] How to check VARCHAR(n) for well-formed XML before CAST/CONVERT

问题描述

我的公司有一个日志表，其中包含一个 VARCHAR(N)列，其中放置了一个假定为XML的字符串，但事实证明是并非总是格式正确的.为了对日志记录进行分析(以识别错误趋势等)，我一直在使用 LIKE 语句.但是，这非常慢.

My company has a logging table containing a VARCHAR(N) column in which a string is placed that is supposed to be XML, but as it turns out it is not always well-formed. In order to perform analysis on the logging (to identify error trends, etc.), I have been using a LIKE statement. However, this is remarkably slow.

最近，我发现SQL Server支持XQuery，因此我开始使用它.我遇到的问题是，我找不到在 SELECT 语句中处理 CAST/CONVERT 错误的方法.我最接近的服务器需要SQL Server 2012，因为它具有 TRY_CONVERT 函数，但是从2008 R2升级现在还不可行.

Recently, I discovered that SQL Server supports XQuery, so I started playing with it. The problem I'm running into is that I can't figure out a way to handle CAST/CONVERT errors within my SELECT statement. The closest I've come requires SQL Server 2012 as it has the TRY_CONVERT function, but upgrading from 2008 R2 is not an option right now.

这里是我所拥有的(如果我的公司正在运行2012，那将可以工作):

Here is what I have (which would work if my company was running 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

注释后的 CAST (或 CONVERT )将在查询遇到格式错误的XML时立即导致错误.如果我限制为TOP(N)，则可以确保没有错误，并且 SELECT 语句的运行速度非常快.我只需要一种按行处理错误的方法.

The commented CAST (or alternately a CONVERT) will cause the query to error as soon as it encounters malformed XML. If I limit to a TOP (N), I can ensure that there are no errors and the SELECT statement work incredibly fast. I just need a way to handle errors on a per-row basis.

我考虑过在 IsMatch()中使用 TRY/CATCH ，但是不能在函数中使用.另外，要使用 TRY/CATCH ，我考虑了一个存储的proc，但是我不知道如何在我的 SELECT 子句中包括它.

I considered using a TRY/CATCH in IsMatch(), but that can't be used in a function. Alternatively, to use the TRY/CATCH, I considered a stored proc, but I can't figure out how to include that in my SELECT clause.

推荐答案

如果您坚持使用2008 R2，我想您想做的就是在存储过程中使用只读正向CURSOR.然后在WHILE @@ FETCH_STATUS = 0循环内使用TRY CATCH块.

If you're stuck on 2008 R2 I think what you want to do is use a readonly forward CURSOR in your stored procedure. Then use the TRY CATCH block inside of the WHILE @@FETCH_STATUS = 0 loop.

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;

这篇关于如何在CAST/CONVERT之前检查VARCHAR(n)中格式正确的XML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！