471,594 Members | 1,835 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,594 software developers and data experts.

Insert/Update tables in a database with relational data from an XML file

Hi,

Could anybody just point me in a direction where I can find information
on how the heck I can update a database with relational data from an XML
file.

I use stored procedures to insert the data. And I am NOT storing the xml
in the database. I want to map the xml elements to the database columns.

I am about to start peeling my skin off in frustration about this. There
are loads of info how to update using SetParentRow with ONE parent row
and ONE child row like this:

Dim ds As DataSet = New DataSet
Dim conn As SqlConnection = New SqlConnection("DataSource... ")

conn.Open()

Dim da1 As SqlDataAdapter = New SqlDataAdapter(New SqlCommand("SELECT *
FROM ParentTable", conn))

da1.InsertCommand = New SqlCommand("AddParentData", conn)

Dim cmd As SqlCommand = da1.InsertCommand

cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add(New SqlParameter("@ParentID", SqlDbType.Int))
cmd.Parameters("@ParentID ").Direction = ParameterDirection.Output
cmd.Parameters("@ParentID").SourceColumn = "ParentID"

cmd.Parameters.Add(New SqlParameter("@ParentText", SqlDbType.NVarChar,
50, "ParentText"))

da1.FillSchema(ds, SchemaType.Source)

Dim pTable As DataTable = ds.Tables("Table")
pTable.TableName = "ParentTable"

’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’ ’’’’’’’’’’’’’’’’’’’’’

Dim da2 As SqlDataAdapter = New SqlDataAdapter(New SqlCommand("SELECT *
FROM ChildTable", conn))

da2.InsertCommand = New SqlCommand("AddChildData", conn)

cmd = da2.InsertCommand

cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@ParentID", SqlDbType.Int))
cmd.Parameters("@ParentID ").SourceColumn = "ParentID"

cmd.Parameters.Add(New SqlParameter("@ChildText", SqlDbType.VarChar, 50,
"ChildText"))

da2.FillSchema(ds, SchemaType.Source)

Dim cTable As DataTable = ds.Tables("Table")
cTable.TableName = "ChildTable"
ds.Relations.Add(New DataRelation("ParentChild", ds.Tables("ParentTable
").Columns("ParentID"), ¬_
ds.Tables("ChildTable ").Columns("ParentID")))
Dim dr1 As DataRow = ds.Tables ("ParentTable").NewRow()

dr1 ("ParentTest") = "TESTING_"
ds.Tables("ParentTable").Rows.Add(dr1)

Dim dr2 As DataRow = ds.Tables("ChildTable").NewRow()

dr2("ChildTest") = "TESTING_

dr2.SetParentRow(dr1)

ds.Tables("ChildTable").Rows.Add(dr2)

da1.Update(ds, "ParentTable")
da2.Update(ds, "ChildTable")

It works like a charm BUT it's not dynamic and it gets a bit confusing
when I want to load an xml document with, say, 128 parent rows(with a
bunch of elements) and each parent row(also with a bunch of elements)
contain between 1 to 500 child rows. I’ve been trying different
solutions for a couple of days now and nothing works.

Any guidens would be highly appreciated.

Regards,

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #1
1 2995
Hi,

DON'T BOTHER TO ANSWER THIS MESSAGE...I HAVE SOLVED THE PROBLEM...

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

18 posts views Thread by Elroyskimms | last post: by
16 posts views Thread by Philip Boonzaaier | last post: by
2 posts views Thread by maltchev | last post: by
16 posts views Thread by robert | last post: by
13 posts views Thread by Terry Olsen | last post: by
reply views Thread by XIAOLAOHU | last post: by
reply views Thread by leo001 | last post: by
reply views Thread by Anwar ali | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.