XML-invoice field layout
Introduction
In another article, *Digital invoice processing in combination with XML invoices*, the benefits of receiving and processing XML invoices were described. In order to read an XML invoice, an existing XML invoice field layout must be available. In the menu item 'Application Management / Invoice Processing / XML Invoice Field Layout', you will find all XML field layouts, also known as XML mappings, available in your environment.
There is a standard set of XML field layouts available for all customer environments. This set is managed by Visma | ProActive, and this is indicated by the fact that these XML field layouts cannot be edited or deleted. Additionally, it is possible to create your own XML field layout. This might be considered if the XML invoice:
- Is not recognized at all because the XML fields differ from the standard XML field layouts;
- Needs to be read more specifically because you want to transfer certain additional values at the invoice(line) level for a more complete/detailed invoice line coding.
To read an XML value, it must, of course, be included in the XML invoice. As long as a value is not included, it cannot be transferred. In that case, first coordinate with your creditor whether a missing value can be included in an existing XML field that is empty, contains the wrong format, or in an additional XML field that should be added by them with a desired value.
XML Field Layout
A distinction can be made between the standard mapping and the creditor-specific mappings. The standard format for XML invoices is UBL (Universal Business Language), most commonly versions UBL 2.0 and UBL 2.1. Extensive general information can be found on the Forum Standardisation website. Further explanation in this article is based on this standard.
Add a New XML Field Layout Yourself
First, investigate whether the XML file for which you want to add a field layout has field names that correspond to an existing XML field layout. If you want to create a more specific XML field layout where the XML is based on an already existing XML format, it is advisable to copy an existing XML field layout. For example, copy the mapping 'Standard UBL invoice field layout' by clicking the three dots at the end of the row and choosing to copy:
You can upload an XML file you received from the creditor.
Before saving a copied XML field layout, you must first adjust/expand the 'Recognition string' field, otherwise a conflict will occur with the existing XML field layout. See also the next paragraph 'Recognition String' in this article. If it is an entirely new XML format that does not resemble an existing XML field layout, you can add a completely new one via the 'Add' button at the top of the 'XML Invoice Field Layout' menu item. The disadvantage is that you will have to map all fields yourself.
Recognition String
To process an XML invoice, a Recognition String must be present. This is a string of characters based on which the file is recognized by Spend Cloud as an invoice. For UBL invoices, it boils down to any XML file containing one of the following strings being processable from the mailbox:
- //cbc:UBLVersionID='2.0' --> appears in the XML as <cbc:UBLVersionID>2.0
- //cbc:UBLVersionID='2.1' --> appears in the XML as <cbc:UBLVersionID>2.1
- //cac:AccountingCustomerParty --> appears in the XML as <cac:AccountingCustomerParty>
If you want to create a specific mapping for a creditor, a unique recognition string must be set. It is useful to look at other specific mappings and use these as examples. The creditor's name is always present in the field AccountingSupplierParty and as seen in the overview, there are several mappings with the recognition string set as: //cac:AccountingSupplierParty/cac:Party/cac:PartyName/cbc:Name='[Creditor Name]' ,where the last part is replaced with the creditor's name, as can be identified in the XML invoice.
Are you copying an existing standard creditor-specific XML mapping? Then append the text 'and 1' to the recognition string. This will ensure the mapping is applied instead of the existing mapping, for example with the mapping for Albert Heijn. This is because the mapping with the most similarities is applied. The addition of 'and 1' makes the mapping more specific than the general one, although you could also choose a different XML value to make it more specific than the standard XML mapping. Example:
(//cbc:UBLVersionID='2.0' or //cbc:UBLVersionID='2.1') and (//cac:AccountingSupplierParty/cac:Party/cac:PartyName/cbc:Name='Albert Heijn') and 1
You can test an XML field layout directly via the test functionality. Has the XML invoice already been added to your Spend Cloud environment? Then you can reapply the XML field layout by clicking on the three dots to the right of the invoice under Coding and then clicking 'Reapply PDF/XML recognition'. Spend Cloud will then reapply the mapping based on the recognition string and will recreate invoice lines if necessary. This can only be done for invoices that have not yet been coded. You can also do this for multiple invoices simultaneously via the 'Reapply recognition' button in the Coding overview.
Has the XML invoice already been coded? Then you cannot reapply the XML field layout, but in the Archive you can click on "Download visualization" for the relevant invoice. Using the Download visualization button, you can see which lines we retrieve from the XML invoice. If something is not as desired:
1. Modify the XML field layout under Application Management / Invoice Processing / XML Invoice Field Layout
2. Go back to the Archive and click Download visualization for the relevant invoice
If it is still not as desired, you can repeat steps 1 and 2 until the desired result is achieved.
If the XML invoice is still in the mailbox, you can also verify if your mapping works by downloading the PDF visualization of the XML.
Line Identifier
Once setting the recognition string is successful, a line identifier must be set. This is a field within the XML file that reads each invoice line. By default, this is //cac:InvoiceLine, and in the case of UBL invoices, no changes are needed. If the creditor delivers an XML invoice not structured according to the UBL format, it is important that each invoice line in the file is opened and closed with the same tag.
Invoice Structure
An XML file is attached containing the standard structure according to UBL, with a short explanation of the information typically included in these fields. An XML invoice is almost always structured with a general part first, the invoice header (invoice date, total amount, IBAN, etc.), followed by the specification per invoice line (item 1 on line 1, item 2 on line 2, etc.) with, per line, among other things, the price and description.
Mapping Invoice Header Data
In the header of the invoice, you will find data that can be used in mapping for the sections 'Invoice Information', 'Relation/Creditor Information', and 'Recipient Information'.
For example, to recognize the correct creditor, Spend Cloud primarily looks for the bank account number (IBAN). By default, this is included in the UBL format in the field: //cac:PaymentMeans/cac:PayeeFinancialAccount/cbc:ID . If the creditor is not recognized, a possible cause is that the bank account number does not match the one registered for the creditor in Spend Cloud or that the creditor, for example, did not include it in the XML file.
Here is an example of the XML path used to read an IBAN from a UBL-format XML invoice. Pay particular attention to the notation method. You must map a field by starting with '//' and each further level (indent or tab) is mapped with a slash '/' followed by the underlying field.
Mapping Invoice Line Data
Does your creditor provide specific data, such as the general ledger account number, cost center, or costunit? Then you can read these fields for as fully automatically filled-in invoice coding as possible. Suppose the creditor provides a cost center number in the field <cbc:AccountingCost>, then you could map this. Since this field is at invoice line level and a line identifier is specified at the top of the XML field layout (//cac:InvoiceLine), you do not need to include that part again in the field mapping cbc:AccountingCost suffices as a field value in this case.
In this way, you can coordinate with suppliers on which data you would like to see included in the XML file, and you can configure Spend Cloud to correctly interpret that data. This enables the automation of the invoice processing for those suppliers. You may need to get used to how XML files are structured, but once you understand it, you can use a custom XML field mapping to have invoice coding largely filled in automatically.
If the XML invoice line fields are provided correctly by the supplier, you should edit the supplier (relation) in Spend Cloud and change the type of “XML invoice grouping.” By default, only the XML invoice header data is read, and all XML invoice lines are grouped into a single invoice line. This is because some suppliers include a very large number of invoice lines, which would otherwise result in many separate lines in the “Coding” menu in Spend Cloud, even though the coding would be the same for each line. For this reason, the setting “Include lines from XML invoice” is not recommended. For example, if 15 different items are listed in the XML invoice, 15 invoice lines would be created. A better option at the relation level is “Custom grouping,” where you can group the XML invoice lines based on a specific field such as general ledger account or a combination of fields like general ledger account, cost center, and VAT percentage (if VAT is applicable). See below how this can be configured at the relation level:
Caution: If you work with the Procurement module, the purchase order match takes precedence over the XML invoice line data. For these suppliers, it's better to focus on as complete a purchase order coding as possible. What’s important for such suppliers is that the purchase order number is included in the XML file so that an automatic match can be made. By default, the purchase order number is included in the UBL format within the XML invoice lines (`cac:InvoiceLine`) in the field cac:OrderLineReference/cac:OrderReference/cbc:ID, and it can be configured in the “Order data” section. Optionally, the order number can also be included in the invoice header, but this is only possible if only one purchase order number applies per invoice. If you want to perform a more specific match, you could also map the fields Item Number and Quantity in the “Order data” section to allow for partial deliveries at the item level in the matching process.
Mutation log
It is possible to view a change overview for the XML field mapping. If any changes have been made and are not working as expected, you can see who made what changes here and easily copy the string to adjust the field mapping again.
Tips & Tricks for creating a mapping
Sometimes a supplier cannot provide the specific information in the XML in a way that Spend Cloud can interpret. Consider the following examples:
1. Instead of just the cost center number, the number and name are always included, like “1000 - Cost Center A.”
2. The supplier provides the cost center and cost object numbers in a single field, for example: “1000-4800.”
3. The supplier includes the number in a field that contains other variable information, for example: “Streetname 12B 1000,” where it always ends with the cost center number.
4. The supplier includes a description per invoice line, but for processing you want the combination “Supplier Name - Description” to appear in the line.
5. The supplier includes an element multiple times per invoice line, so you can't target the element directly, but need to use a parent element. For example:<cac:AdditionalItemProperty>
<cbc:Item>Cost Center Code</cbc:Item>
<cbc:Value>1000</cbc:Item>
</cac:AdditionalItemProperty>
<cac:AdditionalItemProperty>
<cbc:Item>General Ledger</cbc:Item>
<cbc:Value>4400</cbc:Item>
</cac:AdditionalItemProperty>
6. The supplier includes an element multiple times, making it impossible to target the element itself directly, for example:
<cac:InvoiceLine>
<cac:Item>
<cbc:Name>Item Description</cbc:Name>
</cac:Item>
</cac:InvoiceLine>
<cac:InvoiceLine>
<cac:Item>
<cbc:Name>I.1.24.123456</cbc:Name>
</cac:Item>
</cac:InvoiceLine>
With certain functions, it is still possible to extract the correct values. Below are a few functions that help retrieve the correct values:
1. <cbc:AccountingCost>1000 - Cost center A</cbc:AccountingCost> → Cost center 1000 needs to be extracted
Function: substring(cbc:AccountingCost,0,4) → this function extracts a portion of the value; starting at position 0, it reads the next 4 characters
2. <cbc:AccountingCost>1000-4800</cbc:AccountingCost> → Cost center 1000 and cost carrier 4800 need to be extracted
Function for cost center: substring-before(cbc:AccountingCost,'-') → extracts the portion before the hyphen
Function for cost unit : substring-after(cbc:AccountingCost,'-') → extracts the portion after the hyphen
3. <cbc:AccountingCost>Richard Holkade 9 1000</cbc:AccountingCost> → Cost center number 1000 needs to be extracted. The address before the number can vary per invoice, so you can't rely on a fixed value like with the substring-before or substring-after functions.
Function: substring(//cbc:AccountingCost, string-length(//cbc:AccountingCost) - 3) → this function calculates the total number of characters within the cbc:AccountingCost element and then extracts the last character plus the three characters before it. So, in total, four characters — the length of the cost center number in this example.
4. <cac:AccountingSupplierParty/cac:Party/cac:PartyName/cbc:Name>Visma|ProActive</cac:AccountingSupplierParty/cac:Party/cac:PartyName/cbc:Name> &
<cac:InvoiceLine/cac:Item/cbc:Description>Factuur Kwartaal 1</cac:InvoiceLine/cac:Item/cbc:Description> → Description should be *Visma|ProActive - Factuur Kwartaal 1*
Function: concat(//cac:AccountingSupplierParty/cac:Party/cac:PartyName/cbc:Name,' - ',cac:InvoiceLine/cac:Item/cbc:Description) → The concatenate function combines different values, separating them with a comma. By using quotation marks, plain text can be added — in this case, a dash is used.
5. <cac:AdditionalItemProperty><cbc:Item>Costcenter code</cbc:Item><cbc:Value>1000</cbc:Value></cac:AdditionalItemProperty>
The invoice line contains multiple elements like this, so you can’t simply rely on `cbc:Value`, since it’s not unique. However, you can make it unique by using the element above it, *Kostenplaatscode*. This value is unique, and we know that under the *Cost center code*, the corresponding code will always be provided.
Function: //cac:AdditionalItemProperty[cbc:Name='Cost center code']/cbc:Value` → In this string, we specify that we want to extract the value from `cbc:Value` only if the parent element contains a `cbc:Name` with the value 'Cost center code'.
6. <cac:InvoiceLine><cac:Item><cbc:Name>I.1.24.123456</cbc:Name></cac:Item></cac:InvoiceLine>
The invoice contains multiple `cbc:Name` elements where the supplier includes unique values. However, the parent element is not unique, making them hard to distinguish at first. In this example, we want to extract a purchase order number from the invoice.
Function: //cac:Item/cbc:Name[starts-with(., 'I.') and string-length(.) = 13] → In this case, we want to extract the value from `cbc:Name` only if it starts with an *I* and has a total length of 13 characters. This is a unique sequence used only for the order number and always has the same structure.
If you want to extract a cost center code, you can also target values in `cbc:Name` that consist only of digits and have a specific length. For example:
//cbc:Name[string-length(translate(., '0123456789', '')) = 0 and string-length(.) = 6] → in this example, the cost center code is 6 characters long.
Testing the XML Field Mapping
If you’ve created or modified a custom XML field mapping, you can test whether the XML file is read as expected. When you open an XML field mapping, click the **“Test Mapping”** button. Then upload an XML invoice and click the green button to apply the mapping. You will now see an overview of all fields that have a recognition string defined, and on the right side what was actually found in the XML. If nothing is found, this is shown in red. In that case, the recognition string may need to be adjusted. If you see an error message, it means the recognition string cannot be applied to the invoice. Adjust it or create a new field mapping with the correct recognition string.
Related Articles
Default conditions for UBL in the Spend Cloud
This technical information can be used by your accounting package's developer/application manager. This is how the Spend Cloud reads UBL invoices and processes the values. Curious how you can create your own unique XML field layout (mapping)? Read ...How can I receive XML invoices in the Spend Cloud?
The (design) objective of XML is simplicity, uniformity and usability. In order for these invoices to be read by all computers worldwide, certain rules have been laid down on how an XML invoice must be constructed. This set of standard rules ...Everything you need to know about XML-Invoices
Why process XML invoices? Our goal is to have users spend as little time as possible in the Spend Cloud. The software should do as much as possible automatically, so that our users can focus on other important tasks. Digital invoice processing helps ...Encoding an invoice
In the Coding menu, you'll find all the invoices that can be processed (coded). By clicking on a row or the pencil icon from the overview, you can access the page where you can code the invoice details. Encoding Invoices When you start encoding an ...Configuration settings for Invoice Processing
In the menu section Configuration Settings / Invoice Processing, you will find settings that apply to the Invoice Processing module. Also, read this article for more information on configuration settings. Make sure you understand what each setting ...