473,390 Members | 1,464 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,390 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 3069
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: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

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.