AlchemyJ by Axisoft

4.0

ajMakeXML function

Description

The ajMakeXML function returns a XML formatted message with the corresponding name and value pairs.

Syntax

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

Argument NameDescription
header (required)The 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, it will be replaced with one underscore only. For other characters that are not allowed, it will be handled in the same way as space.

If a header name is surrounded by [], that means this node will contain a list and the list has a group field.

If a header name is surrounded by {}, that means this node will contain a single group field.

{} and [] headers will create objects with multiple level nodes and the content expects a string that contains cell range address. Use asterisk "*" to join header and data addresses. For example, A1:B1*A2:B4. This means A1 to B1 is the header range of the nested list, and A2 to B4 are the three rows of data.

data
(required)		The 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 are supported in AlchemyJ: 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 ajMakeXML function.

Example 1

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

Formula:

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

=ajMakeXML(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>

Exmple 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:

=ajMakeXML(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>

Example 4

From the example below, as the header Phone List is surrounded by [], therefore the value will be treated as the parameters of a nested call of ajMakeXML. Cell E18 to Cell F18 is the header of the Phone List and Cell F18 to G19 is the data range of Phone List. In this example, two phone records will be linked to the customer.

Formula:

=ajMakeXML(A18:C18,A19:C19,,,"customers","customer")

Returned XML

<?xml version=""1.0"" encoding=""UTF-8""?>
<customer>
    <ID>1</ID>
    <Name>CHAN TAI MAN</Name>
    <Phone_List>
        <element>
            <Type>Office</Type>
            <Phone_Number>22332222</Phone_Number>
        </element>
        <element>
            <Type>Mobile</Type>
            <Phone_Number>99887766</Phone_Number>
        </element>
    </Phone_List>
</customer>

Example 5

This example is similar to example 4, the only difference is that the Phone is surrounded by {}. Please take note that the nested call of ajMakeXML will only return the first row of the value.

Formula:

=ajMakeXML(A27:C27,A28:C28,,,"customers","customer")

Returned XML

<?xml version=""1.0"" encoding=""UTF-8""?>
<customers>
    <ID>1</ID>
    <Name>CHAN TAI MAN</Name>
    <Phone_List>
        <Type>Office</Type>
        <Phone_Number>22332222</Phone_Number>
    </Phone_List>
</customers>
← ajMakeJsonFromTableajMakeXMLFromSchema →