Xml to Paradox database table

Hi,

I have a Xml file containing over 29000 records.

I need to import them into a paradox database, how can I do this?

I purchased smimport component, but this only brings in a few records.

I have no expeirence with xml.

500 points for the solution.

Thanks
Andy
LVL 1
AswAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Pierre CorneliusConnect With a Mentor Commented:
I have prepared a demo. What it does is load the XML into a treeview using TXMLDocument (part of delphi VCL) and then extracts the LaborTable records to a paradox table which the demo app creates.

PAS File:
============================================================
unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Grids, DBGrids, ExtCtrls, DBTables, DB, DBClient,
  xmldom, XMLIntf, msxmldom, XMLDoc, ComCtrls;

type
  TForm1 = class(TForm)
    Table1: TTable;
    Panel1: TPanel;
    Panel2: TPanel;
    Panel3: TPanel;
    Panel4: TPanel;
    DBGrid2: TDBGrid;
    Panel5: TPanel;
    btnCreatePDox: TButton;
    DataSource2: TDataSource;
    btnLoadXML: TButton;
    OpenDialog1: TOpenDialog;
    XMLDocument1: TXMLDocument;
    Button2: TButton;
    TreeView1: TTreeView;
    procedure FormCreate(Sender: TObject);
    procedure btnCreatePDoxClick(Sender: TObject);
    procedure btnLoadXMLClick(Sender: TObject);
    procedure Button2Click(Sender: TObject);
  private
  public
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure DOMToMemo(XMLNode: IXMLNode; Memo: TMemo);
var s: string;
    xn: IXMLNode;
    i: integer;
begin
  if XMLNode.NodeType <> ntElement then Exit;
  s:= XMLNode.NodeName;
  if XMLNode.IsTextElement
    then s:= XMLNode.NodeName+'='+XMLNode.NodeValue;
  Memo.Lines.Add(s);
  for i:= 0 to XMLNode.AttributeNodes.Count-1 do
  begin
    xn:= XMLNode.AttributeNodes.Nodes[i];
    s:= '   '+xn.NodeName+'='+xn.NodeValue;
    Memo.Lines.Add(s);
  end;

  if XMLNode.HasChildNodes then
    for i:= 0 to XMLNode.ChildNodes.Count-1
      do DOMToMemo(XMLNode.ChildNodes.Nodes[i], Memo);
end;

procedure DOMToTree(XMLNode: IXMLNode; TreeNode: TTreeNode; TreeView: TTreeView);
var s: string;
    xn: IXMLNode;
    i: integer;
    NewTreeNode: TTreeNode;
begin
  if XMLNode.NodeType <> ntElement then Exit;
  s:= XMLNode.NodeName;
  if XMLNode.IsTextElement
    then s:= XMLNode.NodeName+'='+XMLNode.NodeValue;
  NewTreeNode:= TreeView.Items.AddChild(TreeNode, s);
  for i:= 0 to XMLNode.AttributeNodes.Count-1 do
  begin
    xn:= XMLNode.AttributeNodes.Nodes[i];
    s:= '   '+xn.NodeName+'='+xn.NodeValue;
    TreeView.Items.AddChild(NewTreeNode, s);
  end;

  if NewTreeNode.Text = 'Record'
    then beep;
  if XMLNode.HasChildNodes then
    for i:= 0 to XMLNode.ChildNodes.Count-1
      do DOMToTree(XMLNode.ChildNodes.Nodes[i], NewTreeNode, TreeView);
end;

procedure TForm1.FormCreate(Sender: TObject);
var path: string;
begin
  path:= ExtractFilePath(ParamStr(0));
  if Not DirectoryExists(path+'data')
    then CreateDir(Path + 'data');
  Session.NetFileDir:= Path;
  Session.PrivateDir:= Path + 'data';

  //Database1.DatabaseName:=path+'\data';
  Table1.DatabaseName:=path+'data';
  Table1.TableName:= 'Temp.db';
  Table1.TableLevel:= 7;
  Table1.TableType:= ttParadox;
end;

procedure TForm1.btnCreatePDoxClick(Sender: TObject);
begin
  Table1.Close;
  if Table1.Exists AND
    (MessageDlg('Delete existing table?', mtWarning,
               [mbOK, mbCancel], 0) <> mrOK)
    then abort;

  if Table1.Exists then Table1.DeleteTable;
  Table1.CreateTable;
  Table1.Open;
end;

procedure TForm1.btnLoadXMLClick(Sender: TObject);
begin
  if OpenDialog1.Execute then
  begin
    XMLDocument1.FileName:= OpenDialog1.FileName;
    XMLDocument1.LoadFromFile();
    DOMToTree(XMLDocument1.DocumentElement, nil, TreeView1);
  end;
end;

procedure TForm1.Button2Click(Sender: TObject);
  procedure ExportToPDox(n: TTreeNode);
  var id,p: integer;
      f: TField;
      s,v: string;
  begin
    Table1.Append;
    for id:= 0 to n.Count-1 do
    begin
      s:= n.Item[id].Text;
      p:= pos('=', s);
      if p=0 then Continue;
      v:= copy(s, p+1, length(s)-p);
      s:= copy(s, 1, p-1);
      f:= Table1.FindField(s);
      if f <> nil
        then f.Value:= v;
    end;
    Table1.Post;
  end;
var i,j: integer;
    tn,tn2: TTreeNode;
    xn: IXMLNode;
begin
  //find LaborTable node
  for i:= 0 to TreeView1.Items.Count-1 do
    if (UpperCase(TreeView1.Items[i].Text) = 'LABORTABLE') then
    begin
      tn:= TreeView1.Items[i];
      break;
    end;

  for i:= 0 to tn.Count-1 do
    if tn.Item[i].Text = 'Record'
      then ExportToPDox(tn.Item[i]);
end;

end.




DFM File:
===============================================================
object Form1: TForm1
  Left = 192
  Top = 114
  Width = 696
  Height = 480
  Caption = 'Form1'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  OnCreate = FormCreate
  PixelsPerInch = 96
  TextHeight = 13
  object Panel1: TPanel
    Left = 0
    Top = 41
    Width = 688
    Height = 185
    Align = alTop
    Caption = 'Panel1'
    TabOrder = 0
    object Panel3: TPanel
      Left = 1
      Top = 1
      Width = 686
      Height = 24
      Align = alTop
      Caption = 'XML'
      TabOrder = 0
    end
    object TreeView1: TTreeView
      Left = 1
      Top = 25
      Width = 686
      Height = 159
      Align = alClient
      Indent = 19
      TabOrder = 1
    end
  end
  object Panel2: TPanel
    Left = 0
    Top = 0
    Width = 688
    Height = 41
    Align = alTop
    TabOrder = 1
    object btnCreatePDox: TButton
      Left = 128
      Top = 8
      Width = 105
      Height = 25
      Caption = 'Create PDox table'
      TabOrder = 0
      OnClick = btnCreatePDoxClick
    end
    object btnLoadXML: TButton
      Left = 8
      Top = 8
      Width = 105
      Height = 25
      Caption = 'Load XML'
      TabOrder = 1
      OnClick = btnLoadXMLClick
    end
    object Button2: TButton
      Left = 248
      Top = 8
      Width = 113
      Height = 25
      Caption = 'Extract LaborTable'
      TabOrder = 2
      OnClick = Button2Click
    end
  end
  object Panel4: TPanel
    Left = 0
    Top = 226
    Width = 688
    Height = 220
    Align = alClient
    Caption = 'Panel1'
    TabOrder = 2
    object DBGrid2: TDBGrid
      Left = 1
      Top = 25
      Width = 686
      Height = 194
      Align = alClient
      DataSource = DataSource2
      TabOrder = 0
      TitleFont.Charset = DEFAULT_CHARSET
      TitleFont.Color = clWindowText
      TitleFont.Height = -11
      TitleFont.Name = 'MS Sans Serif'
      TitleFont.Style = []
    end
    object Panel5: TPanel
      Left = 1
      Top = 1
      Width = 686
      Height = 24
      Align = alTop
      Caption = 'Paradox'
      TabOrder = 1
    end
  end
  object Table1: TTable
    FieldDefs = <
      item
        Name = 'CrewCode'
        DataType = ftString
        Size = 20
      end
      item
        Name = 'Description'
        DataType = ftString
        Size = 80
      end
      item
        Name = 'LaborRate'
        DataType = ftCurrency
      end>
    StoreDefs = True
    TableType = ttParadox
    Left = 64
    Top = 304
  end
  object DataSource2: TDataSource
    DataSet = Table1
    Left = 104
    Top = 306
  end
  object OpenDialog1: TOpenDialog
    Filter = 'XML Files|*.xml'
    Left = 648
    Top = 8
  end
  object XMLDocument1: TXMLDocument
    FileName = 'C:\Delphi\EE\XML_PDox_Import\xmlSample.xml'
    Left = 248
    Top = 104
    DOMVendorDesc = 'MSXML'
  end
end


Kind Regards
Pierre
0
 
TheRealLokiSenior DeveloperCommented:
what tags does your xml file have? do you have a paradox table ready with the appropriate fields? What fields are these, and what data types are they? We can write you a simple import routine, but we will need to know these facts first.
0
 
AswAuthor Commented:
Hi TheRealLoki,

I can create the table with all the fields needed, I just need to be able to import the file.

Send your your e-mail address to me and I will send you a demo version of the file, so you can see for youself.

e-mail andy@duralead.com

Many Thanks
Andy
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Pierre CorneliusCommented:
What version of Delphi are you using? If you have access to TClientDataset, you can open the XML file and simply cycle through all the records while pasting to the paradox table/query. Can do a demo if you like...

Regards
Pierre
0
 
Pierre CorneliusCommented:
assuming the xml and paradox table/query have the same fieldnames, you could do something like this

1. Open the xml file using TClientDataset

2. Cycle through each record doing something like this (from head):

...
var i: integer;
begin
  While not cdsXMLData.EOF do
  begin
    tabPDox.Append;
    for i:= 0 to cdsXMLData.FieldCount-1
      do tabPDox.FieldByName(cdsXMLData.Field[i].FieldName).value:= cdsXMLData.Field[i].value;
    tabPDox.Post;
    cdsXMLData.Next;
  end;
...


Regards
Pierre
0
 
AswAuthor Commented:
Hi PierreC,

I'm driving Delphi 7 Enterprise.

I sent you the demo file to look at.

I can create the database fields to suite the xml file, then I can weed thought the data and use what I need.

Thanks
Andy
0
 
LMuadDIbCommented:
I have loaded xml docs into MSSQL & MySQL databases using XSD Schema for Bulkloading or Delimnated TextFiles,
and most import failures are caused by incorrect field mapping. You said you used the smimport component, but it only imports a few records before failing. I would check the xml data at the item it failed at, maybe remove that item and see if the import will continue once its removed.
0
 
AswAuthor Commented:
Thanks LMuadDIb, I'll give that a try.

Asw
0
 
Pierre CorneliusCommented:
Can you post the xml file sample somewhere so we can download it...
0
 
AswAuthor Commented:
Hi PierreC,

The file is here: http://asw6464.tripod.com/xml/XML_File.zip

Thanks
Andy
0
 
Pierre CorneliusCommented:
having difficulties downloading the file. only getting to 155kb.
0
 
AswAuthor Commented:
Hi PierreC,

Email me your address and I'll send it to you.

Thanks
Andy
0
 
AswAuthor Commented:
Hi TheRealLoki ,

The file keeps comming back, must be some thing wrong with your e-mail config?

Try download the file again, it downloads to my machine ok.

Thanks for your time

Andy
0
 
AswAuthor Commented:
Hi PierreC ,

Can you email me the demo project?

Thanks
Andy
0
 
Pierre CorneliusCommented:
I e-mailed it to you, but all you need is in my post above...
0
 
AswAuthor Commented:
Hi PierreC,

That has put me in the right direction, the example is excellent.

Thank you
Andy
0
 
Pierre CorneliusCommented:
Glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.