Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 5 of 5 total |
OT: Import XML into DBISam Datasets |
Wed, Jan 4 2012 6:32 PM | Permanent Link |
Adam H. | 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. |
Wed, Jan 4 2012 8:04 PM | Permanent Link |
Raul Team Elevate | 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. |
Thu, Jan 5 2012 4:31 PM | Permanent Link |
Adam H. | 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. |
Thu, Jan 5 2012 6:03 PM | Permanent Link |
Adam H. | 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. |
Wed, Jan 11 2012 2:36 AM | Permanent Link |
Dave Sellers | 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 |
This web page was last updated on Wednesday, April 24, 2024 at 11:07 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |