xml

Load data from an XML file.

Introduction

Use the xml enrichment 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 the enrichment

For more information on creating an enrichment, see Using custom scripts.

Configuring the enrichment

To configure the enrichment, 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 contain the enriched data. The enrichment is applied to the whole data extract, then the enriched data is output into the subtable you have named here.

This subtable is a temporary table, which means it 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.

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>

Enrichment 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