asked on

Table to XML (Visa Versa)

This question kinda goes with my other question: JPG to Base64

I need function which creates a XML file from a database table depending on first character of text of a particular field (FieldName) return true is successful and false if it failed, passing a errorcode

Function ExportToXML(Table: TTable; FieldName: String; C: CHar; FileNanme: String; var ErrorCode: Integer): Boolean
begin
//
end;

I need function which loads the XML file to a database table and inserts each record if the record doesn' already exists (using fieldName) to searchon

Function ImportFromXML(Table: TTable; FieldName: String; FileName: String): Boolean
begin
//
end;

Show an example using the following field types

Integer
Float
String
Date
Time
DateTime
Memo
Graphic (only JPGs used)

memo and image (jpg) field data should be encoded in the XML file when exporting and decoded back when importing

Im willing to use an unit or component (as long as they are free and are free to use in commercial apps)

calinutz

Example of exporting xml to ADO

unit uxmltoado;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, MSXML2_TLB, DB, ADODB, Grids, DBGrids, StdCtrls, ActiveX, COMObj;

type
TForm1 = class(TForm)
Memo1: TMemo;
Button1: TButton;
DataSource1: TDataSource;
DBGrid1: TDBGrid;
ADOTable1: TADOTable;
ADOConnection1: TADOConnection;
ADOTable1type: TWideStringField;
ADOTable1value: TWideStringField;
ADOTable1align: TWideStringField;
ADOTable1face: TWideStringField;
ADOTable1size: TWideStringField;
ADOTable1style: TWideStringField;
ADOTable1voffset: TWideStringField;
ADOTable1mroffset: TWideStringField;
ADOTable1break: TWideStringField;
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;

var
Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
var
oXMLDoc: IXMLDOMDocument2;
s: String;
oTicketNodeList: IXMLDOMNodeList;
oDataNodeList: IXMLDOMNodeList;
oDataNode: IXMLDOMNode;
oDataAttribs: IXMLDOMNamedNodeMap;
oDataAttrib: IXMLDOMNode;
i, j, x: Integer;
sType: String;
sValue: String;
sAlign: String;
sFace: String;
sSize: String;
sStyle: String;
sVoffset: String;
sMroffset: String;
sBreak: String;
begin
oXMLDoc := CreateOleObject('MSXML2.DOMDocument.3.0') as IXMLDOMDocument2;
try
s := StringReplace(StringReplace(Memo1.Lines.Text, #13, '', [rfReplaceAll]), #10, '', [rfReplaceAll]);
oXMLDoc.loadXML(s);
// GET the ticket nodes
oTicketNodeList := oXMLDoc.selectNodes('//Ticket');
for i := 0 to oTicketNodeList.length-1 do
begin
// get the DATA nodes
oDataNodeList := oTicketNodeList.item[i].childNodes;
for j := 0 to oDataNodeList.length-1 do
begin
oDataNode := oDataNodeList.item[j];
// Get the Attributes of the DATA node
// Clear out old values from last DATA node.
sType := '';
sValue := '';
sAlign := '';
sFace := '';
sSize := '';
sStyle := '';
sVoffset := '';
sMroffset := '';
sBreak := '';
oDataAttribs := oDataNode.attributes;
for x := 0 to oDataAttribs.length-1 do
begin
oDataAttrib := oDataAttribs.item[x];
if oDataAttrib.nodeName = 'type' then
sType := oDataAttrib.text;
if oDataAttrib.nodeName = 'value' then
sValue := oDataAttrib.text;
if oDataAttrib.nodeName = 'align' then
sAlign := oDataAttrib.text;
if oDataAttrib.nodeName = 'face' then
sFace := oDataAttrib.text;
if oDataAttrib.nodeName = 'size' then
sSize := oDataAttrib.text;
if oDataAttrib.nodeName = 'style' then
sStyle := oDataAttrib.text;
if oDataAttrib.nodeName = 'voffset' then
sVoffset := oDataAttrib.text;
if oDataAttrib.nodeName = 'mroffset' then
sMroffset := oDataAttrib.text;
if oDataAttrib.nodeName = 'break' then
sBreak := oDataAttrib.text;
end;
ADOTable1.InsertRecord([sType, sValue, sAlign, sFace, sSize, sStyle, sVoffset, sMroffset, sBreak]);
Application.ProcessMessages;
end;
end;
finally
oXMLDoc := nil;
end;
end;

end.

calinutz

Export Dataset to XML:

http://www.swissdelphicenter.ch/en/showcode.php?id=772

Manipulating xml files:
http://delphi.about.com/od/windowsshellapi/a/xml_delphi.htm
http://www.philo.de/xml/downloads.shtml

More...:

https://www.experts-exchange.com/questions/21690696/Exporting-to-xml-with-ado.html

Regards

Pierre Cornelius

You could also use a clientdataset. See demo below:

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

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, DB, Grids, DBGrids, StdCtrls, DBTables, DBClient;

type
TForm1 = class(TForm)
Button1: TButton;
DataSource1: TDataSource;
DBGrid1: TDBGrid;
Table1: TTable;
Button2: TButton;
OpenDialog1: TOpenDialog;
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
end;

var
Form1: TForm1;

implementation

{$R *.dfm}

Function ExportToXML(ATable: TTable; AFieldName: String; C: CHar; AFileName: String): Boolean;
var cds: TClientDataSet;
i: integer;
s: string;
begin
result:= false;
if ATable.FieldByName(AFieldName).DataType <> ftString
then raise exception.Create('Field must be a string field');

cds:= TClientDataSet.Create(nil);
try
for i:= 0 to ATable.FieldCount-1
do cds.FieldDefs.Add(ATable.Fields[i].FieldName,
ATable.Fields[i].DataType,
ATable.Fields[i].Size,
ATable.Fields[i].Required);
cds.CreateDataSet;
cds.Open;
ATable.First;
While not ATable.Eof do
begin
s:= ATable.FieldByName(AFieldName).Value;
if (Length(s) > 0) AND (UpCase(s[1]) = C) then
begin
cds.Append;
for i:= 0 to ATable.FieldCount-1
do cds.FieldByName(ATable.Fields[i].FieldName).Value
:= ATable.Fields[i].Value;
cds.Post;
end;
ATable.Next;
end;

cds.SaveToFile(AFileName, dfXML);
result:= true;
finally
cds.Free;
end;
end;

Function ImportFromXML(ATable: TTable; AFieldName: String; AFileName: String): Boolean;
var cds: TClientDataSet;
i: integer;
f: TField;
s: string;
begin
result:= false;
cds:= TClientDataSet.Create(nil);
try
cds.LoadFromFile(AFileName);
cds.First;

//run updates within a transactions so that if the
//function fails halfway, no records are added
ATable.Database.TransIsolation:= tiDirtyRead;
ATable.Database.StartTransaction;
try
while not cds.Eof do
begin
if NOT ATable.Locate(AFieldName, cds.FieldByName(AFieldName).Value, [loCaseInsensitive]) then
begin
ATable.Append;
for i:= 0 to cds.Fields.Count-1 do
begin
f:= ATable.FindField(cds.Fields[i].FieldName);
if f <> nil
then f.Value:= cds.Fields[i].Value
else
if Pos(Format('Field %s not found in destination dataset',
[QuotedStr(cds.Fields[i].FieldName)]),
s) = 0
then s:= s + Format('Field %s not found in destination dataset',
[QuotedStr(cds.Fields[i].FieldName)])+#13#10;
end;
ATable.Post;
end;
cds.Next;
end;
ATable.Database.Commit;
if s <> '' then ShowMessage(s);
except
ATable.Database.Rollback;
end;

result:= true;
finally
cds.Free;
end;
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
ExportToXML(Table1, 'LastName', 'F', 'C:\Temp\delme.xml');
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
if OpenDialog1.Execute then
ImportFromXML(Table1, 'LastName', OpenDialog1.FileName);
end;

end.

DFM File:
=====================================================
object Form1: TForm1
Left = 192
Top = 114
Width = 696
Height = 314
Caption = 'Form1'
Color = clBtnFace
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'MS Sans Serif'
Font.Style = []
OldCreateOrder = False
PixelsPerInch = 96
TextHeight = 13
object Button1: TButton
Left = 16
Top = 16
Width = 89
Height = 25
Caption = 'Export to XML'
TabOrder = 0
OnClick = Button1Click
end
object DBGrid1: TDBGrid
Left = 16
Top = 56
Width = 633
Height = 209
DataSource = DataSource1
TabOrder = 1
TitleFont.Charset = DEFAULT_CHARSET
TitleFont.Color = clWindowText
TitleFont.Height = -11
TitleFont.Name = 'MS Sans Serif'
TitleFont.Style = []
end
object Button2: TButton
Left = 120
Top = 16
Width = 89
Height = 25
Caption = 'Import to XML'
TabOrder = 2
OnClick = Button2Click
end
object DataSource1: TDataSource
DataSet = Table1
Left = 112
Top = 104
end
object Table1: TTable
Active = True
DatabaseName = 'DBDEMOS'
TableName = 'employee.db'
Left = 144
Top = 104
end
object OpenDialog1: TOpenDialog
Filter = 'XML File|*.xml'
FilterIndex = 0
InitialDir = 'C:\Temp'
Left = 232
Top = 16
end
end

Regards
Pierre

XYZCode

ASKER

Thanks for responding (both of you)...however, I am more interested in how to place the Image and memo into the xml file - rather than the standard data types (integer, string, number, date, etc)....although i did ask for those too. If you can modify your answers to include the addition of images and memo fields into the XML file, I will accespt your answer.

I am assuming the answer will require the image (Jpg) and or memo field to be converted to text some how (base64 encoding) and then inserted into a CDATA section

THanks

ASKER CERTIFIED SOLUTION

Pierre Cornelius

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial