Post reply

How to skip rows not in wellformed xml

  • March 6, 2012 at 5:44 am

    #269433

    Dear All,

    I have a SELECT query like this

    select top 1 * from tMyTable where

    convert(xml,convert(nvarchar(max),xmldocfield)).value('(//record/customerallowcontact)[1]','nvarchar(100)') = '-1'

    Error i got:

    Msg 9412, Level 16, State 1, Line 3

    XML parsing: line 75, character 86, '>' expected

    -----------------

    i guess this is because some of the data inside this column might not be in a well formed xml.

    So, My question is how can i skip those records to make my WHERE clause to filter.

    some thing like "WHERE xmldocfield IS WELLFORMED AND MyCondition"

    Always appreciating your helps.

  • March 6, 2012 at 5:50 am

    #1455229

    Only way I can think of would be to use a CLR. Do a try catch block to convert to XML, if it fails then return 0 if it passes then return 1. Then in your where clause, you'd have something like "WHERE dbo.myXMLCheck(myXMLCol) = 1".

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 2 posts - 1 through 1 (of 1 total)

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