TSQL: importing XML data using OPENROWSET

CREATE TABLE xml_documents( x XML );

 INSERT INTO xml_documents ( x )

   SELECT * FROM OPENROWSET(BULK N’myXmlFile.xml’, SINGLE_BLOB) AS x;

GO

Advertisements

read a xml into SQL-Server

–temp table variable

DECLARE @tmpXML TABLE (XmlColumn xml)

–import xml-file into SQL-Server

INSERT INTO @tmpXML(XmlColumn)

SELECT * FROM OPENROWSET(

BULK ‘C:\Daten\media\list.xml’,

SINGLE_BLOB) AS x;

–query  of xml-node ‘filename’ reads all nodes ‘filename’

WITH XMLNAMESPACES (‘http://winscp.net/schema/session/1.0’ as w)

INSERT INTO @Datei(Dateiname)

SELECT T.c.value(‘@value’, ‘varchar(500)’)

FROM @tmpXML

CROSS APPLY XmlColumn.nodes(‘//w:filename’) T(c);

IF EXISTS(SELECT * FROM @Datei WHERE Dateiname = @EAN + ‘.jpg’)

SET @Bild1 = @EAN + ‘.jpg’ ELSE SET @Bild1 = ”

CLR: strip discouraged characters from a string for xml vb.net

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class UserDefinedFunctions
”’ <summary>
”’ removes discouraged characters
”’ obsolete, sobald LogicBase.CLR überall aktualisiert wurde => Version > 1.0.3804.21154
”’ </summary>
”’ <param name=”Content”>string for xml</param>
”’ <returns>clean SqlString</returns>
”’ <remarks>
”’ Response is not well-formed XML System.Xml.XmlException XML System.Xml.XmlException , hexadecimal value 0x2, is an invalid character.
”’ see http://www.w3.org documentation:
”’ </remarks>
<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function udfLBXMLRemoveDiscouragedCharacters(ByVal Content As String) As SqlString
Dim result As New System.Text.StringBuilder()
‘current character
Dim cC As Char
If Content Is Nothing OrElse Content = String.Empty Then
Return String.Empty
End If
For i As Integer = 0 To Content.Length – 1
cC = Content(i)
If (AscW(cC) = &H9 OrElse AscW(cC) = &HA OrElse AscW(cC) = &HD) _
OrElse ((AscW(cC) >= &H20) AndAlso (AscW(cC) <= &HD7FF)) _
OrElse ((AscW(cC) >= &HE000) AndAlso (AscW(cC) <= &HFFFD)) _
OrElse ((AscW(cC) >= &H10000) AndAlso (AscW(cC) <= &H10FFFF)) Then
result.Append(cC)
End If
Next
Return New SqlString(result.ToString())
End Function
End Class

Imports SystemImports System.DataImports System.Data.SqlClientImports System.Data.SqlTypesImports Microsoft.SqlServer.Server

Partial Public Class UserDefinedFunctions    ”’ <summary>    ”’ removes discouraged characters     ”’ obsolete, sobald LogicBase.CLR überall aktualisiert wurde => Version > 1.0.3804.21154    ”’ </summary>    ”’ <param name=”Content”>string for xml</param>    ”’ <returns>clean SqlString</returns>    ”’ <remarks>    ”’ Response is not well-formed XML System.Xml.XmlException XML System.Xml.XmlException , hexadecimal value 0x2, is an invalid character.    ”’ see http://www.w3.org documentation:    ”’ http://www.w3.org/TR/2008/REC-xml-20081126/#charsets    ”’ http://www.w3.org/TR/xml11/#charsets    ”’ </remarks>    <Microsoft.SqlServer.Server.SqlFunction()> _    Public Shared Function udfLBXMLRemoveDiscouragedCharacters(ByVal Content As String) As SqlString
Dim result As New System.Text.StringBuilder()
‘current character        Dim cC As Char
If Content Is Nothing OrElse Content = String.Empty Then            Return String.Empty        End If
For i As Integer = 0 To Content.Length – 1
cC = Content(i)
If (AscW(cC) = &H9 OrElse AscW(cC) = &HA OrElse AscW(cC) = &HD) _             OrElse ((AscW(cC) >= &H20) AndAlso (AscW(cC) <= &HD7FF)) _             OrElse ((AscW(cC) >= &HE000) AndAlso (AscW(cC) <= &HFFFD)) _             OrElse ((AscW(cC) >= &H10000) AndAlso (AscW(cC) <= &H10FFFF)) Then                result.Append(cC)            End If
Next
Return New SqlString(result.ToString())
End FunctionEnd Class

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)
Else
oResult.Add(import.Namespace, String.Empty)
End If
End If
If Not oExternal.Schema Is Nothing Then
RecurseExternalSchemas(oExternal.Schema)
End If
Next
RecurseExternalSchemas = oResult
End Function

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

a recursive walk through XPathNavigator

Sub RecursiveWalkThroughXpath(ByVal oNavigator As XPathNavigator)
Select Case oNavigator.NodeType
Case XPathNodeType.Element
If oNavigator.Prefix = String.Empty Then
Console.WriteLine(“<{0}>”, oNavigator.LocalName)
Else
Console.Write(“<{0}:{1}>”, oNavigator.Prefix, oNavigator.LocalName)
Console.WriteLine(vbTab + oNavigator.NamespaceURI)
End If
Case XPathNodeType.Text
Console.WriteLine(vbTab + oNavigator.Value)
End Select

If oNavigator.MoveToFirstChild() Then
Do
RecursiveWalkThroughXpath(oNavigator)
Loop While (oNavigator.MoveToNext())

oNavigator.MoveToParent()
If (oNavigator.NodeType = XPathNodeType.Element) Then
Console.WriteLine(“</{0}>”, oNavigator.Name)
End If
Else
If oNavigator.NodeType = XPathNodeType.Element Then
Console.WriteLine(“</{0}>”, oNavigator.Name)
End If
End If
End Sub

..:: 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”)
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. ::..

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: “)
Console.WriteLine(e.Message)
ElseIf e.Severity = XmlSeverityType.Error Then
Console.Write(“ERROR: “)
Console.WriteLine(e.Message)
End If
End Sub

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