Thursday, May 1, 2008

Oracle Data Reader as XML Document

Sometime you just want to return some data from the DB to client in XML format. Here is my Function to return an XML doc.

Public Function GetReaderAsXML(ByVal sql As String, ByVal Environment As String, Optional ByRef OtherOraConnection As OracleConnection = Nothing) As XmlDocument

Dim cmdfunc As New OracleCommand

If OtherOraConnection Is Nothing Then
If Not oraConnection.State = ConnectionState.Open Then
Me.ConnectToOracle(Environment)
End If
cmdfunc.Connection = oraConnection
Else
cmdfunc.Connection = OtherOraConnection
End If

cmdfunc.CommandText = sql
cmdfunc.XmlCommandType = OracleXmlCommandType.Query
cmdfunc.BindByName = True
cmdfunc.XmlQueryProperties.MaxRows = -1

Dim XmlReaderOut As System.Xml.XmlReader
XmlReaderOut = cmdfunc.ExecuteXmlReader
Dim XmlDocOut As New System.Xml.XmlDocument

XmlDocOut.PreserveWhitespace = True
XmlDocOut.Load(XmlReaderOut)

Return XmlDocOut

End Function
Add to Google