For Xml and XmlSchema are great... but large schemas get truncated!

I am developing a BizTalk solution that will extract data from a Sql Server database and load it into another data repository. There is a feature new to Sql Server 2005 that allows the Xml Schema for the result set of an Sql statement to be generated.

Select * From Table for Xml Auto, XmlSchema

The result is a single column result set that contains not only the data in xml format but also the xml schema. A call to ExecuteScalar brings the schema into the .Net world. This is a great help as I can use the schema in my BizTalk mapping.

The problem is that for larger schemas, the result is truncated. With some googling, I found this post and my problems were solved. It turns out that if the schema is too long, it will be cut up and returned in different rows. When I iterated through the rows in the result set and concatenated the contents of each, I ended up with a complete xml schema.

Thanks Milan!