Validate XML using XSD specified at runtime in SQL Server 2008?

Question

SQL Server 2008 allows us to validate XML against an existing XML Schema Collection by defining a typed XML column/variable: DECLARE @TypedXml XML(MyXmlSchemaCollection)

However, as far as I can see, the XML Schema Collection has to be known at the time we define the column/variable.

Is there a way to validate XML using an XML schema specified at runtime?

For example:

DECLARE @Xml XML
SET @Xml = 
N'<person>
    <firstname>Ming</firstname>
    <lastname>The Merciless</lastname>
</person>'

DECLARE @Xsd XML
SET @Xsd =
N'<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
 <xs:element name="person">
  <xs:complexType>
    <xs:sequence>
      <xs:element name="firstname" type="xs:string"/>
      <xs:element name="lastname" type="xs:string"/>
      <xs:element name="dateofbirth" type="xs:date"/>
    </xs:sequence>
  </xs:complexType>
 </xs:element>
</xs:schema>'

DECLARE @Valid BIT
SET @Valid = (SELECT dbo.f_ValidateXmlAgainstXsd(@Xml, @Xsd)) -- With a user-defined function?
EXEC @Valid = s_ValidateXmlAgainstXsd @Xml, @Xsd -- With a stored procedure?

Answer 1

For your kind of requirement I would probably explore CLR integration: using stored procedures or user defined functions; you should check first if in your environment, CLR integration is something that's allowed.

It would be too much to post here; however, the code to validate an XML document using .NET is common; and the links I've added to the online books for SQL Server will be there as long as SQL 2008 will be around...

In general, I believe that to implement a validation that's not limited by how XSD is supported on SQL Server, one should rely on CLR integration. The limitations in XSD collections on SQL server as described here could swing many people in this direction anyway. There are some issues to figure out in a high volume environment, caching of compiled XSDs being the most important one in my mind. The other one is the complexity of the XSDs, and how the XSDs are sourced and referenced (include/import/redefine support). Trying to implement yourself these aspects would give you for sure an appreciation as to why xsd:include is not supported by SQL Server...