Solved

Xml to Paradox database table

Posted on 2006-06-12
17
501 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
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
 
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Styling your websites can become very complex. Here I'll show how SASS can help you better organize, maintain and reuse your CSS code.
SASS allows you to treat your CSS code in a more OOP way. Let's have a look on how you can structure your code in order for it to be easily maintained and reused.
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
The viewer will learn how to dynamically set the form action using jQuery.

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now