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.
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:
Post a Comment