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”)
Try

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

oReader.Read()
Do While oReader.ReadState <> Xml.ReadState.EndOfFile
sXML &= oReader.ReadOuterXml()
Loop
oReader.Close()
oDom.LoadXml(sXML)

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

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

‘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

Finally

If oConnection.State <> ConnectionState.Closed Then
oConnection.Close()
End If

End Try
End Using

End Sub

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

Advertisements