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
vcharlesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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?
0
vcharlesAuthor Commented:
Thanks, will check it out.  I will look into the code to convert from table to xml after I solve the first part.

V.
0
Jacques Bourgeois (James Burger)PresidentCommented:
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)
0
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

vcharlesAuthor Commented:
Hi,

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

Thanks,
V.
0
vcharlesAuthor Commented:
Hi again.

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

Victor
0
Jacques Bourgeois (James Burger)PresidentCommented:
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.
0
vcharlesAuthor Commented:
Thanks, will try it and get back to you.
0
vcharlesAuthor Commented:
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
0
Jacques Bourgeois (James Burger)PresidentCommented:
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.
0
vcharlesAuthor Commented:
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
0
vcharlesAuthor Commented:
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>
0
Jacques Bourgeois (James Burger)PresidentCommented:
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.
0
vcharlesAuthor Commented:
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
0
Jacques Bourgeois (James Burger)PresidentCommented:
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.
0
Jacques Bourgeois (James Burger)PresidentCommented:
For the post you sent while I was writing the code I sent you, the main cause is that you did not define the PrimaryKey. The Merge needs that in order to know how to match the data in the first table with the data in the second one.

I suggested the DataSet because it is the easiest way to do the job. But it requires resources that the solution with only DataTables that I sent you in my last post do not need. So, if you can go with that, that's the best way as far as resources and performance are concerned. It can be a lot more code to write however if there are many fields.

So if you prefer the easy way out, the DataSet, but with the added primary keys:

		Dim TableA As New DataSet
		TableA.ReadXml(IO.Path.Combine(Application.StartupPath + "\Probleme.xml"))
		TableA.Tables(0).PrimaryKey = New DataColumn() {TableA.Tables(0).Columns("Probleme_ID")}

		Dim TableB As New DataSet
		TableB.ReadXml(IO.Path.Combine(Application.StartupPath + "\Problemea.xml"))
		TableB.Tables(0).PrimaryKey = New DataColumn() {TableB.Tables(0).Columns("Probleme_ID")}

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

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


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

Open in new window

I also suggest that you give another name to the result. If something wrong happens, you could end up trashing the original Probleme.xml file.

Also, you usually do not have to specify the ExecutablePath when the file is in the application directory. This is the default. Unless the user creates a shortcut to the application and modifies the Start in path, the file name is sufficient.

However, you might not be aware of that, but if you install the application in Program Files, the user cannot write in the application directory he is not running the application as an administrator. You do not see that while developing because by default, Visual Studio compiles the application in My Documents. But when you install it later in Program Files, the WriteXml with bomb because Program Files is readonly, by default, for .NET applications. The files should be in another directory for a real application.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vcharlesAuthor Commented:
It worked!

Thank you for all your Help and Patience.

Victor
0
vcharlesAuthor Commented:
Hi again,

Your solution works, but I'm unable to convert data from my Grid to anproblemea.xml, will post another question.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.