1. Technology

Your suggestion is on its way!

An email with a link to:

http://delphi.about.com/library/bluc/text/uc050601a.htm

was emailed to:

Thanks for sharing About.com with others!

RTL reference|Glossary|Tips/Tricks|FREE App/VCL|Best'O'Net|Books|Link To

Database table to XML and back
Creating XML files from Paradox (or any DB) tables using Delphi. See how to export the data from a table to a XML file and how to import that data back to the table.
More of this Feature
Download code
Join the Discussion
"Post your questions, concerns, views and comments to this article..."
Discuss!
Related Resources
About XML and Delphi
Database programming

Article submitted by: Sreekumar S.

This article is about generating XML files by reading data from Paradox tables and inserting the same data back in to the same table by reading data from the previously generated XML file (or any database which can be accessed with Delphi). The code won't work for tables with memo fields. I have used MS XML Parser for parsing the XML document. So you must need IE 5.0 or newer installed in your system.

Generating a XML file - from database to XML
I have used the following convention for the XML file:

1. The root name of the XML file is the same as that of the table name.
2. Each record from the table comes in between the and tags.
3. Data from the table rows comes in between the tags and

For example:

<Country>
<Records>
<Name>Argentina</Name>
<Capital>Buenos Aires</Capital>
<Continent>South America</Continent>
<Area>2777815</Area>
<Population>32300003</Population>
</Records>
...
</Country>

Preparing a Delphi project
Start a new application and place a Button and a Table component on the main form. Set the properties of the Table component as follows:

Name = Table1
DatabaseName = DBDEMOS
TableName = Country
Active = True

From the Delphi IDE menu select Project/Import Type library. This will display the "Import Type Library" dialog. Select "Microsoft XML,Version 2.0(version 2.0)" from the list box and then click the "Create Unit" button. This will add MSXML_TLB unit to your project.
Add MSXML_TLB to the uses clause in the interface portion of your unit.

Declare the following variables in the var section

  DataList            : TStringlist; 
  doc                 : IXMLDOMDocument; 
  root, child, child1 : IXMLDomElement; 
  text1, text2        : IXMLDOMText; 
  nlist               : IXMLDOMNodelist; 
  dataRecord          : String; 

Add the following function (makeXml) to your unit. makeXML generates the XML file by reading data from the selected (Country) table.

function TForm1.makeXml(table: TTable): Integer; 
var 
  i   : Integer; 
  xml,temp : String; 
begin 
 try 
  table.close; 
  table.open; 
  xml  := table.TableName; 
  doc  := CreateOleObject('Microsoft.XMLDOM')
          as IXMLDomDocument; 
  //Set the root name of the xml
  file as that of the table name. 
  //In this case "country"
  root := doc.createElement(xml); 
  doc.appendchild(root); 
  //This while loop will go through the
  entire table to generate the xml file 
  while not table.eof do begin
   //adds the first level children , Records 
   child:= doc.createElement('Records'); 
   root.appendchild(child); 
   for i:=0 to table.FieldCount-1 do begin 
     //adds second level children 
    child1 := doc.createElement
              (table.Fields[i].FieldName); 
    child.appendchild(child1); 
    //Check field types 
    case TFieldType
    (Ord(table.Fields[i].DataType)) of 
    ftString: 
    begin 
     if Table.Fields[i].AsString ='' then 
      temp :='null'  //Put a default string  
     else 
      temp := table.Fields[i].AsString; 
    end; 

    ftInteger, ftWord, ftSmallint: 
    begin 
     if Table.Fields[i].AsInteger > 0 then 
      temp := IntToStr(table.Fields[i].AsInteger) 
     else 
      temp := '0'; 
    end; 

    ftFloat, ftCurrency, ftBCD: 
    begin 
     if table.Fields[i].AsFloat > 0 then 
      temp := FloatToStr(table.Fields[i].AsFloat) 
     else 
      temp := '0'; 
    end; 

    ftBoolean: 
    begin 
     if table.Fields[i].Value then 
      temp:= 'True' 
     else 
      temp:= 'False'; 
    end; 

    ftDate: 
    begin 
     if (not table.Fields[i].IsNull) or 
        (Length(Trim(table.Fields[i].AsString))
        > 0) then 
      temp := FormatDateTime
               ('MM/DD/YYYY',
               table.Fields[i].AsDateTime) 
     else 
      //put a valid default date 
      temp:= '01/01/2000'; 
    end; 

    ftDateTime: 
    begin 
     if (not table.Fields[i].IsNull) or 
        (Length(Trim(table.Fields[i].AsString))
        > 0) then 
      temp := FormatDateTime
              ('MM/DD/YYYY hh:nn:ss', 
              table.Fields[i].AsDateTime) 
     else 
      //Put a valid default date and time 
      temp := '01/01/2000 00:00:00'; 
    end; 

    ftTime: 
    begin 
     if (not table.Fields[i].IsNull) or 
        (Length(Trim(table.Fields[i].AsString))
        > 0) then 
      temp := FormatDateTime
              ('hh:nn:ss', 
              table.Fields[i].AsDateTime) 
     else 
       //Put a valid default time 
      temp := '00:00:00';
    end; 
   end; 
    child1.appendChild(doc.createTextNode(temp)); 
  end; 
  table.Next; 
 end; 
  doc.save(xml+'.xml'); 
  memo1.lines.Append(doc.xml); 
  Result:=1; 
 except 
   on e:Exception do 
     Result:=-1; 
 end; 
end; 

Call the above function in Button1's OnClick event handler:

procedure TForm1.Button1Click(Sender: TObject); 
begin 
 if makeXml(table1)=1 then 
   showmessage('XML Generated') 
 else 
   showmessage('Error while generating XML File'); 
end;

If you open the generated XML file (country.xml) in IE, it should look something like:

<country>
<Records>
<Name>Argentina</Name>
<Capital>Buenos Aires</Capital>
<Continent>South America</Continent>
<Area>2777815</Area>
<Population>32300003</Population>
</Records>
<Records>
<Name>Bolivia</Name>
<Capital>La Paz</Capital>
<Continent>South America</Continent>
<Area>1098575</Area>
<Population>7300000</Population>
</Records>
...
<Records>
<Name>Venezuela</Name>
<Capital>Caracas</Capital>
<Continent>South America</Continent>
<Area>912047</Area>
<Population>19700000</Population>
</Records>
</country>

From XML to Database
You have created the XML file from the existing data in the country table. So the data in the generated XML file and the country table are same. If you try to insert the data into the country table from the generated XML file without deleting the existing data in the country table it will show the error "Primary key violation". Therefore you must delete all the existing data from the table before going on with the following actions/code.

Add another Button control (Button2) and a Memo to the main form. Add the next code to the OnClick event of Button2. The Memo is for displaying the status of insertion (sussess/failure).

procedure TForm1.Button2Click(Sender: TObject); 
var 
   i,ret_val,count : Integer; 
   strData : String; 
begin 
  {
  Before inserting data in to the country table,
  make sure that the data in the generated xml
  file (country.xml) and country table (DBDEMOS)
  are different.
  }
  try 
   count:=1; 
   DataList:=TStringList.Create; 
   memo1.Clear; 
   doc := CreateOleObject('Microsoft.XMLDOM')
          as IXMLDomDocument; 
    //Load country.xml file 
    doc.load('country.xml'); 
    nlist:=doc.getElementsByTagName('Records'); 
    memo1.lines.append('Table Name  :country'); 
    memo1.lines.append('---------------------'); 
    for i:=0 to nlist.Get_length-1 do begin 
     travelChildren
        (nlist.Get_item(i).Get_childNodes); 
     //Removes the first character(,)
     //from dataRecord 
     strData:=copy(dataRecord,2,length(dataRecord)); 
     memo1.lines.append(strData); 
     dataRecord:=''; 
     ret_val:=insertintotable(Datalist); 
     if ret_val=1 then 
      memo1.lines.append
       ('Data inserted successfully.............!') 
     else if ret_val=-1 then 
     memo1.lines.append
     ('Error while updating.....Try again.....!'); 
     memo1.lines.append
     ('=======(Record no. :'+inttostr(count)+')'); 
     DataList.Clear; 
     count:=count+1; 
    end; 
   except 
     on e:Exception do 
       Showmessage(e.message); 
  end; 
end;

Note: nlist variable contains a list of nodes. In our case the first node list is as follows:

...
<Records>
<Name>Argentina</Name>
<Capital>Buenos Aires</Capital>
<Continent>South America</Continent>
<Area>2777815</Area>
<Population>32300003</Population>
</Records>
...

We are passing this node list to a recursive function travelchildren. This will recursively travel through the node list until it finds a text node (data). This text data will be added to a TStringlist (Datalist) variable. When the travelchildren completes the first travel through the node list(nlist), Datalist will contain the strings Argentina, Buenos Aires, South America, 2777815, 32300003. Finally we pass this stringlist (Datalist) to the function insertintotable, which will insert one record in to the selected (Country) table. This will be repeated for the whole XML file.

procedure TForm1.travelChildren
                 (nlist1:IXMLDOMNodeList); 
var 
   j:Integer; 
   temp:String; 
begin 
  for j:=0 to nlist1.Get_length-1 do begin 
  //node type 1 means an entity
  //and node type 5 means EntityRef 
  if((nlist1.Get_item(j).Get_nodeType= 1)
     or (nlist1.Get_item(j).Get_nodeType=5)) then 
    travelChildren
      (nlist1.Get_item(j).Get_childNodes) 
    //node Type 3 means a text node,
    //ie you find the data 
    else if(nlist1.Get_item(j).Get_nodeType=3) then 
    begin 
     temp:= trim(nlist1.Get_item(j).Get_nodeValue); 
     //this is for displaying a
     //single record on the memo
     dataRecord:=dataRecord+','+temp; 
     //Datalist will contain one record
     //after completing one full travel
     //through the node list 
     DataList.Add(temp); 
    end 
  end; 
 end; 

function TForm1.insertintotable
               (stpt:TStringList):Integer; 
var 
  i:Integer; 
begin 
  table1.close; 
  table1.open; 
  table1.Insert; 
  for i := 0 to stpt.Count - 1 do begin 
    table1.Fields[i].AsVariant:= stpt[i]; 
  end; 
  try 
    table1.post; 
    result:=1; 
  except 
    on E:Exception do 
      result:=-1; 
  end; 
end; 

Conclusion
The code from this article is available for download. You can generalize this program for any database, so that data can be transfered through XML files in a network (or over the Internet) and update a database at the other end. I have not consider the special characters like &,<,>,','', etc. while generating the XML files from tables. You can change this program in such a way to replace this special characters with corresponding XML equivilents while generating XML file.

Don't forget to post your questions, concerns, views and comments to this article on the Delphi Programming Forum.

All graphics (if any) in this feature created by Zarko Gajic.

More Delphi
· Learn another routine every day - RTL Quick Reference.
· Download free source code applications and components.
· Talk about Delphi Programming, real time.
· Link to the Delphi Programming site from your Web pages.
· Tutorials, articles, tech. tips by date: 2001|2000|1999|1998 or by TOPIC.
· NEXT ARTICLE: Can programming hurt?.
Tips to make you feel better after your computer gives you the pain in the back.
Stay informed with all new and interesting things about Delphi (for free).
Subscribe to the Newsletter
Name
Email

Got some code to share? Got a question? Need some help?

©2014 About.com. All rights reserved.