Post reply

Check for well formed xml

  • January 10, 2012 at 12:48 pm

    #254717

    I'm trying to query strings which, many of which are xml, like this:

    SELECT@HtmlText.value('(.)[1]', 'nvarchar(max)')

    But some of the records contain neither plain text nor well formed xml.. e.g. may have an ending '</p>' tag but not a matching begining tag.

    Is there some way I can check, within the udf, whether a string passed to the udf is well formed xml?

  • January 10, 2012 at 5:17 pm

    #1431512

    Not within a function, no. Functions do not allow TRY...CATCH blocks, so the normal trick of converting to untyped XML inside such a block cannot be used. Consider rewriting the function as a procedure, or validating the XML before it hits SQL Server. XML validation is frequently more efficient using a CLR function (these cannot be called from a T-SQL function either).

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • January 10, 2012 at 5:23 pm

    #1431516

    Ok. Thanks.. saves me a lot of pointless searching..

  • January 10, 2012 at 5:41 pm

    #1431521

    Small comfort, but it will be possible in SQL Server 2012, using the new TRY_CONVERT function:

    ...returns NULL.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply