FIS: Data Mapping Use Case for creating Nested XML using Grouping Logic

Learning Objective:

How do you use key functions in mapping to check duplicate data fields?
Data mapping logic is used to create single root records for duplicate data fields belonging to multiple records and then generate multiple child records belonging to the same duplicate record.
Check Null or Empty values in Data Mapping.

Overview

The specifications for the Mapping transformation process aim to convert input XML data into a predefined output XML format. The transformation focuses on handling individual records and their associated addresses, with a specific emphasis on leveraging the Key function to ensure the unique identification of address records.

Scope

The project's scope encompasses:

Establishing the layout of the input XML.
Describing the guidelines for producing the output XML.
Highlighting the significance of utilizing the Key function for identifying address records.

Pre-Requisites:

Download the Object Zip: Zip Link

Input XML Structure

The input XML consists of a root element <Root> containing multiple <Record> elements. Each <Record> represents an individual's data with various sub-elements. Key fields in the input XML include:

Personal Information: A_DriversLicenseNo, A_FirstNames, A_MiddleName, A_Name, A_DateOfBirth, A_Reference, A_NationalNo, A_PartyType, A_BusinessIndividual, A_PhoneHome, A_EmailHome, A_bu_Name, A_is_deceased, A_InvoiceConsolidate.
Address Information: C_AddressType, C_Street, C_CountryRegionId, C_StateProvinceId, C_CityId, C_ZipCode, C_EffectDtFrom, C_EffectDtTo, C_IsCurrent.

Output XML Structure

The output XML is structured to contain the following hierarchical elements:

<Root>: The root element with an attribute BatchUploadType.
<VOUploadBatch>: Container element.
- <UploadPackages>: Container for multiple packages.
  - <VOBaseCollection>: Collection element.
    - <VOUploadPackage>: Container for individual packages.
      - <VOPartyContainer>: Contains individual and related information.
        <Party>: Contains party details.
        <VOPartyIndividual>: Contains personal information.
        <PartyAddresses>: Contains address details.
        <VOBaseCollection>: Collection of addresses.
        <VOPartyAddress>: Individual address information.

Transformation Rules

Personal Information Transformation

Extract personal details from each <Record> element.
Ensure each individual is unique based on A_DriversLicenseNo.
Populate the <VOPartyIndividual> element with the corresponding fields.

Address Information Transformation (Highlighted)

Purpose: To uniquely identify address records based on a combination of address-related fields using the addressKey function.

Definition:

`<xsl:key name="addressKey" match="Root/Record" use="concat(C_AddressType, C_Street, C_CountryRegionId, C_StateProvinceId, C_CityId, C_ZipCode, C_EffectDtFrom, A_DriversLicenseNo)" />`

`<xsl:key name="addressKey" match="Root/Record" use="concat(C_AddressType, C_Street, C_CountryRegionId, C_StateProvinceId, C_CityId, C_ZipCode, C_EffectDtFrom, A_DriversLicenseNo)" />`

Explanation:

Key Name: addressKey
Match: Root/Record – This indicates that the key applies to all <Record> elements within the <Root> element.
Use: concat(C_AddressType, C_Street, C_CountryRegionId, C_StateProvinceId, C_CityId, C_ZipCode, C_EffectDtFrom, A_DriversLicenseNo) – This specifies the unique combination of fields used to generate the key. The fields C_AddressType, C_Street, C_CountryRegionId, C_StateProvinceId, C_CityId, C_ZipCode, C_EffectDtFrom, and A_DriversLicenseNo are concatenated to form a unique identifier for each address record.

Usage in XSLT:

<xsl:for-each select="/Root/Record[A_DriversLicenseNo = $varDriversLicenseNo]">

<xsl:if test="generate-id(.) = generate-id(key('addressKey', concat(C_AddressType, C_Street, C_CountryRegionId, C_StateProvinceId, C_CityId, C_ZipCode, C_EffectDtFrom, A_DriversLicenseNo)))">

<VOPartyAddress>

<AddressType>

<xsl:value-of select="./C_AddressType"/>

</AddressType>

<Street>

<xsl:value-of select="./C_Street"/>

</Street>

<CountryRegionId>

<xsl:attribute name="location_type">

<xsl:value-of select="'Country'"/>

</xsl:attribute>

<xsl:attribute name="name">

<xsl:value-of select="./C_CountryRegionId"/>

</xsl:attribute>

<xsl:attribute name="owner_id">

<xsl:value-of select="'The World'"/>

</xsl:attribute>

</CountryRegionId>

<StateProvinceId>

<xsl:attribute name="location_type">

<xsl:value-of select="'State/Province'"/>

</xsl:attribute>

<xsl:attribute name="name">

<xsl:value-of select="./C_StateProvinceId"/>

</xsl:attribute>

<xsl:attribute name="owner_id">

<xsl:value-of select="./C_CountryRegionId"/>

</xsl:attribute>

<xsl:value-of select="./C_StateProvinceId"/>

</StateProvinceId>

<CityId>

<xsl:attribute name="location_type">

<xsl:value-of select="'City'"/>

</xsl:attribute>

<xsl:attribute name="name">

<xsl:value-of select="./C_CityId"/>

</xsl:attribute>

<xsl:attribute name="owner_id">

<xsl:value-of select="./C_StateProvinceId"/>

</xsl:attribute>

<xsl:value-of select="./C_CityId"/>

</CityId>

<ZipCode>

<xsl:value-of select="./C_ZipCode"/>

</ZipCode>

<EffectDtFrom>

<xsl:value-of select="./C_EffectDtFrom"/>

</EffectDtFrom>

<EffectDtTo>

<xsl:value-of select="./C_EffectDtTo"/>

</EffectDtTo>

<IsCurrent>

<xsl:value-of select="./C_IsCurrent"/>

</IsCurrent>

</VOPartyAddress>

</xsl:if>

</xsl:for-each>

<xsl:for-each select="/Root/Record[A_DriversLicenseNo = $varDriversLicenseNo]">

<xsl:if test="generate-id(.) = generate-id(key('addressKey', concat(C_AddressType, C_Street, C_CountryRegionId, C_StateProvinceId, C_CityId, C_ZipCode, C_EffectDtFrom, A_DriversLicenseNo)))">

<VOPartyAddress>

<AddressType>

<xsl:value-of select="./C_AddressType"/>

</AddressType>

<Street>

<xsl:value-of select="./C_Street"/>

</Street>

<CountryRegionId>

<xsl:attribute name="location_type">

<xsl:value-of select="'Country'"/>

</xsl:attribute>

<xsl:attribute name="name">

<xsl:value-of select="./C_CountryRegionId"/>

</xsl:attribute>

<xsl:attribute name="owner_id">

<xsl:value-of select="'The World'"/>

</xsl:attribute>

</CountryRegionId>

<StateProvinceId>

<xsl:attribute name="location_type">

<xsl:value-of select="'State/Province'"/>

</xsl:attribute>

<xsl:attribute name="name">

<xsl:value-of select="./C_StateProvinceId"/>

</xsl:attribute>

<xsl:attribute name="owner_id">

<xsl:value-of select="./C_CountryRegionId"/>

</xsl:attribute>

<xsl:value-of select="./C_StateProvinceId"/>

</StateProvinceId>

<CityId>

<xsl:attribute name="location_type">

<xsl:value-of select="'City'"/>

</xsl:attribute>

<xsl:attribute name="name">

<xsl:value-of select="./C_CityId"/>

</xsl:attribute>

<xsl:attribute name="owner_id">

<xsl:value-of select="./C_StateProvinceId"/>

</xsl:attribute>

<xsl:value-of select="./C_CityId"/>

</CityId>

<ZipCode>

<xsl:value-of select="./C_ZipCode"/>

</ZipCode>

<EffectDtFrom>

<xsl:value-of select="./C_EffectDtFrom"/>

</EffectDtFrom>

<EffectDtTo>

<xsl:value-of select="./C_EffectDtTo"/>

</EffectDtTo>

<IsCurrent>

<xsl:value-of select="./C_IsCurrent"/>

</IsCurrent>

</VOPartyAddress>

</xsl:if>

</xsl:for-each>

This key groups and uniquely identifies address records within the <Root> element, ensuring that each unique combination of the specified fields is processed only once.

Data Handling

Use XSL keys to manage and ensure unique records.
Sort data based on A_DriversLicenseNo.
Implement conditional checks for specific fields to handle null or empty values.
Use XSL variables to manage intermediate data during transformation.

Output Generation

The output XML is generated with proper indentation and UTF-8 encoding.
Ensure all relevant data from input XML is transformed and included in the output XML.