Save Oracle Query Results to XML File

[Peterg001]

Hi there,

Not sure which section to put this in as it covers VB.NET, ORACLE and XML so here goes.

Say, for example, I have a table in my oracle data base as below:

Table Name = Test
col1, int
col2, varchar2
col3, int

What I would like to do is to run a query in VB that finds me the table name, the columns within it and the datatype of each column and then save that information to an XML file. Once thats done it will then go on to the next table and do the same thing until there are no tables left.

The XML format im after is:

<dataset>
<table>
<name>test</name>
<column>col1</column>
<type>int</type>
<column>col2</column>
<type>varchar2</type>
</table>
<table>
.....
</table>
</dataset>

can anyone provide some example code on how i might do that?

 

[cjard]

Are you sure youre after that format? That relies on the order of the xml tags staying the same

Did you specify that xml? Might wanna try:

<dataset>
 <table name="test">
  <column type="int" name="col1" />
  <column type="varchar2" name="col2" />
 </table>
</dataset>

If your XML really does have to be structured in the way of your post, I'd probably just do this:

Dim sb as New StringBuilder
Dim lastTable As String = ""

sb.Append("<dataset><table>")

'this dataset is a typed one generated from adding a tableadapter with SELECT * FROM USER_TAB_COLS to a dataset connected to your oracle
For Each ro as USER_TAB_COLSRow in myDS.USER_TAB_COLS 
  If lastTable != ro.TABLE_NAME Then
    sb.AppendFormat("</table><table><name>{0}</name>", ro.TABLE_NAME)
  End If

  sb.AppendFormat("<column>{0}</column><type>{1}</type>", ro.COLUMN_NAME, ro.DATA_TYPE)

  lastTable = ro.TABLE_NAME
Next

sb.Replace("<table></table>", "") 'remove empty table tag introduced in first loop iteration

sb.Append("</table></dataset>")

Like I say, really only do this if that XML youve posted is something you have to conform to for a legacy system. If it's something you came up with yourself, you should use something more like mine instead.. Use tag contents for values, attributes for a fixed number of properties about the tag (ie. a column has a name and atype, always..

Could also:
<column>
<name>col1</name>
<type>varchar2</type>
</column>

But it's clumsy

 

[Peterg001]

oh no, it doesnt have to be structured like that. If you think your way is a better way of doing it then ill deffinately look at it as you will probably be right, lol.

If i was to do it your way what code might i use then?

Thanks for your reply by the way!

 

[Peterg001]

Ok, this is the function i have just written based on your example code. Could you have a look as its not writing the String to the tables.xml file.

    Public Function readTables(ByVal m_strOracleConnection As String) As String
        Dim sb As New StringBuilder
        Dim lastTable As String = ""
        Dim strQuery As String

        strQuery = "SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM USER_TAB_COLS"

        Services.postEvent(m_strOracleConnection, "readTables", "Querying database for list of tables.", Services.NotificationType.Notification, True, False)

        Using oraConn As New OracleConnection
            oraConn.ConnectionString = m_strOracleConnection
            Using oraCmd As New OracleCommand(strQuery, oraConn)
                ' Try to open the connection
                Try
                    oraCmd.Connection.Open()
                    Using oraRdr = oraCmd.ExecuteReader()
                        sb.Append("<dataset><table>")

                        For Each ro In oraRdr
                            If lastTable = ro.TABLE_NAME Then
                                sb.AppendFormat("</table><table><name>{0}</name>", ro.TABLE_NAME)
                            End If

                            sb.AppendFormat("<column>{0}</column><type>{1}</type>", ro.COLUMN_NAME, ro.DATA_TYPE)

                            lastTable = ro.TABLE_NAME
                        Next

                        sb.Replace("<table></table>", "")

                        sb.Append("</table></dataset>")

                    End Using
                Catch oraException As OracleException
                    Services.postEvent(m_strOracleConnection, "readTables", "[0090119X] An error occured while querying the database. " & oraException.Message, Services.NotificationType.Critical, True, False)
                Finally
                    MDL.DatabaseAccess.closeConnection(oraConn)
                End Try
            End Using
        End Using
        Services.postEvent(m_strOracleConnection, "readTables", "Table list Acquired.", Services.NotificationType.Notification, True, False)
        Dim filePath As String = New System.IO.FileInfo(Windows.Forms.Application.ExecutablePath).DirectoryName & "/tables.xml"
        Dim writer As New XmlTextWriter(filePath, System.Text.Encoding.UTF8)
        writer.WriteString(sb.ToString)
        Return sb.ToString
    End Function

 

[Peterg001]

Ok ive made some progress on the code now but still have a problem that i need some help with.

So far ive got:

    Public Function readTables(ByVal m_strOracleConnection As String) As String
        Services.postEvent(m_strOracleConnection, "readTables", "Connecting" & m_strOracleConnection.ToString, Services.NotificationType.Notification, True, False)
        Dim sb As New StringBuilder
        Dim lastTable As String = ""
        Dim strQuery As String

        strQuery = "SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM USER_TAB_COLS"

        Services.postEvent(m_strOracleConnection, "readTables", "Querying database for list of tables.", Services.NotificationType.Notification, True, False)

        Using oraConn As New OracleConnection
            oraConn.ConnectionString = m_strOracleConnection
            Using oraCmd As New OracleCommand(strQuery, oraConn)
                ' Try to open the connection
                Try
                    oraCmd.Connection.Open()
                    Using oraRdr = oraCmd.ExecuteReader()
                        Dim allTables As New List(Of String)
                        Dim allColumns As New List(Of String)
                        Dim allDataTypes As New List(Of String)
                        sb.Append("<table>")

                        While oraRdr.Read

                            Dim tableName As String = String.Empty
                            Dim columnName As String = String.Empty
                            Dim dataType As String = String.Empty
                            tableName = oraRdr.GetValue(oraRdr.GetOrdinal("TABLE_NAME")).ToString
                            columnName = oraRdr.GetValue(oraRdr.GetOrdinal("COLUMN_NAME")).ToString
                            dataType = oraRdr.GetValue(oraRdr.GetOrdinal("DATA_TYPE")).ToString

                            sb.AppendFormat("<column name=""{0}"" type=""{1}""></column>", columnName, dataType)

                            allTables.Add(tableName)
                            allColumns.Add(columnName)
                            allDataTypes.Add(dataType)
                        End While
                        sb.Append("</table>")
                    End Using
                Catch oraException As OracleException
                    Services.postEvent(m_strOracleConnection, "readTables", "[0090119X] An error occured while querying the database. " & oraException.Message, Services.NotificationType.Critical, True, False)
                Finally
                    MDL.DatabaseAccess.closeConnection(oraConn)
                End Try
            End Using
        End Using
        Services.postEvent(m_strOracleConnection, "readTables", "Table list Acquired.", Services.NotificationType.Notification, True, False)
        Dim filePath As String = New System.IO.FileInfo(Windows.Forms.Application.ExecutablePath).DirectoryName & "\tables.xml"
        Using writer As New XmlTextWriter(filePath, System.Text.Encoding.UTF8)
            writer.Formatting = Formatting.Indented
            writer.WriteStartDocument()
            writer.WriteStartElement("dataset")
            writer.WriteRaw(sb.ToString)
            writer.WriteEndElement()
        End Using
        Return Nothing
    End Function

This code produces the following XML file:

<?xml version="1.0" encoding="utf-8" ?> 
<dataset>
<table>
  <column name="POL_EXP_DATE" type="DATE" /> 
  <column name="POL_CANC_DATE" type="DATE" /> 
  <column name="RISK_NO" type="NUMBER" />
</table>
</dataset>

Problem is i need a new <table> element for each new table which it isnt doing. From the code above POL_EXP_DATE and POL_CANC_DATE are in the same table but RISK_NO is in another table and i cant figure out how to create a new table element from each table.

I can change the code so that it produces the table name for each column but i only want one table name for every column in the table

 

[cjard]

In my original code block:

Dim lastTable as String = ""
For Each ro as USER_TAB_COLSRow in myDS.USER_TAB_COLS 
  [B]If lastTable != ro.TABLE_NAME Then
    sb.AppendFormat("</table><table><name>{0}</name>", ro.TABLE_NAME)
  End If[/B]
  sb.AppendFormat("<column>{0}</column><type>{1}</type>", ro.COLUMN_NAME, ro.DATA_TYPE)

  lastTable = ro.TABLE_NAME
Next

The bit in bold did this. The idea is that you order by your results on the table name, then youre looping forward over the results..

Every time the table name changes, you write a table tag. To achieve knowledge of when a table name changes you have to remember what it was last time (last loop) and compare it with this time. Look again at my loop and work out how it does this..

 

[cjard]

ps; why make your coding life so hard with all the DataReader and List objects? Just use the modern methods of TableAdapter and DataTables:

Add a dataset to your project
Open it, right click the surface, choose Add Query
Connect your database, specify the query (SELECT * FROM user_tab_cols), finish
Then in code:

Dim ta as New USER_TAB_COLSTableAdapter
Dim ds as New YourDataSet
ta.Fill(ds)

For Each ro as USER_TAB_COLSRow in ds.USER_TAB_COLS
...
Next

Your 100 lines of code became more like 10 using this way

If youre using VB Express Free edition, you cannot use the visual designer to link to Oracle. In this case, your code would be better:

Dim da as New DataAdapter(...sql command/db connection go here...)
Dim dt as New DataTable
da.Fill(dt)

ForEach ro as DataRow in dt.Rows
  If ro("TABLE_NAME") <> ...
Next

When using typed datatable (first way) we say:

row.COLUMN_NAME ' COLUMN_NAME is a vb Property that gets us the value in that column, as string, int, etc

When using untyped tables (the second way) we say:

ro("COLUMN_NAME") 'just pass a string, and get an Object back (untyped) hence often have to cast to make it useful

-
If youre going to use an XmlWriter you might as well use it propertly and fully in your loop, adding elements and attributes.. No point building up a big string of XML and writing it in raw - either do the whole thing in string, or whole thing using xml writer..

 

[Peterg001]

Thanks for your help there. Looks like its sorted now, works perfectly. If anyone else is interested here's the code I used below:

    Public Function getTableData(ByVal m_strOracleConnection As String) As String
        sb.Remove(0, sb.Length)
        Dim lastTable As String = ""
        Dim strQuery As String

        strQuery = "SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM USER_TAB_COLS"

        Services.postEvent(m_strOracleConnection, "getTableData", "Querying database for list of tables.", Services.NotificationType.Notification, True, False)

        Using oraConn As New OracleConnection
            oraConn.ConnectionString = m_strOracleConnection
            Using oraCmd As New OracleCommand(strQuery, oraConn)
                ' Try to open the connection
                Try
                    oraCmd.Connection.Open()
                    Using oraRdr = oraCmd.ExecuteReader()
                        Dim allTables As New List(Of String)
                        Dim allColumns As New List(Of String)
                        Dim allDataTypes As New List(Of String)
                        sb.Append("<table>")

                        While oraRdr.Read
                            Dim tableName As String = String.Empty
                            Dim columnName As String = String.Empty
                            Dim dataType As String = String.Empty
                            tableName = oraRdr.GetValue(oraRdr.GetOrdinal("TABLE_NAME")).ToString
                            columnName = oraRdr.GetValue(oraRdr.GetOrdinal("COLUMN_NAME")).ToString
                            dataType = oraRdr.GetValue(oraRdr.GetOrdinal("DATA_TYPE")).ToString

                            allTables.Add(tableName)
                            allColumns.Add(columnName)
                            allDataTypes.Add(dataType)

                            If lastTable = tableName Then
                                sb.AppendFormat("")
                            Else
                                sb.AppendFormat("</table><table name=""{0}"">", tableName)
                            End If
                            sb.AppendFormat("<column name=""{0}"" type=""{1}""></column>", columnName, dataType)
                            lastTable = tableName
                        End While

                        sb.Replace("<table></table>", "")
                        sb.Append("</table>")
                    End Using
                Catch oraException As OracleException
                    Services.postEvent(m_strOracleConnection, "getTableData", "[0090120X] An error occured while querying the database. " & oraException.Message, Services.NotificationType.Critical, True, False)
                Finally
                    MDL.DatabaseAccess.closeConnection(oraConn)
                End Try
            End Using
        End Using
        Services.postEvent(m_strOracleConnection, "getTableData", "Table list successfully acquired.", Services.NotificationType.Notification, True, False)
        Return sb.ToString
    End Function

 

[cjard]

Why, if the table name is the same, do you concatenate nothing?

If lastTable = tableName Then
                                sb.AppendFormat("")
                            Else
                                sb.AppendFormat("</table><table name=""{0}"">", tableName)
                            End If

Why not just say:

If lastTable <> tableName Then
  sb.AppendFormat("</table><table name=""{0}"">", tableName)
End If

Some people adopt the strangest coding conventions..

Other things you can do:
You populate the List(Of String)s, but then never use them?
Why not get the ordinal of the columns just once, instead of every loop pass?

Your code, condensed:

Using oraRdr = oraCmd.ExecuteReader()
                        Dim tableName As String = String.Empty
                        Dim columnName As String = String.Empty
                        Dim dataType As String = String.Empty
Dim TN as Integer = oraRdr.GetOrdinal("TABLE_NAME")
Dim CN as Integer = oraRdr.GetOrdinal("COLUMN_NAME")
Dim DT as Integer = oraRdr.GetOrdinal("DATA_TYPE")

                        sb.Append("<table>")

                        While oraRdr.Read

                            tableName = oraRdr.GetValue(TN).ToString
                            columnName = oraRdr.GetValue(CN).ToString
                            dataType = oraRdr.GetValue(DT).ToString

                            If lastTable <> tableName Then
                                sb.AppendFormat("</table><table name=""{0}"">", tableName)
                            End If

                            sb.AppendFormat("<column name=""{0}"" type=""{1}""></column>", columnName, dataType)

                            lastTable = tableName
                        End While

                        sb.Replace("<table></table>", "")
                        sb.Append("</table>")
                    End Using

 

[Peterg001]

Im a beginner so not sure of the best way to do things yet, but im learning. Thanks for checking out my code though.

My objective was to get it working then tidy up and make improvements afterwards so ill deffinately take a look at your code and incorporate it.

Thanks for your help on this, its much appreciated.