Geek asks and answers

XML validation using XSD specified at run time in SQL Server 2008?

SQL Server 2008 allows us to validate XML based on an existing collection of XML schemas by defining a typed XML column / variable: DECLARE @TypedXml XML(MyXmlSchemaCollection)

However, as far as I can see, the assembly of the XML schemas should be known during the definition of the column / variable.

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

For instance:

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?
+3
source share
2 answers

For your requirement, I will probably learn CLR integration: using stored procedures or user-defined functions ; you should first check if CLR integration is what is allowed in your environment.

; , XML- .NET ; , - SQL Server, , SQL 2008 ...

, , , XSD SQL Server, CLR. XSD SQL-, , . , XSD . - XSD, , XSD (/import/redefine ). , xsd: include SQL Server...

+1

XSD, , SQL .

- XSD, , CLR, .

script. , XML . try/catch, , , .

: SQL , script .

    DECLARE @result int

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

    EXEC @result = sp_executesql @XsdValidationSQL

    RETURN @result
0

More articles:


All Articles