SqlServerKudos

I've been workig with XML in SQL Server Integration services this week and to say the XML task in SQL Server Integration services sucks is an understatement. Its buggy, the documentation is woefully inadequate in some cases wrong. I believe this was one of the first components written back for SQL 2005 so you can imagine it was written along time ago, XML wasn't as standardised.

So what if you want to validate an XML document against a XSD Schema. Well in true SQL Server Integration services mode the answer is to do it yourself with a script task.

There are two ways of doing it, the first uses an XmlDocument object and the other is the XmlReader. If you were on my XML course at SQLBits you will remember that I pointed out the importance of straeming when dealing with large data and this is where these two differ. The XmlDocument loads the document into memory, whereas the XmlReader is a streaming interface to the XML document you have. So if you have a large document you should use the XmlReader.

In my case however I wanted to pass the XML to a stored procedure after it had been validated and so I used the XmlDocument.

The first step is to create a XmlSchemaSet and put your schema into it.

Welcome!

XML Validation in SQL Server Intergration Services

No comments yet, be the first one to post comment.

Open ID Login

Standard Login

Forgot Password?

Sign up