473,327 Members | 2,069 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 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 3065
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

14
by: serge | last post by:
I have a scenario where two tables are in a One-to-Many relationship and I need to move the data from the Many table to the One table so that it becomes a One-to-One relationship. I need to...
18
by: Elroyskimms | last post by:
I have a table using an identity column as its Primary Key and two columns (table reduced for simplicity) EmployeeNumber and ArrivalTime. CREATE TABLE ( IDENTITY (1, 1) NOT NULL , (10)...
16
by: Philip Boonzaaier | last post by:
I want to be able to generate SQL statements that will go through a list of data, effectively row by row, enquire on the database if this exists in the selected table- If it exists, then the colums...
2
by: maltchev | last post by:
i need to insert data from an xml file into sql server table. the xml file contains only one record. how to insert the data? how to map the names of the fields in the xml file and the table?...
16
by: robert | last post by:
been ruminating on the question (mostly in a 390/v7 context) of whether, and if so when, a row update becomes an insert/delete. i assume that there is a threshold on the number of columns of the...
3
by: iKiLL | last post by:
Hi all I am having problems getting my SqlCeDataAdapter to Update the SQL Mobile Data base. i am using C# CF2. I have tried this a number of different ways. Starting with the command builder...
5
by: djsdaddy | last post by:
Good Day All, I have some EEO data in an old dBase4 database that I have converted to an Access table. Since dBase was not a relational database, I didn't create any key fields. I linked all of the...
13
by: Terry Olsen | last post by:
I'm using OleDb to connect with an Access Database. I have anywhere from 10 to over 100 records that I need to either INSERT if the PK doesn't exist or UPDATE if the PK does exist, all in a single...
2
by: cday119 | last post by:
Can someone help me out with this one. I am trying to insert a row of data into an access database. The primary key is set to auto increment. It will work once but the next time you try an insert...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.