AlchemyJ by Axisoft

4.0

ajMakeXMLFromSchema function

Description

The ajMakeXMLFromSchema function returns an XML formatted message based on the table relationship definition. If there are multiple tables with dependency, the XML would be multi-level.

Syntax

ajMakeXMLFromSchema(tables_relationship_header, tables_relationship, [root_name])

Argument NameDescription
tables_relationship_header
(required)		Specify the Excel range which defines the header of the Data Relationship Schema.
tables_relationship
(required)		Specify the Excel range which defines the content of the Data Relationship Schema. The empty row will be ignored.
root_Name
(optional)		Specify the root node name in XML.

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

The function will return:

1) Content type: XML

2) Method: Within a cell / row cell array. 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>
← ajMakeXMLajMakeXMLFromTable →