AlchemyJ by Axisoft

4.0

ajMakeXMLFromTable function

Description

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

Syntax

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

Argument NameDescription
header (required)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)		The Excel range of cells to be set as the value of the XML nodes. It can be selected either vertically or horizontally but must be the same as ‘Header’.

For example, if ‘Header’ is selected vertically, ‘Data’ must be selected vertically as well.

data_type
(optional)		Specify 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.

If you do not specify anything, the default data type will follow the cell data type.

exclude_column
(optional)		Specify 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.

If you do not specify anything, no fields will be excluded.

root_name
(optional)		Specify the root node name in XML.

If you do not specify anything, the default root name is < root >

element_name
(optional)		Specify the element name for each record detail in XML.

If you do not specify anything, the default element name is < element >

The function will return:

1) Content type: XML

2) Method: Within a cell / row cell array

Examples

Here are a few examples of the ajMakeXMLFromTable function.

Example 1

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

Formula:

=ajMakeXMLFromTable(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:

=ajMakeXMLFromTable(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:

=ajMakeXMLFromTable(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>
← ajMakeXMLFromSchemaajPopulateJsonToTable →