Append TFDMemTable data into one XML file

Question

Is there any way to save all the database records and structures for multiple queries into one XML file?

What I did is:

procedure CopyRecords(Sender: TObject);
begin
  try

  TFDQuery.SQL.Text := 'SELECT * FROM SAMPLE_TABLE1';
  TFDQuery.FetchOptions.Unidirectional := False;
  TFDQuery.Open;
  TFDQuery.FetchAll;

  TFDmemtable.Data := DM.qry_SQL.Data;
  TFDmemtable.First;

  while not TFDmemtable.Eof do
  begin
    TFDmemtable.Edit;

    TFDmemtable.Post;
    TFDmemtable.Next;
  end;

  TFDmemtable.SaveToFile('C:\Test.xml', sfXML); 
  finally
    TFDmemtable.Close;
  end;
end;

This works fine for one query, but if I change the SQL text and keep the file name the comtents gets overriden. I want to change the query (SQL.text) and save all the query data into one XML file.

TFDQuery.SQL.Text := 'SELECT * FROM SAMPLE_TABLE1';
... save ...
TFDQuery.SQL.Text := 'SELECT * FROM SAMPLE_TABLE2';
... save (append) ...

MartynA · Accepted Answer · 2016-03-21T23:12:16.620

If you want to create/append to an Xml file as you've described, you don't actually need to use an FDMemTable. You can do something like I've shown below, directly from your FDQuery.

As you'll see it basically

Saves a TFDQuery to a TStringStream in XML format;
Loads the contents of the StringStream into an XmlDocument object using Windows' MSXML DOM parser;
Loads an XML file of previously saved queries into a second XmlDocument; and
Copies the contents of the Table node from the saved query into the second XmlDocument and saves the document.

Code:

uses

  Data.DB, ADOInt, Data.Win.ADODB, Vcl.StdCtrls, Vcl.Grids, Vcl.DBGrids,
  System.Classes, SysUtils, Variants, Vcl.Forms, Vcl.Controls, Vcl.ExtCtrls, Vcl.DBCtrls,
  FireDAC.Stan.Intf, FireDAC.Stan.Option, FireDAC.Stan.Error, FireDAC.UI.Intf,
  FireDAC.Phys.Intf, FireDAC.Stan.Def, FireDAC.Stan.Pool, FireDAC.Stan.Async,
  FireDAC.Phys, FireDAC.Stan.Param, FireDAC.DatS, FireDAC.DApt.Intf,
  FireDAC.DApt, FireDAC.Comp.DataSet, FireDAC.Comp.Client, FireDAC.Phys.MSSQL,
  FireDAC.Phys.MSSQLDef, FireDAC.VCLUI.Wait, FireDAC.Comp.UI,
  Dialogs, FireDAC.Stan.StorageXML, WinApi.MSXMLIntf, WinApi.MSXML;

type
  TForm1 = class(TForm)
    DataSource1: TDataSource;
    DBNavigator1: TDBNavigator;
    DBGrid1: TDBGrid;
    FDConnection1: TFDConnection;
    FDQuery1: TFDQuery;
    FDGUIxWaitCursor1: TFDGUIxWaitCursor;
    Button1: TButton;
    Memo1: TMemo;
    FDStanStorageXMLLink1: TFDStanStorageXMLLink;  // This is needed to save the Query to XML
    [...]
  protected
    procedure SaveToXML(FDQuery: TFDQuery);
  end;

const
  scSavedData = 'D:\delphi\code\firedac\SavedData.Xml';
  scSavedSingleQuery = 'D:\delphi\code\firedac\SavedSingleQuery.Xml';
  scSavedDataXML = '<?xml version="1.0" encoding="utf-8"?><Data/>';

procedure TForm1.SaveToXML(FDQuery: TFDQuery);
var
  SS : TStringStream;
  XmlDoc1,
  XMlDoc2 : IXMLDOMDocument2;
  NodeList: IXmlDomNodeList;
  nSource: IXmlDomNode;
  nDestination : IXmlDomNode;
  eDestination : IXmlDomElement;
begin
  SS := TStringStream.Create;
  XmlDoc1 := CoDomDocument.Create;
  try
    FDQuery.SaveToStream(SS, sfXML);
    XmlDoc1.loadXML(SS.DataString);
    NodeList := XmlDoc1.documentElement.selectNodes('//FDBS/Manager/TableList/Table');
    nSource := NodeList.item[0];
    //  At this point, nSource is the XML node which contains the data + metadata
    //  of the FDQuery's result set
    //  Next we splice it into an XML file of previously saved FDQuery result sets
    //  or create this file if it doesn't already exist

    XmlDoc2 := CoDomDocument.Create;
    if FileExists(scSavedData) then begin
      XmlDoc2.load(scSavedData)
    end
    else begin
      XmlDoc2.loadXML(scSavedDataXML);
    end;
    nDestination := nSource.cloneNode(True) as IXmlDomNode;
    XmlDoc2.documentElement.appendChild(nDestination);
    eDestination := nDestination as IXmlDomElement;
    eDestination.setAttribute('Sql', FDQuery.SQL.Text);

    Memo1.Lines.Text := XmlDoc2.documentElement.Xml;
    XmlDoc2.save(scSavedData);
  finally
    SS.Free; //  Only this needs to be freed
    //  all the other (interface) objects will be finalized as the procedure exits
  end;
end;

Append TFDMemTable data into one XML file

1 Answers1

Linked