get imported namespaces of a schema and their schema location

''' <summary>
''' get all imported namespaces of a schema and their schema location
''' </summary>
''' <param name="oSchema">schmema</param>
''' <returns>Dictionary(imported namespace, schema location) </returns>
''' <remarks></remarks>
Function RecurseExternalSchemas(ByVal oSchema As XmlSchema) As Dictionary(Of String, String)
Dim oResult As New Dictionary(Of String, String)
For Each oExternal As XmlSchemaExternal In oSchema.Includes
If oExternal.GetType() Is GetType(XmlSchemaImport) Then
Dim import As XmlSchemaImport = CType(oExternal, XmlSchemaImport)
If Not oExternal.SchemaLocation = Nothing Then
oResult.Add(import.Namespace, oExternal.SchemaLocation)
oResult.Add(import.Namespace, String.Empty)
End If
End If
If Not oExternal.Schema Is Nothing Then
End If
RecurseExternalSchemas = oResult
End Function

..:: Whereever you go, stay in touch. Download toolbar now! It´s free, private and secure. ::..

CLR stored procedure to create a external schema (xsd) or a xml file including schema from a SQL Server table

<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub spLBgenerateInstallscript( _
ByVal sTablename As String, _
<Out()> ByRef iReturnvalue As SqlInt32)

Dim oCommand As SqlCommand
Dim oDom As New Xml.XmlDocument
Dim oDom2 As New Xml.XmlDocument
Dim oReader As Xml.XmlReader
Dim sXML As String = String.Empty
Dim oSB As New StringBuilder
Dim sOutputfilename As String

Using oConnection As New SqlConnection(“context connection=true”)

oCommand = oConnection.CreateCommand
oCommand.CommandText = “SELECT * FROM ” & sTablename & ” FOR XML AUTO, TYPE, XMLSCHEMA, ELEMENTS, ROOT (‘” & sTablename & “‘)”
oCommand.CommandType = CommandType.Text
oReader = oCommand.ExecuteXmlReader

Do While oReader.ReadState <> Xml.ReadState.EndOfFile
sXML &= oReader.ReadOuterXml()

Dim schema As Xml.XmlNode = oDom.ChildNodes(0).FirstChild

Dim mynode As Xml.XmlNode = oDom2.ImportNode(schema, True)

‘write schema only
sOutputfilename = “C:\” & sTablename & “_” & String.Format(“{0:” & “yyyyMMddHHmmss” & “}”, Now)
oDom2.Save(sOutputfilename & “.xsd”)

‘write xml with inline schema
oDom.Save(sOutputfilename & “.xml”)

iReturnvalue = 0

Catch SQLex As Exception

#If DEBUG Then
EmitDebugMessage(“spLBgenerateInstallscript exeption SQLex: ” & SQLex.Message)
#End If
iReturnvalue = 1


If oConnection.State <> ConnectionState.Closed Then
End If

End Try
End Using

End Sub

..:: Whereever you go, stay in touch. Download toolbar now! It´s free, private and secure. ::..

sample XmlReader Validation Handler

”’ <summary>
”’ XmlReader Validation Handler
”’ </summary>
”’ <param name=”sender”></param>
”’ <param name=”e”></param>
”’ <remarks>not used, not ready</remarks>
Sub oXmlReaderSettingsValidationEventHandler(ByVal sender As Object, ByVal e As ValidationEventArgs)
If e.Severity = XmlSeverityType.Warning Then
Console.Write(“WARNING: “)
ElseIf e.Severity = XmlSeverityType.Error Then
Console.Write(“ERROR: “)
End If
End Sub

..:: Whereever you go, stay in touch. Download toolbar now! It´s free, private and secure. ::..

validate XML File against XSD File

”’ <summary>
”’ validates XML File against XSD File
”’ </summary>
”’ <param name=”sXmlFile”>xml data</param>
”’ <param name=”sXSDFile”>external xml schema</param>
”’ <returns>true no errors, false errors</returns>
”’ <remarks></remarks>
Public Function ValidateXMLFileExternalXSD(ByVal sXmlFile As String, ByVal sXSDFile As String)
Dim oXmlReaderSettings As XmlReaderSettings = New XmlReaderSettings()
oXmlReaderSettings.Schemas.Add(“;, sXSDFile)
AddHandler oXmlReaderSettings.ValidationEventHandler, New ValidationEventHandler(AddressOf oXmlReaderSettingsValidationEventHandler)
oXmlReaderSettings.ValidationFlags = oXmlReaderSettings.ValidationFlags And XmlSchemaValidationFlags.ReportValidationWarnings
oXmlReaderSettings.ValidationType = ValidationType.Schema
Dim reader As XmlReader = XmlReader.Create(sXmlFile, oXmlReaderSettings)
Dim oDom As New XmlDocument
Catch ex As XmlException
Console.WriteLine(“XmlDocumentValidationExample.XmlException: {0}”, ex.Message)
Return False
Catch ex As XmlSchemaValidationException
Console.WriteLine(“XmlDocumentValidationExample.XmlSchemaValidationException: {0}”, ex.Message)
Return False
Catch ex As Exception
Console.WriteLine(“XmlDocumentValidationExample.Exception: {0}”, ex.Message)
Return False

End Try
Return True
End Function

..:: Whereever you go, stay in touch. Download toolbar now! It´s free, private and secure. ::..