xml

Load data from an XML file.

Introduction

Use the xml instruction to load data from an XML file. Use XPath syntax to navigate the XML file. For more information on XML path syntax, see this Syntax reference.

Creating a custom script transformation using the xml instruction

To create and configure a custom script using the xml instruction, follow these steps:

1. Create a custom script transformation.

2. In the Instructions step, select the xml instruction.

3. To configure the custom script instruction, fill in the following fields. Required fields are marked with an asterisk (*).

Row Match*

Specify the rows to load from the XML file in one of the following ways:

• Select Array, and enter the positions of the rows. The count starts at 0. To load the first row, enter 0.

• Select String, and enter the XML paths to the rows. If the data is nested, use / to find the nested data. For example, enter /report/items/item to collect item nested within items nested within report. You can use regular expressions to find values.

Mapping*

Specify the column names Adverity uses in the data extract for the fields in the XML file. In the left field, enter the names of the columns to use in the data extract. In the right field, enter the names of the fields in the XML file.

Namespaces

Map new values onto XML namespaces. In the left field, enter the name of the namespace to be renamed. In the right field, enter the new name for the namespace.

Html

Select this checkbox if the XML file to be loaded is in an HTML format.

Subtable

Enter the name for a subtable that you want to use within this custom script.

A subtable is a temporary table that only exists for this custom script. You can apply additional instructions within the same custom script to the subtable. However, the subtable cannot be used in any other custom scripts.

If a subtable does not exist for the current custom script, the transformation is applied to the data extract, and the enriched data is output into the subtable. If the subtable already exists for the custom script, the subtable is used as the input for the transformation and optionally as the output.

Example

XML file to load

<?xml version="1.0" encoding="UTF-8"?>
<Envelop
    SubsidiaryId="WEBS" 
    MessageDate="2021-05-19" 
    ReportEndDate="2021-05-18" 
    ReportStartDate="2021-05-18" 
    TestIndication="false" 
    ReceiverId="" 
    SenderId="112234567890" 
    FileTime="01:10:00.125" 
    FileDate="2022-01-19" 
    FileId="0">
    <Sender
        Country="NLD"
        City="Rotterdam"
    <Receiver/>
    <SalesReports>
        <SalesReport
            ChannelType="WEBSLS" 
            Currency="EUR" 
            ReportingPeriod="Day">
        <Webshop
            WebshopCode="WEBSHOP01" 
            WebShopCodeIDType="EXB"/>
            <Lines>
                <Line
                    Quantity="1"
                    SalesPriceExVAT="4.95"
                    SalesValueExVAT="4.09"
                    DocumentTime="20:28:09"
                    DocumentDate="2021-05-18"
                    LineNo="30000">
                    <WebOrderReference
                        ExternalOrderNo="0788115"
                        OrderNo="WO21-55243"/>
                    <Item
                        MerchandiseCode="99980"
                        ItemNo="VERZEND"/>
                    <Customer
                        CustomerNo="WEBSHOP01"
                        RetailCustomerNo="5629765"/>
                </Line>
            </Lines>
        </SalesReport>
    </SalesReports>
</Envelop>

Transformation configuration

Using this configuration, only those parts of the XML path Line are loaded which are included in Mapping*. The XML paths SalesReport, Sender, and Webshop are not loaded.

Row Match*

//SalesReport/Lines/Line

Mapping*

New fieldname	Value type	XML Path to fieldname
Quantity	String	./@Quantity
SalesPriceExVAT	String	./@SalesPriceExVAT
SalesValueExVAT	String	./@SalesValueExVAT
DocumentTime	String	./@DocumentTime
DocumentDate	String	./@DocumentDate
LineNo	String	./@LineNo
ExternalOrderNo	String	./WebOrderReference/@ExternalOrderNo
OrderNo	String	./WebOrderReference/@OrderNo
MerchandiseCode	String	./Item/@MerchandiseCode
ItemNo	String	./Item/@ItemNo
CustomerNo	String	./Customer/@CustomerNo
RetailCustomerNo	String	./Customer/@RetailCustomerNo