Solved

Xml to Paradox database table

Posted on 2006-06-12
17
523 Views
Last Modified: 2013-11-19
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
0
Comment
Question by:Asw
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
17 Comments
 
LVL 17

Expert Comment

by:TheRealLoki
ID: 16890321
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
 
LVL 1

Author Comment

by:Asw
ID: 16891916
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
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 16894222
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
WordPress Tutorial 1: Installation & Setup

WordPress is a very popular option for running your web site and can be used to get your content online quickly for the world to see. This guide will walk you through installing the WordPress server software and the initial setup process.

 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 16894270
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
 
LVL 1

Author Comment

by:Asw
ID: 16894543
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
 
LVL 4

Expert Comment

by:LMuadDIb
ID: 16896071
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
 
LVL 1

Author Comment

by:Asw
ID: 16897025
Thanks LMuadDIb, I'll give that a try.

Asw
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 16897075
Can you post the xml file sample somewhere so we can download it...
0
 
LVL 1

Author Comment

by:Asw
ID: 16897192
Hi PierreC,

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

Thanks
Andy
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 16897606
having difficulties downloading the file. only getting to 155kb.
0
 
LVL 1

Author Comment

by:Asw
ID: 16897745
Hi PierreC,

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

Thanks
Andy
0
 
LVL 1

Author Comment

by:Asw
ID: 16898850
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
 
LVL 14

Accepted Solution

by:
Pierre Cornelius earned 500 total points
ID: 16900719
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
 
LVL 1

Author Comment

by:Asw
ID: 16900829
Hi PierreC ,

Can you email me the demo project?

Thanks
Andy
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 16901974
I e-mailed it to you, but all you need is in my post above...
0
 
LVL 1

Author Comment

by:Asw
ID: 16902633
Hi PierreC,

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

Thank you
Andy
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 16903909
Glad to help.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
JavaScript has plenty of pieces of code people often just copy/paste from somewhere but never quite fully understand. Self-Executing functions are just one good example that I'll try to demystify here.
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question