XML Schema validation in SQL Server

  • Eirikur Eiriksson

    SSC Guru

    Points: 182509

    Comments posted to this topic are about the item XML Schema validation in SQL Server

  • fregatepllada

    SSCommitted

    Points: 1648

    Thank you for a great article!

    I am not quite agree with this statement "SQL Server has no specific functions or methods for validating XML".

    How about strongly typed XML parameter in stored procedure bound by XML Schema collection.

  • Eirikur Eiriksson

    SSC Guru

    Points: 182509

    fregatepllada (4/17/2014)


    Thank you for a great article!

    I am not quite agree with this statement "SQL Server has no specific functions or methods for validating XML".

    How about strongly typed XML parameter in stored procedure bound by XML Schema collection.

    Thank you fregatepllada.

    The validation is handled similar to a type cast, which throws an exception if the conversion is not allowed. Therefore if an invalid XML is passed to the procedure, the server will still throw an exception.

    😎

  • fregatepllada

    SSCommitted

    Points: 1648

    Yes, I agree - but you could use a wrapper SP to catch an exception (RUSSIAN DOLLs model :-))

  • Eirikur Eiriksson

    SSC Guru

    Points: 182509

    fregatepllada (4/17/2014)


    Yes, I agree - but you could use a wrapper SP to catch an exception (RUSSIAN DOLLs model :-))

    Boils down to the same thing, the exception rolls back the transaction and inhibits a set based method. This is obvious if you look at the identity (TEST_LOG_ID) values in the results of listing 9, the value 3 is missing as a result of a rollback:

    TEST_LOG_ID TEST_DATA_ID TEST_LOG_MESSAGE

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

    1 1 NULL

    2 2 NULL

    4 3 XML Validation: Invalid simple type value: ''. Location: /*:ORDER[1]/*:DETAIL[1]/@*:PRODUCT_NO

    5 4 NULL

    6 5 NULL

    😎

  • Xedni

    SSCertifiable

    Points: 6947

    Be careful of the hanging transaction state if the try catch assignment fails. If you're using explicit transactions or nested proc calls, you can end up in a situation where you get

    The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

    I still very much like schema validation (especially on variables) but there's certainly some funk with them.

    Executive Junior Cowboy Developer, Esq.[/url]

  • robert_verell

    Say Hey Kid

    Points: 668

    I love schema collections in SQL Server. Very under-utilized by web developers who also use sql server, since they like to do everything with .NET, java, whatever.

    At my last gig I had a project where I had to extract a large amount of data in xml format and ftp to a client or 3rd party and used a schema collection bound to an xml column as in this example to validate the xml prior to shipping it off. I also put the insert in a try catch and any errored out xml inserts I would push to an error table for analysis. Worked really well.

  • suhask

    Newbie

    Points: 1

    I have used your script to run in SQL server , I got an error

    Msg 6314, Level 16, State 1, Line 53

    Collection specified does not exist in metadata : 'dbo.MY_XML_ORDER_SCHEMA'

     

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

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