ajXMLFromSchema function

Description

The ajXMLFromSchema function returns an XML formatted message based on the data relationship definition. If there are multiple tables with dependency, the XML would be multi-level. If a data dictionary is defined in the given data relationship schema, it will be used for data type conversion.

Syntax

ajXMLFromSchema(data_relationship_header, data_relationship, [root_name])

Argument Name	Argument Type	Description
data_relationship_header (required)	Range	The Excel range which defines the header of the Data Relationship Schema.
data_relationship (required)	Range	The Excel range which defines the content of the Data Relationship Schema. The empty row will be ignored.
root_Name (optional)	String	The root node name in XML. The default root name is < root >

The function will return:

1) Return Value: XML

2) Return Type: Single Value / Multiple values (array formula). It will split the returned XML message into multiple cells. When the string length is longer than a single cell character limit - 32767, the remaining string will be extended in the row array.

Example

Let's assume there are two tables: Customer and Email.

Example 1

Define the Data Relationship Schema by inserting a Snippet as below.

There are two levels of the XML.
The key field is Name.
Customer records 'CHAN TAI MAN' and 'JERRY SZE' have email records by using the Name as joining key. For the other Customer records, only Customer level information will appear in the XML.
The Date of Birth of the Customer table becomes a number as its value type of the cell is Date. This is as shown in the Returned XML screenshot below.

Returned XML:

<?xml version="1.0" encoding="UTF-8"?>
<root>
    <Customer>
        <element>
            <Name>CHAN TAI MAN</Name>
            <Date_of_Birth>28127</Date_of_Birth>
            <Gender>M</Gender>
            <Customer_Level>5</Customer_Level>
            <Telephone>22205555</Telephone>
            <Email>
                <element>
                    <Email>CHAN.TAN.MAN@hotmail.com</Email>
                    <Type>Work</Type>
                </element>
                <element>
                    <Email>CHAN.TAN.MAN@gmail.com</Email>
                    <Type>Personal</Type>
                </element>
            </Email>
        </element>
        <element>
            <Name>JERRY SZE</Name>
            <Date_of_Birth>32239</Date_of_Birth>
            <Gender>M</Gender>
            <Customer_Level>2</Customer_Level>
            <Telephone>22203333</Telephone>
            <Email>
                <element>
                    <Email>JERRY.SZE@yahoo.com</Email>
                    <Type>Personal</Type>
                </element><element>
                <Email>JERRY.SZE@axisoft.com</Email>
                <Type>Work</Type>
                </element>
            </Email>
        </element>
        <element>
            <Name>MERRY SO</Name>
            <Date_of_Birth>32205</Date_of_Birth>
            <Gender>F</Gender>
            <Customer_Level>4</Customer_Level>
            <Telephone>22201111</Telephone>
    </element>
        <element>
            <Name>TOM HUI</Name>
            <Date_of_Birth>28827</Date_of_Birth>
            <Gender>M</Gender>
            <Customer_Level>1</Customer_Level>
            <Telephone>22204444</Telephone>
        </element>
        <element>
            <Name>VICKY CHAN</Name>
            <Date_of_Birth>29718</Date_of_Birth>
            <Gender>F</Gender>
            <Customer_Level>3</Customer_Level>
            <Telephone>22202222</Telephone>
        </element>
    </Customer>
</root>

Example 2

In this example,Root_Name was specified as 'Customers' in the formula. Meanwhile, JSON Node Name, Multiple Row are used in the data relationship table. JSON Node Name is 'Emails' for Email records. As Multiple Row is set as FALSE, therefore only the first matched row in the Email table would appear in the XML.

Returned XML:

<?xml version=""1.0"" encoding=""UTF-8""?>
<Customers>
    <Customer>
        <element>
            <Name>CHAN TAI MAN</Name>
            <Date_of_Birth>28127</Date_of_Birth>
            <Gender>M</Gender>
            <Customer_Level>5</Customer_Level>
            <Telephone>22205555</Telephone>
            <Emails>
                <Email>CHAN.TAN.MAN@hotmail.com</Email>
                <Type>Work</Type>
            </Emails>
        </element>
        <element>
            <Name>JERRY SZE</Name>
            <Date_of_Birth>32239</Date_of_Birth>
            <Gender>M</Gender>
            <Customer_Level>2</Customer_Level>
            <Telephone>22203333</Telephone>
            <Emails>
                <Email>JERRY.SZE@yahoo.com</Email>
                <Type>Personal</Type>
            </Emails>
        </element>
        <element>
            <Name>MERRY SO</Name>
            <Date_of_Birth>32205</Date_of_Birth>
            <Gender>F</Gender>
            <Customer_Level>4</Customer_Level>
            <Telephone>22201111</Telephone>
        </element>
        <element>
            <Name>TOM HUI</Name>
            <Date_of_Birth>28827</Date_of_Birth>
            <Gender>M</Gender>
            <Customer_Level>1</Customer_Level>
            <Telephone>22204444</Telephone>
        </element>
        <element><Name>VICKY CHAN</Name>
            <Date_of_Birth>29718</Date_of_Birth>
            <Gender>F</Gender>
            <Customer_Level>3</Customer_Level>
            <Telephone>22202222</Telephone>
        </element>
    </Customer>
</Customers>

Example 3

In this example, the Data Dictionary is used.

In the Data Dictionary,

The field Gender of the Customer table is converted to lowercase.
The field Date of Birth of the Customer table is converted to 'yyyy-MM-dd' format.
The field Email of the Email table is converted to uppercase.

Returned XML:

<?xml version=""1.0"" encoding=""UTF-8""?>
<Customers>
    <Customer>
        <element>
            <Name>CHAN TAI MAN</Name>
            <Date_of_Birth>1977-01-02</Date_of_Birth
            <Gender>m</Gender>
            <Customer_Level>5</Customer_Level>                                   <Telephone>22205555</Telephone>
            <Emails>
                <Email>CHAN.TAN.MAN@HOTMAIL.COM</Email>                               <Type>Work</Type>
            </Emails>
        </element>
        <element>
            <Name>JERRY SZE</Name>
            <Date_of_Birth>1988-04-06</Date_of_Birth>                             <Gender>m</Gender>
            <Customer_Level>2</Customer_Level>                                   <Telephone>22203333</Telephone>
            <Emails>
                <Email>JERRY.SZE@YAHOO.COM</Email>                                   <Type>Personal</Type>
            </Emails>
        </element>
        <element>
            <Name>MERRY SO</Name>
            <Date_of_Birth>1988-03-03</Date_of_Birth>
            <Gender>f</Gender>
            <Customer_Level>4</Customer_Level>
            <Telephone>22201111</Telephone>
        </element>
        <element>
            <Name>TOM HUI</Name>
            <Date_of_Birth>1978-12-03</Date_of_Birth>
            <Gender>m</Gender>
            <Customer_Level>1</Customer_Level>
            <Telephone>22204444</Telephone>
        </element>
        <element>
            <Name>VICKY CHAN</Name>
            <Date_of_Birth>1981-05-12</Date_of_Birth>
            <Gender>f</Gender>
            <Customer_Level>3</Customer_Level>
            <Telephone>22202222</Telephone>
        </element>
    </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
Does not use ajAddress in Table address or Data dictionary address.
Duplicate table name in the data relationship table.
For REST API, the root table was configured with Multiple Row = TRUE.
Invalid table name, it shall begin with an uppercase letter and contain alphabets, numbers, and underscores only.
Key is not provided but parent table contain multiple row.
Parent table does not exist.
Parent table name is empty but key is not empty.
Table address is empty.
Table is empty.
Table name is a reserved Java keyword. For example, new, return, default etc.
Data relationship header range is invalid, it must contain at least one column.
Data relationship range is invalid, it must contain at least one column.

4.2

Database Operation

File Operation

External API Integration

Data Validation

Data Handling

Access Control

AlchemyJ Workbook

Business Logic

Java API

REST API

OpenAPI Spec for REST API

AlchemyJ Extended Functions