Read XML DOM Document into table

zeon · 2012-05-15

Hi,

I'm trying to download some currency exchange rates in an xml file, and then read them into a table, but I can't seem to get it right. Have tried to foloow the advises in other threads without luck.
The format of the xml file is the following:

<?xml version="1.0" encoding="UTF-8" ?> 
- <Envelope>
  <subject>Reference rates</subject> 
- <Sender>
  <name>European Central Bank</name> 
  </Sender>
- <Cube>
- <Cube time="2012-05-14">
  <Cube currency="USD" rate="1.2863" /> 
  <Cube currency="JPY" rate="102.64" /> 
  <Cube currency="BGN" rate="1.9558" /> 
  </Cube>
  </Cube>
  </Envelope>

My Code looks like this. For now I'm saving the values in MyText just to see the value, but later it must be saved to Currency Exchange Rates.

IF ISCLEAR(XMLHTTP) THEN
  IF NOT CREATE(XMLHTTP) THEN
    ERROR(Text000);

GLSetup.GET;
GLSetup.TESTFIELD("URL Latest Currency Rates");

// URL := 'http://www.ecb.int/stats/eurofxref/eurofxref-daily.xml';
// URL := 'http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml';
URL := GLSetup."URL Latest Currency Rates";

XMLHTTP.open('GET',URL,FALSE);
XMLHTTP.send();

IF (XMLHTTP.status = 200) THEN BEGIN
  IF ISCLEAR(XMLDoc) THEN
    IF NOT CREATE(XMLDoc) THEN
      ERROR(Text000);

  XMLDoc.async := FALSE;
  XMLDoc.load(XMLHTTP.responseBody);

IF RemoveNamespace(XMLDoc,XMLDoc,Filename) THEN BEGIN
    WITH TmpCurrExchRate DO BEGIN
      XMLNode := XMLDoc.documentElement;
      XMLNodeList := XMLNode.selectNodes('Envelope');
      XMLNodeList.reset;
      REPEAT
        TmpCurrExchRate.INIT;
        XMLNode := XMLNodeList.nextNode;
        IF FindNode(XMLNode,'Sender',XMLReturnedNode) THEN
          IF STRLEN(XMLReturnedNode.text) > 0 THEN
            MyText := XMLReturnedNode.text
        IF FindNode(XMLNode,'Cube',XMLReturnedNode) THEN
          IF STRLEN(XMLReturnedNode.text) > 0 THEN
            MyText := XMLReturnedNode.text;
        IF FindNode(XMLNode,'Cube currency',XMLReturnedNode) THEN
          IF STRLEN(XMLReturnedNode.text) > 0 THEN
            MyText := XMLReturnedNode.text;
        IF TmpCurrExchRate.INSERT(TRUE) THEN;
      UNTIL COUNT = (XMLNodeList.length);
    END;

Function FindNode from CU13601

IF ISCLEAR(XMLRootNode) THEN
  EXIT(FALSE);

IF NOT NormalCaseMode THEN
  NodePath := NodePath;
FoundXMLNode := XMLRootNode.selectSingleNode(NodePath);

IF ISCLEAR(FoundXMLNode) THEN
  EXIT(FALSE)
ELSE
  EXIT(TRUE);

XMLNode	Automation	'Microsoft XML, v6.0'.IXMLDOMNode	
XMLReturnedNode	Automation	'Microsoft XML, v6.0'.IXMLDOMNode	
XMLNodeList	Automation	'Microsoft XML, v6.0'.IXMLDOMNodeList

How can I get the values of the attributes in <Cube currency="USD" rate="1.2863" />

pdj · 2012-05-15

Untested, but I'm quite sure you can access an attribute of an element, just prefixed it with @.

IF FindNode(XMLNode,'Cube',XMLReturnedNode) THEN
IF FindNode(XMLReturnedNode,'@currency',XMLReturnedAtt) THEN

Please give it a shot...

zeon · 2012-05-15

Hi Peter,

It didn't help, BUT instead you brought me unto something else, and in my search I found this msdn blog entry http://blogs.msdn.com/b/nav_developer/archive/2008/03/31/sample-of-how-to-read-a-simple-xml-document-using-msxml-dom-in-nav.aspx
that can help me process the XML DOM Document. I'm not finished yet, but now I can get to the values of the attributes, and from there put it into a table :-) Damn, that has taken me a loooooong time ](*,)

So, thanks anyway!

zeon

pdj · 2012-05-15

Hmm, surprised my suggestion doesn't work. I have this code running at several sites:

GetAttribute(VAR XMLNode : Automation "'Microsoft XML, v6.0'.IXMLDOMNode";AttributeName : Text[250];VAR AttributeValue : Text[250]) : Boolean
XMLAttribute := XMLNode.selectSingleNode(STRSUBSTNO('@%1',AttributeName));
IF ISCLEAR(XMLAttribute) THEN
  EXIT(FALSE);
AttributeValue := XMLAttribute.text;
EXIT(TRUE);

I guess the difference is the XMLAttribute, which isn't a IXMLDOMNode but a IXMLDOMAttribute.

Read XML DOM Document into table

Answers

Categories