1. An XML Export Example

Occasionally, you may need to export data from a table in XML format. This example demonstrates how to achieve this using ACF.

An XML Export Example
Same example with plugin version 1.7.0.8

NOTE: The example code here was written before the ACF-plugin got its own XML implementation. The example is now far more easy. Look at the bottom of the article for the new version.

:##1. Example Code: :

Package XMLDump "Dump SQL result to an XML file";

function XMLtag (string tag, string value, int tabs) 
    string result; 
    value = substitute ( value, "&amp;", "&amp;amp;");
    string t = "\t"*tabs; 
    if (value == "") then
        result = t+"<"+tag+"/>\n";
    else
         result = t+"<" + tag + ">" + value + "</" + tag + ">\n"; 
    end if
    
    return result; 
end

function XMLtag2 (string tag, string value, int tabs)  

    string result; 
    string t = "\t"*tabs; 
    if (value == "") then
        result = t+"<"+tag+"/>\n";
    else
         result = t+"<" + tag + ">\n" + value + "\n"+t+"</" + tag + ">\n"; 
    end if
    
    return result; 
end

function XMLdumpDataset (string filename, string MasterKey, string DetailRecordKey, string DetailKeys, string SQLresult, string FieldSeparator, string RecordSeparator)

    array string Records = explode ( RecordSeparator, SQLresult); 
    array string Keys = explode ( FieldSeparator, DetailKeys); 
    array string Fields; 
    int i,j, numRecords = sizeof ( Records ), numFields = sizeof (Keys); 
    string result = ""; 
    string fres ; 
    // Produce the XML content. 
    for (i = 1, numRecords) 
        Fields = explode ( FieldSeparator, Records[i]); 
        if (sizeof ( Fields ) != numFields ) then
            throw format ("Different number of fields and keys: Fields:%d, Keys:%d ", sizeof ( Fields ), numFields) ; 
        end if
        fres = ""; 
        for (j = 1, numFields)
            fres += XMLtag(Keys[j], Fields[j], 2); 
        end for
        result += XMLtag2 ( DetailRecordKey, fres, 1); 
    end for
    // Add master key
    if (MasterKey != "") then
        result = XMLtag2(MasterKey, result, 0); 
    end if
    // Write to file, or return result if no filename. 
    if (filename != "") then
        int x = open (filename, "w"); 
        write (x, "<?xml version='1.0' encoding='UTF-8' standalone='no'?>\n"); 
        write (x, result ); 
        close (x); 
        result = "OK";
    end if
    long tu = uSec(); 
    print "Time used : " + tu + format  (" (%-.6f sec)", tu/1.0E6); 
    return result; 
end

:###1. FileMaker Script to Execute the Dump: :

// Calculation used in the "export button" in the layout: 
// Prototype: 
// ACF_run("XMLdumpDataset"; text_filename; text_MasterKey; text_DetailRecordKey; 
// text_DetailKeys; text_SQLresult; text_FieldSeparator; text_RecordSeparator)
//    ==> Return type = text

Set field [Preferences::gResult; 
ACF_run("XMLdumpDataset"; Contacts::gFileName; "Contacts"; "Contact"; 
"ID||FirstName||LastName||Address1||Address2||Zip||City||Country||Email||Phone||Mobile"; 
ExecuteSQL ( "SELECT ID,FirstName,LastName,Address1,Address2,Zip,City,Country,Email,Phone,Mobile FROM Contacts" ; "||" ; "|*|"  ); "||"; "|*|")]

After running this on approximately 2000 contacts, inspecting the console shows:

Time used : 400447 (0.400447 sec)

This time includes writing 450KB of data to the output file. It's important to note that the SQL query time is not included, as it occurs before entering the function. With 400 milliseconds for 2000 contacts, it averages to 0.2 milliseconds per contact—a respectable performance.

The resulting file structure resembles the following:

<?xml version='1.0' encoding='UTF-8' standalone='no'?>
<Contacts>
    <Contact>
        <ID>1000</ID>
        <FirstName>Ole Kristian Ek</FirstName>
        <LastName>Hornnes</LastName>
        <Address1>Gluppeveien 12B</Address1>
        <Address2>Innerst i Hørnet</Address2>
        <Zip>1614</Zip>
        <City>FREDRIKSTAD</City>
        <Country>NO</Country>
        <Email>ole@webapptech.no</Email>
        <Phone>46908981</Phone>
        <Mobile>46908981</Mobile>
    </Contact>
    <Contact>
        <ID>1001</ID>
        <FirstName>Roffe</FirstName>
        <LastName>Hansen</LastName>
        <Address1>Linjegata 12</Address1>
        <Address2>Oppgang B</Address2>
        <Zip>1600</Zip>
        <City>FREDRIKSTAD</City>
        <Country>NO</Country>
        <Email>roffe@b.com</Email>
        <Phone>222222</Phone>
        <Mobile>98765432</Mobile>
    </Contact>
    ...
    ... (439KB of data)
    ...
</Contacts>

This XML file can be used for various purposes, such as data interchange or further processing.

:###1. Screenshots from the Test Application: :

Test Contacts

2. Same example with plugin version 1.7.0.8 ↑

We have moved the ExecuteSQL into the plugin to take advantage of the new SQL functions in the plugin as well. We use the XML datatype to craft the XML. We have one function to create the contact containing all the elements of the contact. This function was made with the dev tool using one record in the XML as a sample.

Function CreateContact(string id, string firstName, string lastname, string add1, string add2, 
    string zip, string city, string country, string email, string phone, string mobile)

    XML xmlvar;
    xmlVar["Contact"] = 
            XML("ID", id, "FirstName", firstName,"LastName", lastname
            ,"Address1",add1,"Address2",add2,"Zip",zip
            ,"City",city,"Country",country,"Email",email
            ,"Phone",phone,"Mobile",mobile);
    

    return XmlVar;
End

// Since we have moved the SQL inside the function, 
// We only need a filename to save the file as parameter.
 
function XMLdumpDataset (string filename)

    string sql = "SELECT ID, FirstName, LastName, Address1, Address2, Zip, City, 
        Country, Email, Phone, Mobile
        FROM Contacts

        INTO :ID, :FirstName, :LastName, :Address1, :Address2, :Zip, 
                :City, :Country, :Email, :Phone, :Mobile"; 

    ARRAY string ID;
    ARRAY string FirstName;
    ARRAY string LastName;
    ARRAY string Address1;
    ARRAY string Address2;
    ARRAY string Zip;
    ARRAY string City;
    ARRAY string Country;
    ARRAY string Email;
    ARRAY string Phone;
    ARRAY string Mobile;

    
    string res = executeSQL ( sql ) ; 

    int i,j, numRecords = sizeof ( FirstName ); 
    
    // Produce the XML content. 
    XML contacts; 
    for (i = 1, numRecords) 
        contacts["Contacts+"] = CreateContact(ID[i], FirstName[i], LastName[i], 
            Address1[i], Address2[i], Zip[i], City[i], Country[i], 
            Email[i], Phone[i], Mobile[i]);
    end for
    
    if (filename != "") then
        int x = open (filename, "w"); 
        write (x, string (contacts ); 
        close (x); 
        result = "OK";
    end if
    long tu = uSec(); 
    print "Time used : " + tu + format  (" (%-.6f sec)", tu/1.0E6); 
    return string(contacts); 
end

This example demonstrates how to efficiently export data to an XML file using ACF, making it a valuable tool for handling structured data in your FileMaker solutions.