How Can I Create Referential Integrity with Dataset.XMLReader??

[Workaholic]

Hi,
I am hoping somebody can help me by answering a question about
referential integrity within the DATASET.READXML method. I am trying
to process an XML file that is provided by a third party system, and
am running into some referential integrity problems. I will use a
simple piece of XML as an example.

<XML_DATA>
<MEMBER>
<MEMBER_ID>1</MEMBER_ID>
<MEMBER_NAMES>
<TITLE>MR</TITLE>
<FIRST_NAME>JOE</FIRST_NAME>
<LAST_NAME>BLOGGS</LAST_NAME>
</MEMBER_NAMES>
</MEMBER>
<MEMBER>
<MEMBER_ID>2</MEMBER_ID>
<MEMBER_NAMES>
<TITLE>MRS</TITLE>
<FIRST_NAME>JOSEPHINE</FIRST_NAME>
<LAST_NAME>BLOGGS</LAST_NAME>
</MEMBER_NAMES>
</MEMBER>
</XML_DATA>

I know this XML is pretty poor, but it does nicely explain the issue.
Within this XML, you can see that Member Id "1" is "Mr Joe Bloggs" and
Member Id "2" is "Mrs Josephine Bloggs". The issue for me though is
relating the FIRST_NAME column to the appropriate Member ID...
....If I examine DATASET.TABLES("MEMBER_NAMES").FIRST_NAME I will get a
count of two (element 0 being "Joe" and element 1 being "Josephine").
What I really need is a way to differentiate them.

Can anybody tell me if there is a command similar to:

DATASET.TABLES("MEMBER").ROWS(1).GETCHILDROWS("MEMBER_NAMES").ROWS(0).ITEM("FIRST_NAME")

I have tried this, but GetChildRows always seems to return nothing. I
have been able to use the syntax
DATASET.TABLES("MEMBER").CHILDRELATIONS(0).CHILDTABLE.TABLENAME to
return the value "MEMBER_NAMES", but this doesn't tell me which row in
the sub-table is related to which row in the outer table.

If it makes any difference, I am still using .Net Framework 1.1, but
will be happy to upgrade if that helps me.

Many thanks for your help

 

[Cor Ligthert[MVP]]

Hi,

There are more methods to get XML information (XMLReader, LoadXML). I prefer
the XMLNodereader for that.

http://www.vb-tips.com/ReadXMl.aspx

Cor

 

[Workaholic]

Thanks for the tip. I had a play with these methods, but would prefer
to stick with Dataset.ReadXML. The advantage that Dataset.ReadXML has
for my application, is that I can easily access the data in a
particular table. I should say that the application is allowing users
to develop their own templates, which then get filled with the XML
values. In my simple XML example above, my users can easily access ALL
of the first names by using Dataset.Tables("MEMBER_NAMES"). This
doesn't seem possible with the new methods proposed, because you have
to drill down each block in turn (the new methods are more structured,
but less flexible). I still think that if I can get row-level
referential integrity via the Dataset.ReadXML method (in other words
to determine which ROW, not which TABLE, is the parent of "JOE
BLOGGS") then I will have everything I need. Any other ideas? Thanks
for your help

 

[Bill McCarthy]

Hi,

Try this sample code to get you started .


For Each row As DataRow In ds.Tables("MEMBER").Rows

Console.WriteLine("Member ID : " & row.Item("MEMBER_ID").ToString)

For Each name As DataRow In row.GetChildRows("MEMBER_MEMBER_NAMES")

Console.WriteLine("First Name is : " &
name.Item("FIRST_NAME").ToString)
Next

Console.WriteLine("----------")

Next


Note, I used the MEMBER_MEMBER_NAMES relation to find the child rows.


Regards,

Bill.