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?

Petru Gardea · Accepted Answer · 2012-05-22 02:22:43Z

2

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

answered May 22, 2012 at 2:22

Petru Gardea

21.7k2 gold badges55 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tim S Over a year ago

Thanks for a very comprehensive answer Petru. I suspect that CLR is the way to go but just wanted to check I haven't overlooked a way of doing it in native SQL Server code.

kappamaki · Accepted Answer · 2016-01-07 18:57:59Z

0

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

edited Jan 7, 2016 at 18:57

answered Dec 2, 2015 at 21:16

kappamaki

6748 silver badges14 bronze badges

2 Comments

new2ios Over a year ago

Can you explain in more details how you do that, because the result is:

Msg 557, Level 16, State 2, Line 6 Only functions and some extended stored procedures can be executed from within a function.

kappamaki Over a year ago

@new2ios You're right, I tested the code without actually testing it after wrapping it in a function. I didn't know that you aren't allowed to execute dynamic SQL from within functions. Sorry.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related