Merging Tables Generated by XML

[normality2000]

Would anyone have any example code in VB that illustrates how I can merge the tables that are generated when XML is read into a dataset.

An example of the XML would be:

<process>

    <block1>
        <name>name1</name>
        <dated>01/04/2009</dated>
    </block1>

    <block2>
        <text>A</text>
        <UID>1</UID>
        <title>
            <label>label1</label>
            <value>value1</value>
        </title>
        <description>
            <label>label2</label>
            <value>value2</value>
        </description>
        <test />
    </block2>

    <block2>
        <text>B</text>
        <UID>2</UID>
        <title>
            <label>label3</label>
            <value>value3</value>
        </title>
        <description>
            <label>label4</label>
            <value>value4</value>
        </description>
        <test>test1</test>
    </block2>

</process>

I want to end up with a table with the following columns:

block1_name; block1_dated; block2_text; block2_UID; block2_title_label; block2_title_value; block2_description_label; block2_description_value; block2_test

And the data to read:
name1 , 01/04/2009 , A , 1 , label1 , value1 , label2, value2 , NULL;
name1 , 01/04/2009 , B , 2 , label3 , value3 , label3, value3 , test1;

I've tried using the .merge method on the tables but to no avail.

Any pointers would be most welcome.

Many thanks in anticipation.

[sedgey]

Hi,
I would load the seperate xml files into datasets then merge the datasets as you cannot merge the individual tables in a single dataset without iterating through the data rows, check this article on MSDN for a detailed description on merging Datasets:

http://msdn.microsoft.com/en-us/library/aa984388(VS.71).aspx

[normality2000]

Appreciate the quick response.

However, I am only being provided with a single XML file within which I need to merge the datatables to one table as detailed above. As I said, I tried to use the merge function but it didn't work.

I have even tried to import each table into its own dataset and then merge these datasets into one, but I get the "The same table 'title1' cannot be the child table in two nested relations." error message.

Are there any thoughts on how to progress?