XML Schema validation in SQL Server

Question

Post reply

XML Schema validation in SQL Server

Eirikur Eiriksson

SSC Guru

Points: 182911
More actions
April 17, 2014 at 12:04 am

#287690

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

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

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

fregatepllada SSCommitted Points: 1702 More actions · Answer 1

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: 182911 More actions · Answer 2

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: 1702 More actions · Answer 3

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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 4

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: 6951 More actions · Answer 5

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 More actions · Answer 6

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 More actions · Answer 7

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'