Avatar of vcharles
vcharles asked on

Help with comparing ID elements in two tables (TableA, TableB) and updating TableA

Hi,

How do I read two xml files to two separate tables (TableA, TableB), compare IDs in both tables? and If TableA_ID = TableB_ID replace values in TableA with values in TableB?
than how do I convert the data in TableA back to an xml file?

Thanks,

Victor
Visual Basic.NET

Avatar of undefined
Last Comment
vcharles

8/22/2022 - Mon
Guy Hengel [angelIII / a3]

For the update, check this article
http://mobile.experts-exchange.com/Database/Miscellaneous/A_1517-UPDATES-with-JOIN-for-everybody.html

For the second part, what part is the problem?
ASKER
vcharles

Thanks, will check it out.  I will look into the code to convert from table to xml after I solve the first part.

V.
Jacques Bourgeois (James Burger)

You can create the DataTable from the xml files with the following:

Dim TableA As New DataTable
TableA.ReadXml("Path\YourFile.xml")

Depending on XML file, you might need to call ReadXmlSchema first. If no schema is available, then you will have to set the PrimaryKey property of the DataTable to define which column is the ID field.

When you have both tables, simple merge the second one into the first one:

 TableA.Merge(TableB)
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
vcharles

Hi,

After merging the tables how do i convert tableA back to an xml file?

Thanks,
V.
ASKER
vcharles

Hi again.

How do I set the PrimaryKey property of the DataTable to define which column is the ID field?

Victor
Jacques Bourgeois (James Burger)

For the PrimaryKey, you need to pass an array of columns, even if you have only one. The syntax could be the following:

TableA.PrimaryKey = New DataColumn() {Table.Columns("YourIDField")}

Notice the parenthesis ( ) after DataColumn to indicate that you are creating an array, ant the brackets { } that are used to initialize an array while calling its constructor.

------

To convert back to an xml file, you simply call WriteXlm:

TableA.WriteXml(("Path\YourFile.xml", XmlWriteMode.WriteSchema)

The second parameter is optional. If the original file contained the schema, which it probably doesn't since you need to have to define the primary key, this will write it back, otherwise it will write only write the data.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
vcharles

Thanks, will try it and get back to you.
ASKER
vcharles

Hi,

I'm trying the cole below to add data from table B to table A but received the following error  message: DataTable does not support schema inference from Xml.

On line: TableA.ReadXml(Path.Combine(Application.StartupPath + "\Probleme.xml"))

Code:

  Dim TableA As New DataTable
        TableA.ReadXml(Path.Combine(Application.StartupPath + "\Probleme.xml"))

        Dim TableB As New DataTable
        TableB.ReadXml(Path.Combine(Application.StartupPath + "\Problemea.xml"))

        TableA.Merge(TableB)

        MsgBox(TableA.Rows.Count)

How do I fix this error?

Thanks,
Victor
Jacques Bourgeois (James Burger)

Try adding the primary key to the tables before the merge:

TableA.PrimaryKey = New DataColumn() {Table.Columns("YourIDField")}
TableB.PrimaryKey = New DataColumn() {Table.Columns("YourIDField")}

And when you get an error, please states on which line the error occurs. Its hard to identify the specific cause of the problem otherwise.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
vcharles

Hi,

Below is my latest code, the error occurs on the second line:

        Dim TableA As New DataTable
        TableA.ReadXml(Path.Combine(Application.StartupPath + "\Probleme.xml")) '***Error

        Dim TableB As New DataTable
        TableB.ReadXml(Path.Combine(Application.StartupPath + "\Problemea.xml"))

        TableA.PrimaryKey = New DataColumn() {TableA.Columns("probleme_ID")}
        TableB.PrimaryKey = New DataColumn() {TableB.Columns("probleme_ID")}

        TableA.Merge(TableB)

Thanks,

Victor
ASKER
vcharles

Hi,

Below are the xml files, I am trying to replace value in promleme.xml where values in both xml ID = 1 and add two more elements Probleme_ID = 11 and Probleme_ID = 12.

Probleme.xml


<Root>
  <ProblemeTable>
    <Probleme_ID>1</Probleme_ID>
    <Probleme>Probleme1</Probleme>
  </ProblemeTable>
  <ProblemeTable>
    <Probleme_ID>2</Probleme_ID>
    <Probleme>Probleme2</Probleme>
  </ProblemeTable>
 <ProblemeTable>
    <Probleme_ID>3</Probleme_ID>
    <Probleme>Probleme3</Probleme>
  </ProblemeTable>
<ProblemeTable>
    <Probleme_ID>4</Probleme_ID>
    <Probleme>Probleme4</Probleme>
  </ProblemeTable>
  <ProblemeTable>
    <Probleme_ID>5</Probleme_ID>
    <Probleme>Probleme5</Probleme>
  </ProblemeTable>
 <ProblemeTable>
    <Probleme_ID>6</Probleme_ID>
    <Probleme>Probleme6</Probleme>
  </ProblemeTable>
<ProblemeTable>
  <Probleme_ID>7</Probleme_ID>
    <Probleme>Probleme7</Probleme>
  </ProblemeTable>
 <ProblemeTable>
    <Probleme_ID>8</Probleme_ID>
    <Probleme>Probleme8</Probleme>
  </ProblemeTable>
<ProblemeTable>
    <Probleme_ID>9</Probleme_ID>
    <Probleme>Probleme9</Probleme>
  </ProblemeTable>
  <ProblemeTable>
    <Probleme_ID>10</Probleme_ID>
    <Probleme>Probleme10</Probleme>
  </ProblemeTable>
</Root>



Problemea.xml

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<Root>
  <ProblemeTable>
    <Probleme_ID>1</Probleme_ID>
    <Probleme>Probleme1Modified</Probleme>
  </ProblemeTable>
  <ProblemeTable>
    <Probleme_ID>11</Probleme_ID>
    <Probleme>Probleme11</Probleme>
  </ProblemeTable>
  <ProblemeTable>
    <Probleme_ID>12</Probleme_ID>
    <Probleme>Probleme12</Probleme>
  </ProblemeTable>
</Root>
Jacques Bourgeois (James Burger)

Although xml is a standard, there are variations on how it can be presented, and sometimes a file created in one application cannot be read in another one. So you might have to try different ways of reading the file before you find one that will work.

As I told you before, you might have to call ReadXmlSchema before calling ReadXml.

Dim TableA As New DataTable
TableA.ReadXmlSchema(Path.Combine(Application.StartupPath + "\Probleme.xml"))
TableA.ReadXml(Path.Combine(Application.StartupPath + "\Probleme.xml")) '***Error

Dim TableB As New DataTable
TableB.ReadXmlShema(Path.Combine(Application.StartupPath + "\Problemea.xml"))
TableB.ReadXml(Path.Combine(Application.StartupPath + "\Problemea.xml"))

If you have a .xsd file along your .xml file, then call ReadXmlSchema on that .xsd file instead of the .xml (a schema can be part of the .xml or in a separate file that uses the .xsd extension by convention)

If this does not work, then you xml file is in a format that is not entirely compatible with the Visual Studio xml mechanism. You can then try with a DataSet instead of DataTable, because the DataSet can analyze a file that does not have a schema and create one from that analysis.

If it does work, then you probably will be able to get rid of the PrimaryKeys. They are usually defined in the schema, but not always.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
vcharles

I am using the dataset approach but the result is wrong, the data in ID = 1 for TableB does not replace the data with ID = 1 for TableA.  The data in TableB is added at the end of Table A. How do I check if ID are equal, and if so replace data in TableA with data from TableB?

 Dim TableA As New DataSet
        TableA.ReadXml(Path.Combine(Application.StartupPath + "\Probleme.xml"))

        Dim TableB As New DataSet
        TableB.ReadXml(Path.Combine(Application.StartupPath + "\Problemea.xml"))

        TableA.Tables(0).Merge(TableB.Tables(0))

        MsgBox(TableA.Tables(0).Rows.Count)


        TableA.WriteXml((Application.StartupPath + "\Probleme.xml"))

Thanks,

Victor
Jacques Bourgeois (James Burger)

Because of the way you xml is built, with no schema information, the table columns have to be created in the code before you read the file. Here is code that works with the sample data that you have provided:

		Dim TableA As New DataTable
		Dim TableB As New DataTable
		Dim ds As New DataSet

		TableA.Columns.Add("Probleme_ID", GetType(Integer))
		TableA.Columns.Add("Probleme", GetType(String))
		TableA.TableName = "ProblemeTable"
		TableA.ReadXml("Probleme.xml")
		TableA.PrimaryKey = New DataColumn() {TableA.Columns("Probleme_ID")}

		TableB.Columns.Add("Probleme_ID", GetType(Integer))
		TableB.Columns.Add("Probleme", GetType(String))
		TableB.TableName = "ProblemeTable"
		TableB.ReadXml("Problemea.xml")
		TableB.PrimaryKey = New DataColumn() {TableB.Columns("Probleme_ID")}

		TableA.Merge(TableB)
		ds.DataSetName = "Root"
		ds.Tables.Add(TableA)
		ds.WriteXml("Result.xml")

Open in new window

Ideally, xml files should have a schema. This helps a lot in working with them because the schema contains a definition of the structure of the data, and you do not have to create the fields yourself.

If you do not know what a schema is, change the last line for ds.WriteXml("Result.xml", XmlWriteMode.WriteSchema) and look at the beginning of the file. You see that the structure of the data is described, up to the field(s) that define the primary key. Your code can read that in order to create the DataTable.

I expected something like that in my first solutions.

I suppose that the xml you provided was only for testing purposes, and that in real life, you will have more fields to work with, which mean a lot more lines of code. If you have control over the creation of the xml files, set things so that the schema is included. Then, a simple ReadXmlSchema before reading the files will take care of all the TableA.Columns.Add commands.
ASKER CERTIFIED SOLUTION
Jacques Bourgeois (James Burger)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
vcharles

It worked!

Thank you for all your Help and Patience.

Victor
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
vcharles

Hi again,

Your solution works, but I'm unable to convert data from my Grid to anproblemea.xml, will post another question.