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

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?

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

V.

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)

Hi,

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

Thanks,
V.

Hi again.

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

Victor

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.

Thanks, will try it and get back to you.

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

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.

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

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>

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.

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

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")

Select allOpen 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.

It worked!

Thank you for all your Help and Patience.

Victor

Hi again,

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