Validate XML on the fly in SQL Server 2005...? 1

[blondebier]

Hi Guys,

I am sending some XML to a stored procedure via an XML parameter. I am storing the XSDs (schemas) for these XML documents in a table. (Also XML datatype)

I'd really like to be able to read the schema and validate the XML documents on the fly...

Anyone ever done this or know of a cool way of doing it?

I've looked at the "CREATE XML SCHEMA COLLECTION" but it looks like it is something rather permanent.

I'd like something generic so that I can validate any type of XML document against it's own schema.

Any ideas or tips would be great.

Cheers,

Blondebier

 

[philhege]

You could probably write a CLR-based stored procedure to load up the DOM and do your validation within the CLR code.

I'm ashamed to admit that I'm not as savvy about SQL's XML capabilities as I should be, but I feel this is a viable solution.

Phil H.
Some Bank
-----------
Time's fun when you're having flies.

 

[blondebier]

Thanks for the reply Phil. I've always felt a bit uneasy about using clr. We have exposed some assemblies before, but it just seems a little bit odd. It's dirty!

I thought that if there was a way of doing it using the XML support in SQL Server 2005 then I might as well use it. Maybe 2008 will be better...

I wrote a stored procedure that uses the XML SCHEMA COLLECTION and that seems to work, but if you change your schema then you would also have to alter the XML SCHEMA COLLECTION.

Decisions, decisions...

 

[blondebier]

I thought I'd post my code in case anyone else would find it useful in the future...

Here's the VB.Net Assembly that you'll expose to SQL server

Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.IO
Imports System.Runtime.InteropServices
Imports System.Xml
Imports System.Xml.Schema
Imports Microsoft.SqlServer.Server

Public Class YourFunkyClassName

    ' Validates XML against XSD - returns schema validation error if found
    Public Shared Sub ValidateXML(ByVal XMLtoValidate As String, ByVal XSD As String, ByRef FailureMessage As String)

        Dim sqlP As SqlPipe = SqlContext.Pipe()
        Dim schemaError As String = ""

        Try
            ' Check that the XMLtoValidate is well formed
            Dim IsValidXML As New XmlDocument
            Try
                IsValidXML.LoadXml(XMLtoValidate)

            Catch ex As Exception
                FailureMessage = "XML was not well formed"

                'Dim sdr As New SqlDataRecord(New SqlMetaData("FailureMessageAsResult", SqlDbType.NVarChar, 4000))
                'sqlP.SendResultsStart(sdr)
                'sdr.SetString(0, FailureMessage)
                'sqlP.SendResultsRow(sdr)
                'sqlP.SendResultsEnd()
                Exit Sub
            End Try

            Dim SchemaReader As XmlReader = XmlReader.Create(New StringReader(XSD))

            ' Load the schema into the schema object...
            Dim Schema As XmlSchema = XmlSchema.Read(SchemaReader, Nothing)

            ' Check that namespace from the schema is the same as the XML to validate
            Dim ns As String = ""
            If Not IsValidXML.FirstChild.Attributes("xmlns") Is Nothing Then
                ns = IsValidXML.FirstChild.Attributes("xmlns").InnerText
            End If

            If Schema.TargetNamespace <> ns Then
                FailureMessage = "XML has an invalid namespace"
                'Dim sdr As New SqlDataRecord(New SqlMetaData("FailureMessageAsResult", SqlDbType.NVarChar, 4000))
                'sqlP.SendResultsStart(sdr)
                'sdr.SetString(0, FailureMessage)
                'sqlP.SendResultsRow(sdr)
                'sqlP.SendResultsEnd()

                Exit Sub
            End If

            ' Configure the reader to use validation, and add the schema we just loaded...
            Dim SchemaReaderSettings As New XmlReaderSettings()
            SchemaReaderSettings.ValidationType = ValidationType.Schema
            SchemaReaderSettings.Schemas.Add(Schema)
            SchemaReaderSettings.ValidationFlags = XmlSchemaValidationFlags.ProcessInlineSchema
            SchemaReaderSettings.ValidationFlags = XmlSchemaValidationFlags.ReportValidationWarnings
            SchemaReaderSettings.ValidationFlags = XmlSchemaValidationFlags.ProcessSchemaLocation

            ' Read the XML into an XmlReader...
            Dim XMLtoValidateReader As XmlReader = XmlReader.Create(New StringReader(XMLtoValidate))

            ' Create a reader that will read the document and validate it against the XSD...
            Dim ValidationReader As XmlReader = XmlReader.Create(XMLtoValidateReader, SchemaReaderSettings)

            ' Validate the document...
            Do While ValidationReader.Read()
            Loop

            ValidationReader.Close()

        Catch exXml As XmlException
            schemaError = "Error on line " & exXml.LineNumber & ", "
            schemaError += "Position " & exXml.LinePosition & ", "
            schemaError += exXml.Message
            FailureMessage = schemaError
        Catch exXsd As XmlSchemaException
            schemaError = "Error on line " & exXsd.LineNumber & ", "
            schemaError += "Position " & exXsd.LinePosition & ", "
            schemaError += exXsd.Message
            FailureMessage = schemaError
        Catch ex As Exception
            sqlP.Send("Schema validation exception - " & ex.Message.ToString)
            FailureMessage = schemaError
        End Try

    End Sub

End Class

2 things to note here. I am going to declare a @FailureMessage parameter in my stored procedure that consumes the assembly, so you need to declare the failure message variable to the ValidateXML() method as ByRef.

If you wanted to return the failure message as results to the calling SP then you could uncomment the SqlDataRecord lines referencing the SqlPipe. (I've put it in so you can play around with it if you'd like to...)

After you've compiled the code using the vbc command line utility you'll end up with the assembly (.dll) extension.

To expose this to SQL server you need to run some SQL like this :

CREATE ASSEMBLY YourFunkyClassNameCLR FROM 'C:\YourFunkyClassNameCLR.dll' 
WITH PERMISSION_SET = SAFE;

You should then be able to see it under the "Assembly" section in SQL server management studio.

I have created 2 Stored procedures that reference the exposed assembly. The first consumes the YourFunkyClassNameCLR.dll file.

IF EXISTS (SELECT * FROM dbo.SysObjects WHERE ID = OBJECT_ID(N'[dbo].[CLR_ValidateXMLfromSQL]') AND OBJECTPROPERTY(ID, N'IsProcedure') = 1)
DROP PROCEDURE dbo.CLR_ValidateXMLfromSQL
GO

CREATE PROCEDURE dbo.CLR_ValidateXMLfromSQL(
@XMLtoValidate NVARCHAR(MAX),
@XSD NVARCHAR(MAX),
@FailureMessage NVARCHAR(4000) = '' OUTPUT
)
AS EXTERNAL NAME YourFunkyClassNameCLR.YourFunkyClassName.ValidateXML
GO

I then created another SP to reference this that I will assign permissions to.

IF EXISTS (SELECT * FROM dbo.SysObjects WHERE ID = OBJECT_ID(N'[dbo].[YourSP_Validate_XML]') AND OBJECTPROPERTY(ID, N'IsProcedure') = 1)
DROP PROCEDURE dbo.YourSP_Validate_XML
GO

CREATE PROCEDURE dbo.YourSP_Validate_XML(
 @XMLtoValidate XML, 
 @XSD XML,
 @FailureMessage NVARCHAR(4000) = '' OUTPUT
) 
AS
DECLARE @ErrMsg NVARCHAR(4000), @XML NVARCHAR(MAX), @Schema NVARCHAR(MAX)
	
BEGIN TRY
	IF @XMLtoValidate IS NULL
	BEGIN
		SET @FailureMessage = 'Schema validation failed - no XML was received.'
		RETURN
	END

	IF @XSD IS NULL
	BEGIN
		SET @FailureMessage = 'Schema validation failed - no XSD was received.'
		RETURN
	END

	SET @XML = CONVERT(NVARCHAR(MAX), @XMLtoValidate)
	SET @Schema = CONVERT(NVARCHAR(MAX), @XSD)

	EXEC dbo.CLR_ValidateXMLfromSQL	@XML, @Schema, @FailureMessage OUTPUT
END TRY
BEGIN CATCH
	-- Raise an error with the details of the exception
	SELECT @ErrMsg = ERROR_MESSAGE()
	SELECT @FailureMessage = 'Schema validation failed - ' + @ErrMsg
END CATCH
GO

GRANT EXECUTE ON dbo.YourSP_Validate_XML TO ..., ..., ...
GO

I hope it helps...

Cheers,

Blondebier

 

[philhege]

Great stuff; thanks for the exposition.

Phil H.
Some Bank
-----------
Time's fun when you're having flies.