Labels

Monday, June 3, 2013

Universal XML export

Few days ago I had a task to export some records (about 20 tables related) in XML.
I'd prefer to write some kind of universal export procedure, rather than proceed with every table.

So, at first, after running we should read the settings from the file, which will be located in the same location as the result export file. Every line in the file should be as follows: TableNo [Tab] FieldNo, meaning that this field should be exported to XML.
The procedure for reading this file should be like this:

ReadSettings()
DlgStatus.UPDATE(1, StatusReadSettings);
SettingFile.TEXTMODE(TRUE);
SettingFile.WRITEMODE(FALSE);
SettingFile.OPEN(ExportPath + 'mapping.txt');
REPEAT
  SettingFile.READ(SettingString);
  i := 1;
  CLEAR(Index);
  WHILE i <= STRLEN(SettingString) DO BEGIN
    Symbol := COPYSTR(SettingString,i,1);
    IF STRPOS('0123456789',Symbol) <> 0 THEN BEGIN
      IF (Index[1] = 0) THEN Index[1] := i;
      IF (Index[2] <> 0) AND (Index[3] = 0) THEN Index[3] := i;
    END ELSE BEGIN
      IF (Index[1] <> 0) AND (Index[2] = 0) THEN Index[2] := i;
    END;
    i += 1;
  END;
  EVALUATE(Mapping[1], DELCHR(COPYSTR(SettingString,Index[1],Index[2]-Index[1]),'<>', ' '));
  EVALUATE(Mapping[2], DELCHR(COPYSTR(SettingString,Index[3],STRLEN(SettingString)-Index[3]+1),'<>', ' '));
  ExportFields.INIT;
  ExportFields.TableNo := Mapping[1];
  ExportFields."No." := Mapping[2];
  ExportFields.INSERT;
UNTIL SettingFile.POS = SettingFile.LEN;
where ExportFields is the temporary table, based on the table 2000000041 Field.

Next, I've prepared the core function to export whatever record:

ExportRecord(XMLDocRoot : Automation "'Microsoft XML, v6.0'.IXMLDOMNode";VAR XMLDocDoc : Automation "'Microsoft XML, v6.0'.IXMLDOMNode";RecRef : RecordRef; RecName : Text [30])
XMLQuery := NodeName(RecName)+'[';
KeyRef := RecRef.KEYINDEX(1);
i := 1;
REPEAT
  FieldRef := KeyRef.FIELDINDEX(i);
  IF i > 1 THEN
    XMLQuery := XMLQuery + ' and ';
  XMLQuery := XMLQuery + '@' + NodeName(FieldRef.CAPTION) + '="' + FORMAT(FieldRef.VALUE) + '"';
  i += 1;
UNTIL i > KeyRef.FIELDCOUNT;
XMLQuery := XMLQuery + ']';
XMLDocDoc := XMLDocRoot.selectSingleNode(XMLQuery);
IF NOT ISCLEAR(XMLDocDoc) THEN EXIT;
IF RecName = '' THEN RecName := RecRef.CAPTION;
AddSubNode(XMLDocRoot, XMLDocDoc, NodeName(RecName));

KeyRef := RecRef.KEYINDEX(1);
i := 1;
REPEAT
  FieldRef := KeyRef.FIELDINDEX(i);
  IF UPPERCASE(FORMAT(FieldRef.TYPE)) = 'OPTION' THEN BEGIN
    EVALUATE(OptionValueAsInteger,FORMAT(FieldRef.VALUE));
    AddAttributeNode(XMLDocDoc, XMLDocNode, NodeName(FieldRef.CAPTION),
      SELECTSTR(OptionValueAsInteger + 1, FieldRef.OPTIONCAPTION))
  END ELSE
    AddAttributeNode(XMLDocDoc, XMLDocAttr, NodeName(FieldRef.CAPTION), FORMAT(FieldRef.VALUE));
  TmpKey.INIT;
  TmpKey.Number := FieldRef.NUMBER;
  TmpKey.INSERT;
  i += 1;
UNTIL i > KeyRef.FIELDCOUNT;

i := 1;
REPEAT
  FieldRef := RecRef.FIELDINDEX(i);
  EVALUATE(OptionVar,FORMAT(FieldRef.CLASS));
  IF OptionVar = OptionVar::FlowField THEN FieldRef.CALCFIELD;
  IF NOT TmpKey.GET(FieldRef.NUMBER) AND ExportFields.GET(RecRef.NUMBER,FieldRef.NUMBER) THEN BEGIN
    IF UPPERCASE(FORMAT(FieldRef.TYPE)) = 'OPTION' THEN BEGIN
      EVALUATE(OptionValueAsInteger,FORMAT(FieldRef.VALUE));
      AddSubNodeText(XMLDocDoc, XMLDocNode, NodeName(FieldRef.CAPTION),
        SELECTSTR(OptionValueAsInteger + 1, FieldRef.OPTIONCAPTION))
    END ELSE
      AddSubNodeText(XMLDocDoc, XMLDocNode, NodeName(FieldRef.CAPTION), FORMAT(FieldRef.VALUE));
  END;
  i += 1;
UNTIL i > RecRef.FIELDCOUNT;
This function should create the subnode for XMLDocRoot node and return reference for it. This node should have the name, which is defined in RecName parameter. Before addind the new one, the procedure check for existing record in XML file, builing the query with primary keys values. After this the procedure export the primary key values as the attributes to the result node. Next step is to go through all other fields, check if they are exists in the ExportFields array and export them as nodes one by one.

The example of how to call this function could be as follows:
  RecordRef.OPEN(DATABASE::"Purch. Inv. Header");
  RecordRef.GETTABLE(InvoiceHdr);
  XMLDocHdr := XMLDocRoot.selectSingleNode('/Documents/Purchases');
  IF ISCLEAR(XMLDocHdr) THEN AddSubNode(XMLDocRoot, XMLDocHdr, 'Purchases');
  IF NOT InvoiceHdr."Empl. Purchase" THEN
    ExportRecord(XMLDocHdr, XMLDocHdr, RecordRef, 'Purchase')
  ELSE
    ExportRecord(XMLDocHdr, XMLDocHdr, RecordRef, 'AdvanceStatement');

Now you should catch why I'm setting the node name rather to read it from the record table name. The reason is to export some documents (based on value of some fields) to the particular node in XML file. 

No comments: