Is There Better Way to Validate SqlXml in CLR?

Is There Better Way to Validate SqlXml in CLR?

I need to validate xml in sql server against an xsd. The xsd is too
complex to use with XML SCHEMA COLLECTION, so I am writing a SQL CLR
function to do it. I have two issue with how I have "had" to write the
code. Code is VB.NET targeting 2.0, btw, thought I think I run into the
same issue in C#. If not happy to switch.
[1] I cannot seem to attach setting to the reader created by
SqlXml.CreateReader so I have to load into an XmlDocument to perform the
validation. I also cannot just load the SqlXml straight to XmlDocument--I
would have to do extra type conversions.
Am I missing something or is that just the way it is?
[2] I hate that I am using a shared member to pass the validation result
from the event handler and then back to the caller. This is fine in my
first specific usage where I no there is a sequence of individual calls.
But if I ever tried using this with multiple callers or in set operation I
fear the results would be suspect.
Is there a way around this?
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Xml
Imports System.Xml.Schema
Partial Public Class UserDefinedFunctions
'this is from an ms sample
Const TARGET_NAMESPACE As String = "http://www.contoso.com/books"
Const SCHEMA_URI As String = "D:\temp\temp.xsd"
Shared schemaSet As XmlSchemaSet
Shared schemaValidationEventHandler As ValidationEventHandler
Shared isValid As Boolean
Shared doc As XmlDocument
Shared Sub New()
schemaSet = New XmlSchemaSet
schemaSet.Add(TARGET_NAMESPACE, SCHEMA_URI)
schemaSet.Compile()
doc = New XmlDocument
doc.Schemas = schemaSet
schemaValidationEventHandler = New ValidationEventHandler(
AddressOf ValidationCallBack)
End Sub
<SqlFunction()> _
Public Shared Function ValidateWithContosoXsd(ByVal xml As SqlXml) _
As SqlBoolean
isValid = True
Dim reader As XmlReader = xml.CreateReader
reader.Settings.ValidationType = ValidationType.Schema
doc.Load(reader)
doc.Validate(schemaValidationEventHandler)
ValidateWithContosoXsd = isValid
End Function
Private Shared Sub ValidationCallBack(ByVal sender As Object,
ByVal args As ValidationEventArgs)
isValid = False
End Sub
End Class
I did follow a clue to try using the initial reader as the basis a for a
second reader, which works in a roughly equivalent console app. The
difference in the console app is that the first ready is from a file uri
instead of from SqlXml. Sadly, this always shows valid when done in the
clr.
Partial Public Class UserDefinedFunctions
Const TARGET_NAMESPACE As String = "http://www.contoso.com/books"
Const SCHEMA_URI As String = "D:\temp\temp.xsd"
Shared settings As XmlReaderSettings
Shared schemaSet As XmlSchemaSet
Shared schemaValidationEventHandler As ValidationEventHandler
Shared isValid As Boolean
Shared Sub New()
schemaSet = New XmlSchemaSet
schemaSet.Add(TARGET_NAMESPACE, SCHEMA_URI)
schemaSet.Compile()
schemaValidationEventHandler = New
ValidationEventHandler(AddressOf ValidationCallBack)
settings = New XmlReaderSettings
settings.Schemas = schemaSet
settings.ValidationType = ValidationType.Schema
AddHandler settings.ValidationEventHandler,
schemaValidationEventHandler
End Sub
<SqlFunction()> _
Public Shared Function ValidateWithContosoXsd(ByVal xml As SqlXml) As
SqlBoolean
isValid = True
Dim baseReader As XmlReader = xml.CreateReader
Dim reader As XmlReader = XmlReader.Create(baseReader, settings)
While reader.Read()
End While
ValidateWithContosoXsd = isValid
End Function
Private Shared Sub ValidationCallBack(ByVal sender As Object, ByVal
args As ValidationEventArgs)
isValid = False
End Sub
End Class