Load dynamic XML into dataset

Question

I'm trying to load an XML file into a Delphi dataset using mydataset.loadfromfile("myfile.xml")

The problem is that I don't know the XML field names and how many fields it has. I do know that the XML has just one level, for example:

<DOCTO id ="3892" usuario="2" data="22/12/2015 10:33:22">
  <CONDUTA_1>teste conduta 1</CONDUTA_1>
  <CONDUTA_2>teste conduta 2</CONDUTA_2>
</DOCTO>

I'm looking for a way to load the xml without creating the fields before.

Answer 1

As you've said you can't use XMLMapper, I've moved that section of my answer to the bottom.

Since you can't use XmlMapper you'll need to parse the XML yourself, using an XML parser. The example below uses the XML DOM Parser that comes with Windows, for which you need MSXML in your Uses list. To illustrate how to do this, I'm going to use XML with the same structure as yours but with abstract node- and attribute-names, to avoid your XML's node names and values being a distraction.

<data a1="a" a2="b" a3="c">
  <r1>11111</r1>
  <r2>22222</r2>
</data>

At one extreme, the field names might be the names a1, a2, a3 of the attributes of the <data> node and the field values might be these attributes' values.

How to get those is shown in the LoadFromAttributes routine. Basically, it loads the XML into the XMLDoc object, uses an XPath query to find the <data> node and them gets hold of its attributes to create the dataset fields and set their values.

At the other extreme, the sub-nodes <r1> and <r2> might be the field names and their node texts, the field values. The LoadFromNodes routine shows how to do that.

Of course, in practice, the sematics of your XML might mean that the field names and values are a mixture of the two, but as you haven't said anything about what your XML "means", I'll have to leave that to you.

  TForm1 = class(TForm)
    Memo1: TMemo;
    CDS1: TClientDataSet;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    btnAttributes: TButton;
    btnFromNodes: TButton;
    procedure FormCreate(Sender: TObject);
    procedure btnAttributesClick(Sender: TObject);
    procedure btnFromNodesClick(Sender: TObject);
  public
    XMLDoc : IXMLDOMDocument;
    procedure PrepareCDS;
    procedure LoadFromAttributes;
    procedure LoadFromNodes;
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

const
  scXML =
     '<data a1="a" a2="b" a3="c">'#13#10
   + '  <r1>11111</r1>'#13#10
   + '  <r2>22222</r2>'#13#10
   + '</data>';

   scRootNodeName = 'data';

procedure TForm1.FormCreate(Sender: TObject);
begin
  XmlDoc := CoDOMDocument.Create;
  XmlDoc.Async := False;
  XMLDoc.loadXML(scXML);
  Memo1.Lines.Text := scXML;
end;

procedure TForm1.LoadFromAttributes;
var
  PathQuery : String;
  NodeList : IXmlDOMNodeList;
  Node,
  AttrNode : IXmlDomNode;
  Attributes : IXMLDOMNamedNodeMap;
  Field : TField;
  i : Integer;
begin
  PrepareCDS;

  PathQuery := scRootNodeName;
  NodeList := XMLDoc.SelectNodes(PathQuery);
  Assert(NodeList.Length > 0);
  Node := NodeList.item[0];
  Attributes := Node.attributes;
  for i := 0 to Attributes.Length - 1 do begin
    AttrNode := Attributes.item[i];
    Field := TStringField.Create(Self);
    Field.Size := 80;
    Field.FieldName := AttrNode.nodeName;
    Field.DataSet := CDS1;
  end;

  CDS1.CreateDataSet;

  CDS1.Insert;
  for i := 0 to Attributes.Length - 1 do begin
    AttrNode := Attributes.item[i];
    CDS1.Fields[i].Value := AttrNode.nodeValue;
  end;
  CDS1.Post;
end;

procedure TForm1.LoadFromNodes;
var
  PathQuery : String;
  NodeList : IXmlDOMNodeList;
  Node,
  AttrNode : IXmlDomNode;
  Field : TField;
  i : Integer;
begin
  PrepareCDS;

  PathQuery := scRootNodeName + '/*';
  NodeList := XMLDoc.SelectNodes(PathQuery);
  Assert(NodeList.Length > 0);
  for i := 0 to NodeList.Length - 1 do begin
    Node := NodeList.item[i];
    Field := TStringField.Create(Self);
    Field.Size := 80;
    Field.FieldName := Node.nodeName;
    Field.DataSet := CDS1;
  end;

  CDS1.CreateDataSet;

  CDS1.Insert;
  for i := 0 to NodeList.Length - 1 do begin
    Node := NodeList.item[i];
    CDS1.Fields[i].Value := Node.Text;
  end;
  CDS1.Post;
end;

procedure TForm1.PrepareCDS;
begin
  if CDS1.Active then
    CDS1.Close;
  CDS1.FieldDefs.Clear;
  CDS1.Fields.Clear;
end;

procedure TForm1.btnAttributesClick(Sender: TObject);
begin
  LoadFromAttributes;
end;

procedure TForm1.btnFromNodesClick(Sender: TObject);
begin
  LoadFromNodes;
end;

As you can see, all this is quite straightforward with a little familiarity with the MS XML parser and the use of XPath (though in this case, the XML structure is so simple you don't really need to use XPath to get at it).

In your case, it doesn't apply, but if your XML structure is fixed in advance, you can use a utility, XmlMapper, which comes with Delphi, to define a mapping file to transform it into the format TClientDataSet uses. Then, you can use a TClientDataSet in combination with another component, TXmlTransformProvider, which uses the transform file to load your data into the CDS. Fuller details are here:

Parsing xml file Delphi

Btw, at the risk of stating the obvious, if your .Xml file actually has multiple <DOCTO> nodes, you'll need to place them under a root node so that the file has a valid XML structure.