Fill FDQuery/Dataset with stored DB infos from XML file

Question

I have an XML file where I store database infos (TableName, Records etc) Are there any way to load Records to a Query or Dataset from the file? I want to change TablaName parameters and after I want to fill the database with Table Records. Thanks for the answers!

My XML looks like:

<?xml version="1.0" encoding="UTF-8"?>
-<Data>
  -<Table Sql="Select * from VAT_RATE " MinimumCapacity="50" EnforceConstraints="False" TabID="0" SourceID="1" SourceName="VAT_RATE" Name="FDQuery">
   -<ColumnList>
     <Column SourceID="1" SourceName="VAT_RATE_ID" Name="VAT_RATE_ID" OriginColName="VAT_RATE_ID" OriginTabName="VAT_RATE" OInKey="True" OInWhere="True" OInUpdate="True" Base="True" Searchable="True" DataType="Int32"/>
     <Column SourceID="2" SourceName="BEGINS" Name="BEGINS" OriginColName="BEGINS" OriginTabName="VAT_RATE" OInWhere="True" OInUpdate="True" Base="True" Searchable="True" DataType="Date" OAllowNull="True" AllowNull="True"/>
     <Column SourceID="3" SourceName="ENDS" Name="ENDS" OriginColName="ENDS" OriginTabName="VAT_RATE" OInWhere="True" OInUpdate="True" Base="True" Searchable="True" DataType="Date" OAllowNull="True" AllowNull="True"/>
     <Column SourceID="4" SourceName="NAME" Name="NAME" OriginColName="NAME" OriginTabName="VAT_RATE" OInWhere="True" OInUpdate="True" Base="True" Searchable="True" DataType="AnsiString" SourceSize="20" Size="20"/>
     <Column SourceID="5" SourceName="ACCOUNT" Name="ACCOUNT" OriginColName="ACCOUNT" OriginTabName="VAT_RATE" OInWhere="True" OInUpdate="True" Base="True" Searchable="True" DataType="AnsiString" SourceSize="20" Size="20"/>
     <Column SourceID="6" SourceName="KEY" Name="KEY" OriginColName="KEY" OriginTabName="VAT_RATE" OInWhere="True" OInUpdate="True" Base="True" Searchable="True" DataType="Currency" SourceScale="2" SourcePrecision="9" Scale="2" Precision="9"/>
     <Column SourceID="7" SourceName="D_EFAULT" Name="D_EFAULT" OriginColName="D_EFAULT" OriginTabName="VAT_RATE" OInWhere="True" OInUpdate="True" Base="True" Searchable="True" DataType="Int16"/>
     <Column SourceID="8" SourceName="count_ID" Name="count_ID" OriginColName="count_ID" OriginTabName="VAT_RATE" OInWhere="True" OInUpdate="True" Base="True" Searchable="True" DataType="Int32"/>
   </ColumnList>
   <ConstraintList/>
    <ViewList/>
   -<RowList>
     -<Row RowState="Unchanged" RowID="0">
       <Original count_ID="14" D_EFAULT="0" KEY="20" ACCOUNT="20%" NAME="20%" ENDS="20090630" BEGINS="20051001" VAT_RATE_ID="1"/>
      </Row>
     -<Row RowState="Unchanged" RowID="1">
       <Original count_ID="14" D_EFAULT="0" KEY="18" ACCOUNT="18%" NAME="18%" BEGINS="20040101" VAT_RATE_ID="3"/>
     </Row>
   </RowList>
  </Table>
</Data>

In the example TablneName is ... SourceName="VAT_RATE"

Answer 1

The easiest way to do this seems to be to modify the SaveTXML method in my answer to your earlier q (Append TFDMemTable data into one XML file) as shown below and add a LoadFromXML method.

The reason for modifying the SaveToXML is that an FDQuery requires the Manager node of the XML that it generates when doing a save, and the earlier version of my SaveToXML stripped it out.

In the LoadFromXML, the query + data which is loaded it determined by this statement

    Node := NodeList.item[0];

NodeList is the list of child nodes of the saved XML's top-level document element, anditem[0] is simply the first one of these nodes (they are numbered 0..NodeList.length - 1). So to load a different one, you could set the item[] number to suit. Depending on how familiar you are with XML XPath queries, there are various other ways to select the XML node whose query + data you reload, but XPath is a completely different topic.

Btw, make sure you delete or rename any version of the XML file which was saved prior to making the change to SaveToXML because the previous version is incompatible with theLoadFromXML.

Code:

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.LoadFromXML(FDQuery: TFDQuery);
var
  SS : TStringStream;
  XmlDoc : IXmlDOMDocument;
  NodeList: IXmlDomNodeList;
  Node: IXmlDomNode;
  Element : IXmlDomElement;
begin
  if FDQuery.Active then
    FDQuery.Close;
  SS := TStringStream.Create;
  XmlDoc := CoDomDocument.Create;
  try
    XmlDoc.load('file://' + scSavedData);
    NodeList := XmlDoc.documentElement.childNodes;
    Node := NodeList.item[0];
    SS.WriteString(Node.xml);
    FDQuery.LoadFromStream(SS, sfXML);
  finally
    SS.Free;
  end;
end;

procedure TForm1.SaveToXML(FDQuery: TFDQuery);
var
  SS : TStringStream;
  XmlDoc1,
  XMlDoc2 : IXMLDOMDocument2;
  nDestination : IXmlDomNode;
  eDestination : IXmlDomElement;
begin
  SS := TStringStream.Create;
  XmlDoc1 := CoDomDocument.Create;
  try
    FDQuery.SaveToStream(SS, sfXML);
    XmlDoc1.loadXML(SS.DataString);
    Memo1.Lines.Text := SS.DataString;

    XmlDoc2 := CoDomDocument.Create;
    if FileExists(scSavedData) then begin
      XmlDoc2.load(scSavedData)
    end
    else begin
      XmlDoc2.loadXML(scSavedDataXML);
    end;
    nDestination := XmlDoc1.DocumentElement.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;
  end;
end;