CODEWITHSUNDEEP
xmlsql-server-2008t-sqlxsd
Tim S
Tim S

Reputation: 727

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

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?

Upvotes: 5

Views: 6978

Answers (2)

kappamaki
kappamaki

Reputation: 676

If you are validating against a predefined set of XSDs that are stored in your database, you could use dynamic SQL in a stored procedure to do the validation.

If you want to validate against any XSD, it would probably be best to use CLR as in Petru's answer.

The following script would work. It throws an exception if the input XML was not valid. You could use a try/catch clause if you wanted the validation to simply return a result, instead of throwing an exception.

Edit: You cannot use dynamic SQL from within a function, but you could wrap this script in a stored procedure.

    DECLARE @result int

    DECLARE @XsdValidationSQL nvarchar(max) = 
        'DECLARE @xml xml(' + @xsdSchema + '.' + @xsdName + ') = ''' + CONVERT(nvarchar(max), @xml) + ''''

    EXEC @result = sp_executesql @XsdValidationSQL

    RETURN @result

Upvotes: 0

Petru Gardea
Petru Gardea

Reputation: 21658

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...

Upvotes: 2

Related Questions