ajXML function
AlchemyJ Excel Library, AlchemyJ Community Edition, AlchemyJ Enterprise Edition
Description
The ajXML function returns an XML formatted message with the corresponding name and value pairs. Data with either an empty header or an empty value will be ignored.
Syntax
ajXML(header, data, [data_type], [exclude_column], [root_name], [element_name])
Argument Name | Argument Type | Description |
---|---|---|
header (required) | Range / Array | 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) | Range / Array | 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) | String | 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. 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 ajXML function.
Example 1
In this example, we only need to define the mandatory parameters of the ajXML function. Cells A1 to C1 defines the header range (first parameter) while cells A2 to C4 defines the data range (second parameter).
Formula:
=ajXML(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:
=ajXML(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:
=ajXML(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 ajXML. 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:
=ajXML(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 ajXML will only return the first row of the value.
Formula:
=ajXML(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>
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 |
---|
Invalid data type, it could be number, integer, boolean or string. |
Defined header or data range does not exist. |
Failed to convert data to specified data type. |