Reading distinct values from XML using XPath

Question

I have an XML in the following format:

<Accounts>
   <Account Number="1"   DebitAmount="1000" Amount="2827561.95" /> 
   <Account Number="225" DebitAmount="2000"  Amount="12312.00" /> 
   <Account Number="236" DebitAmount="London"    Amount="457656.00" /> 
   <Account Number="225" DebitAmount="London"    Amount="23462.40" /> 
   <Account Number="236" DebitAmount="Bangalore" Amount="2345345.00" /> 
</Accounts>

How do I retreive the unique Account Numbers using Xpath? ie, I want to get the values 1, 225 and 236.

This is what I did:(I'm using Delphi 2007...)

Const XmlStr =
' <Accounts>
   <Account Number="1"   DebitAmount="1000" Amount="2827561.95" /> 
   <Account Number="225" DebitAmount="2000"  Amount="12312.00" /> 
   <Account Number="236" DebitAmount="London"    Amount="457656.00" /> 
   <Account Number="225" DebitAmount="London"    Amount="23462.40" /> 
   <Account Number="236" DebitAmount="Bangalore" Amount="2345345.00" /> 
</Accounts>';

 function GetAccountNumbers:TList;
 Var
   XMLDOMDocument  : IXMLDOMDocument;
   accounts : IXMLDOMNodeList;
  accountdetail :IXMLDOMNode;
   i:Integer
   list :TList
 begin
   Result:=TList.Create;
   XMLDOMDocument:=CoDOMDocument.Create;
   XMLDOMDocument.loadXML(XmlStr);
   accounts:= XMLDOMDocument.SelectNodes(''./Accounts 
  /Account[not(@Number=preceding-sibling/ Account /@Number)]');
  for i := 0 to accountdetails.length - 1 do begin
     accountdetail := accountdetails.item[i];
     //omitting the "<>nil" checks...
     list.Add(accountdetail.attributes.getNamedItem('Number').Nodevalue;
  end;
 end;

But this returns no nodes(accountdetails.length=0). Please let me know what I am missing here.

Thanks,

Pradeep

Is that piece part of your real code ? Looking at it, it won't even compile. — TLama, Jun 05 '12 at 08:29
I've copied that; but didn't corrected the apostrophes. I just wanted to give an idea of what I did... — Pradeep, Jun 05 '12 at 08:45
To answer why are you getting `IXMLDOMNodeList` zero length list is simple. It's because you've tried to workaround the error about the invalid `::` characters at `preceding-sibling::` by replacing it with `/` but this would mean you're comparing the value with the node `preceding-sibling/..` what is wrong. You need to use the `preceding-sibling` as a parameter (using the `::`) returning the previous node, not as a constant node value. To use the XPath axis you need to use the newer version of the MSXML since the old one doesn't support that. — TLama, Jun 05 '12 at 11:18

TLama · Accepted Answer · 2012-06-05T11:40:35.667

It seems the version of the MSXML from Delphi 2007 doesn't support XPath axis. So if you decide to use the following code, import either Microsoft XML, v3.0 or the Microsoft XML, v6.0 type library first and then try this:

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, MSXML2_TLB;

type
  TForm1 = class(TForm)
    Button1: TButton;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

const
  XMLString =
    '<Accounts>' +
    '<Account Number="1" DebitAmount="1000" Amount="2827561.95"/>' +
    '<Account Number="225" DebitAmount="2000"  Amount="12312.00"/>' +
    '<Account Number="236" DebitAmount="London" Amount="457656.00"/>' +
    '<Account Number="225" DebitAmount="London" Amount="23462.40"/>' +
    '<Account Number="236" DebitAmount="Bangalore" Amount="2345345.00"/>' +
    '</Accounts>';

type
  TIntegerArray = array of Integer;

function GetAccountNumbers(const AXMLString: string): TIntegerArray;
var
  I: Integer;
  XMLDOMNodeList: IXMLDOMNodeList;
  XMLDOMDocument: IXMLDOMDocument3;
begin
  XMLDOMDocument := CoDOMDocument60.Create;
  if Assigned(XMLDOMDocument) and XMLDOMDocument.loadXML(AXMLString) then
  begin
    XMLDOMNodeList := XMLDOMDocument.selectNodes('/Accounts/Account[not(@Number=preceding-sibling::Account/@Number)]/@Number');
    SetLength(Result, XMLDOMNodeList.length);
    for I := 0 to XMLDOMNodeList.length - 1 do
      Result[I] := XMLDOMNodeList.item[I].nodeValue;
  end;
end;

procedure TForm1.Button1Click(Sender: TObject);
var
  S: string;
  I: Integer;
  IntegerArray: TIntegerArray;
begin
  S := 'Account numbers: ';
  IntegerArray := GetAccountNumbers(XMLString);
  for I := 0 to Length(IntegerArray) - 1 do
    S := S + IntToStr(IntegerArray[I]) + ', ';
  Delete(S, Length(S) - 1, 2);
  ShowMessage(S);
end;

end.

I installed the Microsoft xml 6.0, but still getting Debugger exception. (I'm now interested in knowing the mehtod) — Zeina, Jun 05 '12 at 10:43
@Zeina, you didn't tell me so much, what exception and when ? You mean when the nodes are being selected by the XPath ? I've tried the code from this post in Delphi 2007 on Windows 7 and Windows XP and it works fine for me. — TLama, Jun 05 '12 at 10:53
this is the message: Exception class EOleException with message ‘Expected token ‘)’ found ‘:’ ./Accounts/Account[not(@Number=preceding-sibling-->:<--:Account/@Number)]/@Number’. Is it the Misrosoft XML not installed properly? — Zeina, Jun 05 '12 at 11:25
@Zeina, are you sure you've instantiated the `IXMLDOMDocument3`, not the `IXMLDOMDocument` ? It will fail with the `IXMLDOMDocument`, you need to import and use the MSXML 6.0 type library. I've imported the type library on Windows 7, but it should be the same also on Windows XP. What version of Windows do you have ? Have you tried the code from this post ? — TLama, Jun 05 '12 at 11:37

balazs · Answer 2 · 2012-06-07T15:38:29.950

1

Don't understand exactly what do you want to achieve. Maybe this?

'/Accounts/Account[not(@Number=preceding-sibling::node()/@Number)]/@Number'

edited Jun 07 '12 at 15:38

answered Jun 05 '12 at 11:22

balazs

5,698
7
37
45

This will fail with the old version of MSXML type library shipped with Delphi 2007 with the error `Exception class EOleException with message 'Expected token ')' found ':'. /Accounts/Account[not(@Number=preceding-sibling-->:<--:node()/@Number)]'.` – TLama Jun 05 '12 at 11:27
@TLama Than the newer type library should be used, as you suggested in your answer. It's not clear to me that the question is about using xpath and axes in delphi 2007, or about the xpath expression to use? or maybe both? – balazs Jun 05 '12 at 11:37
I was looking for a solution using the xpath expression which works in my version of Delphi... – Pradeep Jun 06 '12 at 10:13

score 1 · Answer 3 · edited May 23 '17 at 12:12

This works in my XPath example in Delphi XE2 and a Delphi 2007 update of the XPath example gives this output:

nodeName[0]:Number
nodeValue[0]:1
nodeName[1]:Number
nodeValue[1]:225
nodeName[2]:Number
nodeValue[2]:236

The ideas in the example should get you going with Delphi and 2007 using either the MSXML 6 DOM, or the OpenXML DOM (Delphi XE2 supports MSXML 6 DOM or ADOM XML v4 DOM).

Note that MSXML 6 behaviour highly depends on the actual version you have installed which depends on your Windows OS (hence this answer).

Which probably means you do not have the latest MS XML 6 installed, or have not imported the right typelibraries from it (the Delphi 2007 msxml unit does not contain IXMLDOMDocument2 which you need for XPath support).

On July 27, 2012, I made some time to retro fit the example to Delphi 2007 and test it.
I'm not completely sure it is completely memory leak free (I hacked together a TDictionary class to put interfaces in), but the results are the same as the Delphi XE2 example and at first sight it looks quite OK.

In the XPathTester demo app, load Example 3, then Run the XPath (that will load your example, and execute the XPath).

Note that most other things in the bo library requires at least Delphi 2009, but this part works.
In the next weeks I will test it as I require it in a different project that is still at Delphi 2007.

procedure TMainForm.LoadXmlExample3ButtonClick(Sender: TObject);
begin
  LoadXmlExample([ // unique account numbers
    '/Accounts/Account[not(@Number=preceding-sibling::Account/@Number)]/@Number'
  ], 
  [
    '<?xml version="1.0"?>',
    '<Accounts>',
    '  <Account Number="1"   DebitAmount="1000" Amount="2827561.95" />',
    '  <Account Number="225" DebitAmount="2000"  Amount="12312.00" />',
    '  <Account Number="236" DebitAmount="London"    Amount="457656.00" />',
    '  <Account Number="225" DebitAmount="London"    Amount="23462.40" />',
    '  <Account Number="236" DebitAmount="Bangalore" Amount="2345345.00" />',
    '</Accounts>'
  ]);
end;

Currently my answer is to prove that the XPath query works on the XML using MSXML 6. So yes: it should be possible to do this in Delphi 2007 as well. If/when I find time to translate the example project to Delphi 2007, I will let you know by an edit to this answer. — Jeroen Wiert Pluimers, Jun 06 '12 at 10:19
@user466744 I finally had a chance to work on porting the XPath demo do Delphi 2007. Please see if you can run the code that is stored here: http://bo.codeplex.com/SourceControl/changeset/80654 — Jeroen Wiert Pluimers, Jul 27 '12 at 22:06

score 0 · Answer 4 · answered Jun 05 '12 at 09:04

try this code:

XMLDocument1: TXMLDocument;

procedure TForm1.Button2Click(Sender: TObject);
var
  BodyNode:IXMLNode;
  I: Integer;
  sl:TStringList;
begin
  sl:=TStringList.Create;
  XMLDocument1.Active:=False;
  XMLDocument1.FileName:='C:\Zeina\Test.xml';
  XMLDocument1.Active:=True;

  BodyNode:=XMLDocument1.DocumentElement;
  for I:=0 to BodyNode.ChildNodes.Count-1 do
  begin
    if sl.IndexOf(BodyNode.ChildNodes[i].Attributes['Number'])=-1 then
      sl.Add(BodyNode.ChildNodes[i].Attributes['Number']);
  end;
  Memo1.Lines:=sl;
end;

this is the result:

It works though, but it's not the XPath way, what was asked here. — TLama, Jun 05 '12 at 09:08

score -1 · Answer 5 · answered Jun 05 '12 at 10:11

Ziena, i change your code :

  XMLDocument1: TXMLDocument;

procedure TForm1.Button2Click(Sender: TObject);
var
  BodyNode:IXMLNode;
  I: Integer;
  sl:TStringList;
begin
  sl:=TStringList.Create;
  XMLDocument1.Active:=False;
  XMLDocument1.FileName:='C:\Zeina\Test.xml';
  XMLDocument1.Active:=True;

  BodyNode:=XMLDocument1.DocumentElement;
  sl.Duplicates := dupIgnore;
  for I:=0 to BodyNode.ChildNodes.Count-1 do
    sl.Add(
      BodyNode.ChildNodes[i].Attributes['Number']); // ignore duplicated lines  
   Memo1.Lines.AddStrings(sl); // for avoid memory leak
end;

Reading distinct values from XML using XPath

5 Answers5