ajXMLFromTable function

Description

The ajXMLFromTable function returns an XML formatted message based on the corresponding name and value pairs that are keyed in.

Syntax

ajXMLFromTable(header, data, [data_type], [exclude_column], [root_name], [element_name])

Argument Name	Argument Type	Description
header (required)	Range / Array	The Excel range of cells to be set as the name of the XML nodes. It can be selected either vertically or horizontally. Spaces will be trimmed and will be replaced by an underscore if it exists between words. For example, the provided header is "Date of Birth", the xml node name will be "Date_of_Birth". For multiple spaces in the middle will be replaced with one underscore only. For other characters that are not allowed, it will be handled as the same as space.
data (required)	Range / Array	The Excel range of cells to be set as the value of the XML nodes. It can be selected either vertically or horizontally but the orientation must be the same as ‘Header’. For example, if ‘Header’ is selected vertically, ‘Data’ must be selected vertically as well.
data_type (optional)	String	The data type of the fields (‘Header’) in the format below. The following data types supported in AlchemyJ are number, integer, boolean and string. Fields that do not exist will be ignored. You can combine the data type of multiple fields as shown below: <field_name>-##-<data_type>-||-<field_name>-##-<data_type>-||-... Where, -||- denotes AlchemyJ parameter separator. -##- denotes AlchemyJ parameter type separator. For example, ID-##-number-||-Name-##-string Please take note that both separator symbols can be configured in %%AppConfig worksheet. The default data type will follow the cell data type.
exclude_column (optional)	String	The fields to be excluded from the result. Fields that do not exist will be ignored. <field_name>-||-<field_name>-||-... Where, -||- denotes AlchemyJ parameter separator. For example, ID-||-Last Name Please take note that the parameter separator symbols can be configured in %%AppConfig worksheet. No fields will be excluded by default.
root_name (optional)	String	The root node name in XML. The default root name is < root >
element_name (optional)	String	The element name for each record detail in XML. The default element name is < element >

The function will return:

1) Return Value: XML

2) Return Type: Single Value / Multiple values (array formula)

Examples

Here are a few examples of the ajXMLFromTable function.

Example 1

In this example, we only need to define the mandatory parameters of the ajXMLFromTable. Cell A1 to C1 defines the header range (first parameter) while cell A2 to C4 defines the data range (second parameter).

Formula:

=ajXMLFromTable(A1:C1,A2:C4)

Returned XML

<?xml version=""1.0"" encoding=""UTF-8""?>
<root>
    <element>
        <ID>1</ID>
        <Name>CHAN TAI MAN</Name>
        <Gender>M</Gender>
    </element>
    <element>
        <ID>2</ID>
        <Name>JERRY SZE</Name>
        <Gender>M</Gender>
    </element>
    <element>
        <ID>3</ID>
        <Name>MICKY MAI</Name>
        <Gender>F</Gender>
    </element>
</root>         

Example 2

This example is similar to Example 1. The difference is that it specifies the root name as "customers" (fifth parameter) and specify the element name as "customer" (sixth parameter).

Formula:

=ajXMLFromTable(A1:C1,A2:C4,,,"customers","customer")

Returned XML

<?xml version=""1.0"" encoding=""UTF-8""?>
<customers>
    <customer>
        <ID>1</ID>
        <Name>CHAN TAI MAN</Name>
        <Gender>M</Gender>
    </customer>
    <customer>
        <ID>2</ID>
        <Name>JERRY SZE</Name>
        <Gender>M</Gender>
    </customer>
    <customer>
        <ID>3</ID>
        <Name>MICKY MAI</Name>
        <Gender>F</Gender>
    </customer>
</customers>            

Example 3

This example is similar to Example 2 except the excluded column (fourth parameter) is specified. In this example, the "Gender" column will be excluded from the XML message.

Formula:

=ajXMLFromTable(A1:C1,A2:C4,,"Gender","customers","customer")

Returned XML

<?xml version=""1.0"" encoding=""UTF-8""?>
<customers>
    <customer>
        <ID>1</ID>
        <Name>CHAN TAI MAN</Name>
    </customer>
    <customer>
        <ID>2</ID>
        <Name>JERRY SZE</Name>
    </customer>
    <customer>
        <ID>3</ID>
        <Name>MICKY MAI</Name>
    </customer>
</customers>            

Click here to download the use case workbooks for further reference.

Error Scenarios

It will return #VALUE! when missing any required parameter or mismatch parameter type. Besides, system will raise error for below scenario(s).

Error Scenario
Failed to convert data to specified data type.
Invalid data type, it could be number, integer, boolean or string.
Header or data range does not exist.