AlchemyJ by Axisoft

4.2

ajXMLToTable function

Description

The ajXMLToTable function extracts node values from an XML string and converts them into an array of data. When the selected nodes exist at different levels, it flattens the structure and this will be repeated for node values at a higher level.

Syntax

ajXMLToTable(table_column, xml_data, [traversal_level], [Convert_to_text])

Argument NameArgument TypeDescription
table_column
(required)		RangeThe range of cells (row only) in which the value in each cell represents a node name in the XML string.

The wording must be the same and it is case-sensitive. These node names can be at different levels in the XML string.

xml_data
(required)		Range / ArrayThe range of cells that contains the XML string. The range of cells can span across multiple rows and columns.

The cell values will be concatenated into one string from left to right and top to bottom.

traversal_level
(optional)		DoubleLevel of the path of the node should be searched from the XML string. The top-level is 1.

All the levels in the XML string will be searched by default.

convert_to_text
(optional)		BooleanIf it equals TRUE, the return result will be converted to string values. If it equals FALSE, the return result will preserve the original data type.

The default value is FALSE.

The function will return:

1) Return Value: Populated XML string in a range of cells

2) Return Type: Multiple values (array formula)

Examples

The XML contains customer information and locate in B1. Here is the XML using in the examples.

<?xml version="1.0" encoding="UTF-8"?>
<Customers>
    <Customer>
        <element>
            <Name>CHAN TAI MAN</Name>
            <Date_of_Birth>1/2/1977</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>4/6/1988</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>3/3/1988</Date_of_Birth>
            <Gender>F</Gender>
            <Customer_Level>4</Customer_Level>
            <Telephone>22201111</Telephone>
        </element>
        <element>
            <Name>TOM HUI</Name>
            <Date_of_Birth>12/3/1978</Date_of_Birth>
            <Gender>M</Gender><Customer_Level>1</Customer_Level>
            <Telephone>22204444</Telephone>
        </element>
        <element>
            <Name>VICKY CHAN</Name>
            <Date_of_Birth>5/12/1981</Date_of_Birth>
            <Gender>F</Gender>
            <Customer_Level>3</Customer_Level>
            <Telephone>22202222</Telephone>
        </element>
    </Customer>
</Customers>

Example 1

A common scenario is to define the required columns as shown in B4 to H4. Then, use an array formula of ajXMLToTable from B5 to H11 to populate the data into the range.

Since the ‘Traversal_level’ is not defined, the function searches for the node name at all levels.

Example 2

Using the same XML string, this example set Traversal_level to 3 which limits the node search to the third level only. Email and Type value are empty.

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 traversal level, it must be 0 or above.
Invalid XML Format.
XML data contains an Excel error.
← ajXMLFromTableSupported Excel Functions →