Hi,

I know this is strictly not a DBISam related issue, but probably more of
a Tdataset issue, but nonetheless I'm hoping someone here can point me
in the correct direction.

In short - what I need to be ablet o do is import data from an XML file
into my database. The XML file will contain multiple records, and more
importantly nested data.

A short example of what I'm trying to import would be similar tot he
following:

<identities>
  <identity>
    <Name>Tom</name>
    <Address>1 Main Street</Address>
    <Phones>
      <PhoneNo>555-1212</PhoneNo>
      <PhoneType>Landline</PhoneType>
    </Phones>
    <Phones>
      <PhoneNo>0400-122-212</PhoneNo>
      <PhoneType>Cell / Mobile</PhoneType>
    </Phones>
    <Phones>
      <PhoneNo>555-1111</PhoneNo>
      <PhoneType>Fax</PhoneType>
    </Phones>
  </identity>

  <identity>
    <Name>Harry</name>
    <Address>1 Fifth Street</Address>
    <Phones>
      <PhoneNo>888-1212</PhoneNo>
      <PhoneType>Landline</PhoneType>
    </Phones>
    <Phones>
      <PhoneNo>0400-156-777</PhoneNo>
      <PhoneType>Cell / Mobile</PhoneType>
    </Phones>
    <Phones>
      <PhoneNo>888-1111</PhoneNo>
      <PhoneType>Fax</PhoneType>
    </Phones>
  </identity>
 <identities>

I have two dbisam datasets. One called Names, and the other called
Phones. (I don't really have these - but to simplify what I'm doing I'm
trying to relate to a simple situation

Phones is linked to Names on a ParentID field.

So:

Names
~~~~~
ID : AutoInc
Name : VarChar;
Address : VarChar;


Phones
~~~~~~
ID : AutoInc
ParentID: Integer (Links to ID in Names)
PhoneNo : VarChar;
PhoneType : VarChar;


I need the XML data above to load into the two tables above, with the
correctly defined links. The XML file is not flat and will contain a
different number of records every time it is imported.


SMImport doesn't seem to be able to handle nested datasets like this
from what I can tell.

XML Data Binding Wizard appears to be able to handle nested datasets,
but from what I can tell it's still static. (ie I will run into trouble
if one XML file has 2 name records today, and 8 tomorrow).

TXMLDocument??? I'm not sure where to start here. I've used it for
creating XML documents from datasets, but not the other way around.
Creating from datasets is easy, as I just do a while not eof / next on
the dataset to create my child records.

Does anyone know what is the correct approach to dealing with this?

Thanks & Regards

Adam.

Adam,

XML is pretty straightforward to parse so i would suggest you just parse
it out in code and populate your tables as you go.

Below is a super quick n dirty recursive xml parsing code that should
get you started - it parses out all text elements.

Your xml is fairly simple so you could just do few nested for loops and
process ChildNodes that way.

If you decide to use recursive version then just set couple of global
vars for person for example and then once you discover more info (like
address) then insert record into your names table. Then continue
processing phones and insert those into phones phones table until you
hit a XmlNode.NodeName='name' again (indicating that you're now at new
person).


To run code do following:
- new vcl form application
- drop 2 memo controls, button and TXMLDocument on the form.
-name memo controls  MemoXml and MemoOutput
- paste your XML into the MemoXml lines

- button click event should do following
 try
    XMLDocument1.XML := MemoXml.Lines;
    XMLDocument1.Active := true;
    log('=== START XML PARSE ===');
    ProcessChildList(XmlDocument1.DocumentElement);
    log('=== END XML PARSE ===');
  except
  on E:Exception do
    log('Parsing error :' + E.Message);
  end;

and then add a new procedure


procedure TForm1.ProcessChildList(xmlNode: IXMLNode);
var i:integer;
begin
  if xmlNode.NodeType = ntElement then
  begin
    if xmlNode.IsTextElement then
    begin
      if XmlNode.NodeValue <> null then
        log( XmlNode.NodeValue + ':' + XmlNode.NodeValue)
    end;

    if XmlNode.HasChildNodes then
      for i:=0 to XmlNode.ChildNodes.Count-1 do
        ProcessChildList(XMlNode.ChildNodes[i]);
  end
end;


Raul





On 1/4/2012 6:32 PM, Adam H. wrote:
> Hi,
>
> I know this is strictly not a DBISam related issue, but probably more of
> a Tdataset issue, but nonetheless I'm hoping someone here can point me
> in the correct direction.
>
> In short - what I need to be ablet o do is import data from an XML file
> into my database. The XML file will contain multiple records, and more
> importantly nested data.
>
> A short example of what I'm trying to import would be similar tot he
> following:
>
> <identities>
> <identity>
> <Name>Tom</name>
> <Address>1 Main Street</Address>
> <Phones>
> <PhoneNo>555-1212</PhoneNo>
> <PhoneType>Landline</PhoneType>
> </Phones>
> <Phones>
> <PhoneNo>0400-122-212</PhoneNo>
> <PhoneType>Cell / Mobile</PhoneType>
> </Phones>
> <Phones>
> <PhoneNo>555-1111</PhoneNo>
> <PhoneType>Fax</PhoneType>
> </Phones>
> </identity>
>
> <identity>
> <Name>Harry</name>
> <Address>1 Fifth Street</Address>
> <Phones>
> <PhoneNo>888-1212</PhoneNo>
> <PhoneType>Landline</PhoneType>
> </Phones>
> <Phones>
> <PhoneNo>0400-156-777</PhoneNo>
> <PhoneType>Cell / Mobile</PhoneType>
> </Phones>
> <Phones>
> <PhoneNo>888-1111</PhoneNo>
> <PhoneType>Fax</PhoneType>
> </Phones>
> </identity>
> <identities>
>
> I have two dbisam datasets. One called Names, and the other called
> Phones. (I don't really have these - but to simplify what I'm doing I'm
> trying to relate to a simple situation
>
> Phones is linked to Names on a ParentID field.
>
> So:
>
> Names
> ~~~~~
> ID : AutoInc
> Name : VarChar;
> Address : VarChar;
>
>
> Phones
> ~~~~~~
> ID : AutoInc
> ParentID: Integer (Links to ID in Names)
> PhoneNo : VarChar;
> PhoneType : VarChar;
>
>
> I need the XML data above to load into the two tables above, with the
> correctly defined links. The XML file is not flat and will contain a
> different number of records every time it is imported.
>
>
> SMImport doesn't seem to be able to handle nested datasets like this
> from what I can tell.
>
> XML Data Binding Wizard appears to be able to handle nested datasets,
> but from what I can tell it's still static. (ie I will run into trouble
> if one XML file has 2 name records today, and 8 tomorrow).
>
> TXMLDocument??? I'm not sure where to start here. I've used it for
> creating XML documents from datasets, but not the other way around.
> Creating from datasets is easy, as I just do a while not eof / next on
> the dataset to create my child records.
>
> Does anyone know what is the correct approach to dealing with this?
>
> Thanks & Regards
>
> Adam.

Hi Raul,

Thanks for that. I was wondering if TXMLDocument was the best path, but
didn't know where to start. The example you have provided is great!

Cheers!

Adam.

Hi Raul,

I have modified your script slightly to the following to try and obtain
where the new parent records come into play. (So I know when to post and
create a new record).

I was wondering if this is the best approach, or whether there is a
different / more preferred option?

----------------------------
procedure TForm1.ProcessChildList(xmlNode: IXMLNode; recno : integer);
var i:integer;
begin
if XMLNode.ParentNode.nodename = 'identity' then
if recno = 0 then
 log('----NEW IDENTITY-------------------');

if XMLNode.ParentNode.nodename = 'Phones' then
if recno = 0 then
 log('----NEW IDENTITY PHONE----');


  if xmlNode.NodeType = ntElement then
    if (xmlNode.IsTextElement) then
      if XmlNode.NodeValue <> null then
        log(xmlNode.ParentNode.NodeName + ' : '+ XmlNode.NodeName + ' :
' + XmlNode.NodeValue);


    if XmlNode.HasChildNodes then
     begin
      for i:=0 to XmlNode.ChildNodes.Count-1 do
        ProcessChildList(XMlNode.ChildNodes[i], i);
     end;
end;

----------------------------


The output it gives is:

=== START XML PARSE ===
----NEW IDENTITY-------------------
identity : Name : Tom
identity : Address : 1 Main Street
----NEW IDENTITY PHONE----
Phones : PhoneNo : 555-1212
Phones : PhoneType : Landline
----NEW IDENTITY PHONE----
Phones : PhoneNo : 0400-122-212
Phones : PhoneType : Cell / Mobile
----NEW IDENTITY PHONE----
Phones : PhoneNo : 555-1111
Phones : PhoneType : Fax
----NEW IDENTITY-------------------
identity : Name : Harry
identity : Address : 1 Fifth Street
----NEW IDENTITY PHONE----
Phones : PhoneNo : 888-1212
Phones : PhoneType : Landline
----NEW IDENTITY PHONE----
Phones : PhoneNo : 0400-156-777
Phones : PhoneType : Cell / Mobile
----NEW IDENTITY PHONE----
Phones : PhoneNo : 888-1111
Phones : PhoneType : Fax
=== END XML PARSE ===


Cheers

Adam.

Adam H. wrote:

> Hi,
>
> I know this is strictly not a DBISam related issue, but probably more
> of a Tdataset issue, but nonetheless I'm hoping someone here can
> point me in the correct direction.
>
> In short - what I need to be ablet o do is import data from an XML
> file into my database. The XML file will contain multiple records,
> and more importantly nested data.
>
> A short example of what I'm trying to import would be similar tot he
> following:
>
> <identities>
>   <identity>
>     <Name>Tom</name>
>     <Address>1 Main Street</Address>
>     <Phones>
>       <PhoneNo>555-1212</PhoneNo>
>       <PhoneType>Landline</PhoneType>
>     </Phones>
>     <Phones>
>       <PhoneNo>0400-122-212</PhoneNo>
>       <PhoneType>Cell / Mobile</PhoneType>
>     </Phones>
>     <Phones>
>       <PhoneNo>555-1111</PhoneNo>
>       <PhoneType>Fax</PhoneType>
>     </Phones>
>   </identity>
>
>   <identity>
>     <Name>Harry</name>
>     <Address>1 Fifth Street</Address>
>     <Phones>
>       <PhoneNo>888-1212</PhoneNo>
>       <PhoneType>Landline</PhoneType>
>     </Phones>
>     <Phones>
>       <PhoneNo>0400-156-777</PhoneNo>
>       <PhoneType>Cell / Mobile</PhoneType>
>     </Phones>
>     <Phones>
>       <PhoneNo>888-1111</PhoneNo>
>       <PhoneType>Fax</PhoneType>
>     </Phones>
>   </identity>
>  <identities>
>
> I have two dbisam datasets. One called Names, and the other called
> Phones. (I don't really have these - but to simplify what I'm doing
> I'm trying to relate to a simple situation
>
> Phones is linked to Names on a ParentID field.
>
> So:
>
> Names
> ~~~~~
> ID : AutoInc
> Name : VarChar;
> Address : VarChar;
>
>
> Phones
> ~~~~~~
> ID : AutoInc
> ParentID: Integer (Links to ID in Names)
> PhoneNo : VarChar;
> PhoneType : VarChar;
>
>
> I need the XML data above to load into the two tables above, with the
> correctly defined links. The XML file is not flat and will contain a
> different number of records every time it is imported.
>
>
> SMImport doesn't seem to be able to handle nested datasets like this
> from what I can tell.
>
> XML Data Binding Wizard appears to be able to handle nested datasets,
> but from what I can tell it's still static. (ie I will run into
> trouble if one XML file has 2 name records today, and 8 tomorrow).
>
> TXMLDocument??? I'm not sure where to start here. I've used it for
> creating XML documents from datasets, but not the other way around.
> Creating from datasets is easy, as I just do a while not eof / next
> on the dataset to create my child records.
>
> Does anyone know what is the correct approach to dealing with this?
>
> Thanks & Regards
>
> Adam.

I would take a look at:
http://sourceforge.net/projects/tpxmlpartner/files/

This is much easier to use (IMO).

Dave