Solved

Data Transformation

Posted on 2004-04-18
25
204 Views
Last Modified: 2010-04-24
How can we import data from an XML Document into a table in oracle using VB.NET ?
0
Comment
Question by:Pavanpvr
25 Comments
 

Author Comment

by:Pavanpvr
ID: 10852243
Dear Friends

I have converted an Access Table as an XML document. And I want the contents of that XML Document (which is nothing but a table ) to be stored in a Table in Oracle using VB.NET.

Can u just help me in this regard

With Regards

Pavanpvr
0
 
LVL 4

Expert Comment

by:Jarodtweiss
ID: 10852314
You can load the data into a DataSet using
dim myDs as new DataSet()
myDs.ReadXML(yourXml)

and then you can configure a DataAdapter to point to your Oracle database and use your dataset to populate a table
0
 

Author Comment

by:Pavanpvr
ID: 10852391
I can read the data from an XML Document and i can display it on to the console as well for verification. What I need is i want the data to be inserted into the table in oracle.
0
 
LVL 6

Expert Comment

by:zulu_11
ID: 10859355
Since you have written the code to iterate through the XML document and print it on the console..all you  need to do now is

1. Make an Oracle Connection Object, Connect to the db using the connectionstring,,,userid, password etc.

2. Use the Oracle Command Object and Use it CommandText property to insert the records one-by-one in the table.

3. Excute the command

Run the above loop till the time all the records have been inserted just like the way you are showing the records on the console.

Regards

Zulu

Zulu
0
 

Author Comment

by:Pavanpvr
ID: 10859855
I tried it ut could not make it. If u dont mind can u just give the code for it .


Regards
Pavan
0
 
LVL 6

Expert Comment

by:zulu_11
ID: 10865790
post you code and i'll add the Oracle part to it..

Regards

Zulu
0
 

Author Comment

by:Pavanpvr
ID: 10865906
Sure I will post the code

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        ' Connection1 for ORACLE
        Me.OleDbConnection1.Open()
        ' Connection2 for ACCESS
        'Me.OleDbConnection2.Open()
        MsgBox("Connected To Database")
        Me.OleDbDataAdapter4.Fill(Me.DataSet4)
        Dim myReader As OleDb.OleDbDataReader
         Me.DataSet4.WriteXml("e:\test002.xml")
        Try
            MessageBox.Show("Copied The Contents", "", MessageBoxButtons.OK)
        Catch ex As OleDb.OleDbException
            MsgBox(ex.Message)
        Finally
            Me.OleDbConnection1.Close()
        End Try
    End Sub


    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Me.Dispose()
    End Sub
    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim reader1 As New Xml.XmlTextReader("e:\test002.xml")
        Dim reader10 As New Xml.XmlTextReader("e:\test002.xml")
        Dim XmlNodeType As Xml.XmlNodeType '= textreader.NodeType
        Try
            While reader1.Read()
                reader1.MoveToElement()
                If reader1.HasValue Then
                    reader1.MoveToElement()
                End If
                reader1.ReadAttributeValue()
                Console.Write(" " + reader1.Value + " ") '+ reader1.Name)
                Console.Write(reader1.Name)

                Do While (reader10.Read())
                    Select Case reader10.NodeType
                        Case XmlNodeType.Element ' The node is an Element
                            Console.Write("<" + reader10.Name)
                            While (reader10.MoveToNextAttribute())
                                Console.Write(" {0}='{1}'", reader10.Name, reader10.Value)

                                ' Read attributes
                                Console.Write(" " + reader10.Name + "='" + reader10.Value + "'")
                            End While
                            Console.Write(">")
                        Case XmlNodeType.DocumentType ' The node is a DocumentType
                            Console.WriteLine("<" & reader10.Name & ">") '& reader10.Value)
                    End Select
                Loop
            End While
            reader1.Close()
            reader10.Close()

            MessageBox.Show("copied The Contents on to Console", " ", MessageBoxButtons.OK)

        Catch ex1 As OleDb.OleDbException
            MsgBox(ex1.Message)
        End Try
    End Sub


I made some modifications to this also
u better check it and re write the code
Please do it

Regards

Pavan
0
 
LVL 6

Expert Comment

by:zulu_11
ID: 10867173
OK! so this is what i have done..i've made a small test xml file having the following contents..

<root>
      <record id="2" name="jim"/>
      <record id="3" name="tim"/>
</root>
--------------------------------------------------------------------------------
then i used the  OleDBConnection Object which is under Data Objects in the Toolbox of the VS IDE..
this makes a small object at the bottom of the design view ..right click on and click on properties..
In the properties dialog box click on the Connection String row and choose the <New Connection> option from the drop down list...
A new dialog box appears in which you should choose the  Microsoft OLE DB Provider for Oracle Db from the Provider Tab and then in the Connection tab give the details about the Oracle server and the user/passowrd..And test the connection..Once this is done..come to the code behind..
----------------------------------------------------------------------------------
What i done is used the XmlDocument Object to Read the document, Select the Nodes and then iterate throguh them...i'm not familar with the XmlReader Class..
--------------------------------------------------
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim xmlDoc As XmlDocument
        Dim tNode As XmlNode
        Dim oleCmd As OleDb.OleDbCommand
        Dim ConnString As String = "Provider=OraOLEDB;Data Source=kndevdb;Integrated Security=false;User ID=knadmin;Password=knadmin"
        Try
            xmlDoc = New XmlDocument()
            xmlDoc.Load("c:\exOra.xml")
            OleDbConnection1.Open() <----------Open the Connection
            oleCmd = New OleDb.OleDbCommand()
            oleCmd.Connection = OleDbConnection1 <------Assoc the Connection with the Command
            For Each tNode In xmlDoc.SelectNodes("//record")
                '' Create the Command
                oleCmd.CommandText = "Insert into test_rohit values( "
                oleCmd.CommandText += tNode.Attributes("id").Value
                oleCmd.CommandText += " ,"
                oleCmd.CommandText += "'" & tNode.Attributes("name").Value & "'"
                oleCmd.CommandText += ")"
                oleCmd.ExecuteNonQuery() <-- Excute the Command
                oleCmd.CommandText = ""  <- Cleatr the Command For the next loop..
            Next
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            If Not xmlDoc Is Nothing Then
                xmlDoc = Nothing
            End If
            If Not OleDbConnection1 Is Nothing Then
                OleDbConnection1.Close()
                OleDbConnection1.Dispose()
                OleDbConnection1 = Nothing
            End If
            If Not oleCmd Is Nothing Then
                oleCmd.Dispose()
                oleCmd = Nothing
            End If
        End Try
End Sub
----------------------------
it's not important for you to use the XMLDocument Object...the important thing is that you should understand the use of the OleDBCommand object..if you get that then it's a Walk in the park.,..

Hope this helps..

Regards

Zulu
0
 

Author Comment

by:Pavanpvr
ID: 10875710
Its not working
Can u make some other modifications to it and give to me

Regards
Pavan
0
 
LVL 6

Expert Comment

by:zulu_11
ID: 10875733
send me you XML and the complete details of which all fields you want to go in which table in Oracle..
0
 

Author Comment

by:Pavanpvr
ID: 10875765
This is the XML which I have genereated using the program from an Microsoft Access Table called "EMPL". The fields i want to insert into ORACLE are

dept
ename
eno

In ORACLE I have created a table by name EMPL with the same fields. I just want the data to get into oracle table.


THE  XML IS BELOW:


 <?xml version="1.0" standalone="yes" ?>
- <NewDataSet>
- <empl>
  <dept>COMPUTERS</dept>
  <ename>PAVAN</ename>
  <eno>10</eno>
  </empl>
- <empl>
  <dept>MAINFRAMES</dept>
  <ename>RAVI</ename>
  <eno>20</eno>
  </empl>
- <empl>
  <dept>Marketing</dept>
  <ename>Phani</ename>
  <eno>30</eno>
  </empl>
- <empl>
  <dept>Marketing</dept>
  <ename>Vamsi</ename>
  <eno>40</eno>
  </empl>
- <empl>
  <dept>Hardware</dept>
  <ename>Louis</ename>
  <eno>50</eno>
  </empl>
- <empl>
  <dept>Hardware</dept>
  <ename>Shankar</ename>
  <eno>60</eno>
  </empl>
- <empl>
  <dept>Hardware</dept>
  <ename>Narayana</ename>
  <eno>70</eno>
  </empl>
- <empl>
  <dept>Project Leader</dept>
  <ename>Badri Narayana</ename>
  <eno>80</eno>
  </empl>
- <empl>
  <dept>Project Leader</dept>
  <ename>Badri</ename>
  <eno>90</eno>
  </empl>
- <empl>
  <dept>Programmer</dept>
  <ename>Siva</ename>
  <eno>100</eno>
  </empl>
- <empl>
  <dept>Programmer</dept>
  <ename>Said</ename>
  <eno>110</eno>
  </empl>
- <empl>
  <dept>Medical</dept>
  <ename>Murali</ename>
  <eno>120</eno>
  </empl>
- <empl>
  <dept>MAINTENANCE</dept>
  <ename>NAGESH</ename>
  <eno>123</eno>
  </empl>
- <empl>
  <dept>MAINFRAME</dept>
  <ename>MAITHILI</ename>
  <eno>125</eno>
  </empl>
- <empl>
  <dept>COMPUTERS</dept>
  <ename>HARITHA</ename>
  <eno>126</eno>
  </empl>
- <empl>
  <dept>COMPUTERS</dept>
  <ename>MIHIR</ename>
  <eno>150</eno>
  </empl>
- <empl>
  <dept>COMPUTERS</dept>
  <ename>KIRAN</ename>
  <eno>140</eno>
  </empl>
- <empl>
  <dept>MAINTENANCE</dept>
  <ename>PRAVEENA</ename>
  <eno>130</eno>
  </empl>
- <empl>
  <dept>MAINFRAMES</dept>
  <ename>MUKUNDA</ename>
  <eno>121</eno>
  </empl>
- <empl>
  <dept>ACCOUNTS</dept>
  <ename>PRASHANT</ename>
  <eno>122</eno>
  </empl>
- <empl>
  <dept>EEE</dept>
  <ename>RAVALI</ename>
  <eno>124</eno>
  </empl>
- <empl>
  <dept>PROGRAMMER</dept>
  <ename>PRASANNA</ename>
  <eno>127</eno>
  </empl>
- <empl>
  <dept>DBA</dept>
  <ename>RADYA</ename>
  <eno>128</eno>
  </empl>
- <empl>
  <dept>MAINTENANCE</dept>
  <ename>PRAMOD</ename>
  <eno>129</eno>
  </empl>
- <empl>
  <dept>MAINTANANCE</dept>
  <ename>ABDOO</ename>
  <eno>131</eno>
  </empl>
- <empl>
  <dept>HARDWARE</dept>
  <ename>AHMED</ename>
  <eno>132</eno>
  </empl>
- <empl>
  <dept>ACCOUNTS</dept>
  <ename>KASIM</ename>
  <eno>133</eno>
  </empl>
- <empl>
  <dept>ADMIN</dept>
  <ename>JOSEPH</ename>
  <eno>134</eno>
  </empl>
- <empl>
  <dept>BOOL</dept>
  <ename>PUROSHATAM</ename>
  <eno>135</eno>
  </empl>
- <empl>
  <dept>BOOL</dept>
  <ename>SANEESH</ename>
  <eno>136</eno>
  </empl>
- <empl>
  <dept>BOOL</dept>
  <ename>CHITTAPPA</ename>
  <eno>137</eno>
  </empl>
- <empl>
  <dept>FINANCE</dept>
  <ename>JOHN</ename>
  <eno>175</eno>
  </empl>
- <empl>
  <dept>TTT</dept>
  <ename>DDD</ename>
  <eno>501</eno>
  </empl>
  </NewDataSet>
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 6

Expert Comment

by:zulu_11
ID: 10876383
OK! here's the final code..now you have make the Connection Object  to the oracle db like i said in my previous post. and the rest is done as i said in my previous post..
i saved your xml in c:\test.xml file and used that file to make the different command text for each insertion
---------------
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim xmlDoc As XmlDocument
        Dim tNode As XmlNode
        Dim oleCmd As OleDb.OleDbCommand
        Try
            xmlDoc = New XmlDocument()
            xmlDoc.Load("c:\test.xml")
            OleDbConnection1.Open()
            oleCmd = New OleDb.OleDbCommand()
            oleCmd.Connection = OleDbConnection1
            For Each tNode In xmlDoc.SelectNodes("//empl")
                oleCmd.CommandText = "Insert into EMPL(dept,ename,eno) values( "
                oleCmd.CommandText += "'" & tNode.Item("dept").InnerText & "'"
                oleCmd.CommandText += " ,"
                oleCmd.CommandText += "'" & tNode.Item("ename").InnerText & "'"
                oleCmd.CommandText += " ,"
                oleCmd.CommandText += tNode.Item("eno").InnerText
                oleCmd.CommandText += ")"
                'The Command text at this point would be like this..
                '  "Insert into EMPL(dept,ename,eno) values( 'COMPUTERS' ,'PAVAN' ,10)"
                oleCmd.ExecuteNonQuery()
                oleCmd.CommandText = ""
            Next
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            If Not xmlDoc Is Nothing Then
                xmlDoc = Nothing
            End If
            If Not OleDbConnection1 Is Nothing Then
                OleDbConnection1.Close()
                OleDbConnection1.Dispose()
                OleDbConnection1 = Nothing
            End If
            If Not oleCmd Is Nothing Then
                oleCmd.Dispose()
                oleCmd = Nothing
            End If
        End Try
    End Sub
---------------------------------------
Zulu
0
 

Author Comment

by:Pavanpvr
ID: 10876638
Zulu thanx for ur assistance
I could make it at last
Thank You

Regards

Pavan
0
 
LVL 6

Expert Comment

by:zulu_11
ID: 10877249
No problemo...now how about closing the question ..
0
 

Author Comment

by:Pavanpvr
ID: 11020774
Hai Zulu,

the concept of transfering data from Oracle to XMl to Access is not working. Can u just check it out.
another thing if there is no data in that particular field then also the data is not properly updating.
for that only i have enclosed an XML Document to u .
actually in a table it has 4 fields and for one record it has 3 fields updated. this data is not getting inserted. And i am facing a problem with inserting data from oracle to access.
Please look into it
i am enclosing bot XML Document and VB code to u
please check it and give it to me

Thanks & Regards

Pavan

XML DOCUMENT:

<?xml version="1.0" standalone="yes" ?>
- <NewDataSet>
- <PAVANDETAILS>
  <SLNO>1</SLNO>
  <NAME>pavan</NAME>
  <AGE>26</AGE>
  <HOBBIES>pen collection</HOBBIES>
  </PAVANDETAILS>
- <PAVANDETAILS>
  <SLNO>2</SLNO>
  <NAME>ravi</NAME>
  <AGE>26</AGE>
  <HOBBIES>riding</HOBBIES>
  </PAVANDETAILS>
- <PAVANDETAILS>
  <SLNO>3</SLNO>
  <NAME>phani</NAME>
  <AGE>26</AGE>
  </PAVANDETAILS>
- <PAVANDETAILS>
  <SLNO>4</SLNO>
  <NAME>vamsee</NAME>
  <AGE>27</AGE>
  <HOBBIES>chatting</HOBBIES>
  </PAVANDETAILS>
  </NewDataSet>



VB.NET CODE


Public Class Form1
    Inherits System.Windows.Forms.Form
    Dim con1 As New OleDb.OleDbConnection
    Dim con2 As New OleDb.OleDbConnection
    Dim Cmd1 As New OleDb.OleDbCommand

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        con1.ConnectionString = "Provider=""OraOLEDB.Oracle.1"";User ID=pavan;Data Source=pavandjb;Extended Properties=;Persist Security Info=True;Password=pavan"
        con1.Open()
        'Me.OleDbConnection1.Open()
        MsgBox("Connected To Database")
        Me.OleDbDataAdapter1.Fill(Me.DataSet1)
        Dim myReader As OleDb.OleDbDataReader
        Me.DataSet1.WriteXml("e:\pav_det.xml")
        Try
            MessageBox.Show(" XML Document Generated ", "", MessageBoxButtons.OK)
        Catch ex As OleDb.OleDbException
            MsgBox(ex.Message)
        Finally
            con1.Close()
            'Me.OleDbConnection1.Close()
        End Try
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim xmlDoc As Xml.XmlDocument
        Dim tNode As Xml.XmlNode
        Dim oleCmd As OleDb.OleDbCommand
        con2.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\pavanpvr\Mes documents\bd1.mdb"
        con2.Open()
        MsgBox("Connected To Database")
        Try
            xmlDoc = New Xml.XmlDocument
            xmlDoc.Load("e:\pav_det.xml")
            MsgBox("Reading The Contents")
            oleCmd = New OleDb.OleDbCommand
            oleCmd.Connection = con2
            'MsgBox("Connected to The Destination Database")
            For Each tNode In xmlDoc.SelectNodes("//pavandet")
                MessageBox.Show("Entered For Block", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information)
                oleCmd.CommandText = "Insert into pavandet(slno,name,age,hobbies) values( "
                oleCmd.CommandText += "'" & tNode.Item("slno").InnerText & "'"
                oleCmd.CommandText += " ,"
                oleCmd.CommandText += "'" & tNode.Item("name").InnerText & "'"
                oleCmd.CommandText += " ,"
                oleCmd.CommandText += "'" & tNode.Item("age").InnerText & "'"
                oleCmd.CommandText += " ,"
                oleCmd.CommandText += tNode.Item("hobbies").InnerText
                oleCmd.CommandText += ")"
                oleCmd.ExecuteNonQuery()
                oleCmd.CommandText = ""
            Next
            MessageBox.Show("Copied The Contents ", " ", MessageBoxButtons.OK)
        Catch ex1 As Exception
            MsgBox(ex1.Message)
            MessageBox.Show(ex1.StackTrace, "Info", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If Not xmlDoc Is Nothing Then
                xmlDoc = Nothing
            End If
            If Not con2 Is Nothing Then
                con2.Close()
                con2 = Nothing
            End If
            If Not oleCmd Is Nothing Then
                oleCmd.Dispose()
                oleCmd = Nothing
            End If
        End Try
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Dispose()
    End Sub
End Class
0
 

Author Comment

by:Pavanpvr
ID: 11056324
Hai all

I dindt get any responce. Please try to solve my problem.
i could move the data from XML to ORACLE where the source is ACCESS. But i could not do viceversa.

And if there is any blank record in that particular field even then the data is not being transformed.
I have posted my code in the previous posting, i mean the earlier one.

Kindly verify my previous postings and its solutions and answer my query.

Thanks & Regards

Pavan
0
 
LVL 6

Expert Comment

by:zulu_11
ID: 11056383
the problem is that whereever the field has no value you have to put a check on it...so that if ithat node does not exist then skip the innertext code as that would generate an Error:

try this:

If Not tNode.Item("hobbies") Is Nothing Then
'now put the code here
else
'write the code when that value/node does not exist..
End If

Regards

Zulu.
0
 

Author Comment

by:Pavanpvr
ID: 11056616
Hai
can u just make it clear and even try for oracle to Access .
i have placed the code there for ur reference. its executing but not giving proper output.
please check it and give to me

Thanks & Regards

pavan
0
 
LVL 6

Expert Comment

by:zulu_11
ID: 11056667
this is what i meant...

oleCmd.CommandText = "Insert into pavandet(slno,name,age,hobbies) values( "
If Not tNode.Item("slno") Is Nothing Then
              oleCmd.CommandText += "'" & tNode.Item("slno").InnerText & "'"
else
                oleCmd.CommandText += " '' "
End If
oleCmd.CommandText += " ,"

If Not tNode.Item("name") Is Nothing Then
      oleCmd.CommandText += "'" & tNode.Item("name").InnerText & "'"
else
      oleCmd.CommandText += "''"
End If
oleCmd.CommandText += " ,"

If Not tNode.Item("age") Is Nothing Then
                oleCmd.CommandText += "'" & tNode.Item("age").InnerText & "'"
else
                oleCmd.CommandText += "''"
End If
oleCmd.CommandText += " ,"

If Not tNode.Item("hobbies") Is Nothing Then
                oleCmd.CommandText += tNode.Item("hobbies").InnerText
else
                oleCmd.CommandText += "''"
End If
                oleCmd.CommandText += ")"
 MsgBox(oleCmd.CommandText) ' this should show you the command that is about to be executed..
                oleCmd.ExecuteNonQuery()
                oleCmd.CommandText = ""

Zulu
0
 

Author Comment

by:Pavanpvr
ID: 11056982
sorry to say that even now its still not working
its getting compiled and executed but result is nil.
please check it

pavan
0
 
LVL 6

Accepted Solution

by:
zulu_11 earned 125 total points
ID: 11057011
i'm sorry but now it's upto you to debug the program...i can't do that for you...just put a break point at the start of the for loop and check to see whether the values are coming or not and make sure that you are using the right Oracle and Access DB...

This forum is for showing the way to code / guide you not a Place to DO YOUR WORK!!

Zulu
0
 

Author Comment

by:Pavanpvr
ID: 11057032
its ok
thanks for ur advice
dont mind

pavan
0
 
LVL 1

Expert Comment

by:corpuslabs
ID: 12258048
hi zulu,

assume that i want to load the data from an XML document to dataset with out savign it to to a xml file. is there a way ?

thanking u

anto
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now