Extract tables with key/value columns

You can use a special extraction class to extract tables that contain cells with keys/value pair. A new event attribute is created for each extracted key of an event.

The following example shows a table in the source system that contains key value attributes for an event.

Table "WF_ITEMS" (system event table, header table for work items)

ID (key)

TYPE

TEXT

CREATOR

STAT

000000532657

F

Create a benefit adjustment reason for employee 00001448 due to any event.

Creating an adjustment reason due to event...

COMPLETED

For each of these work items (that is, for each line of the "WF_ITEMS" table), there are arbitrary attributes that are stored in the table "WF_ITEM_ATTRIBUTES" as key/value:

Table "WF_ITEM_ATTRIBUTES"

ID (key)

ATTRIBUTE (key)

VALUE

000000532657

DATEOFVALIDITY

20020101

000000532657

EMPLOYEENUMBER

00001448

000000532657

EVENT

CREATED

000000532657

OBJECTTYPE

FAMILY

000000532657

_WF_INITIATOR

USWF-BATCH

000000532657

_WF_PRIORITY

5

Extraction class

com.idsscheer.ppm.xmlextractortools.extractor.sap2ppm.ZTableKeyValueFieldsToAttributes_sap2ppm

This extraction class creates a new attribute for the source system event from each key value found, and the value becomes the value of the attribute. For this class, you must specify the names of the key and value column as parameters separated by "#-#".

All key and value fields must be specified as fields to be read (XML element fieldtoread). Additional fields to be read are not allowed.

The following configuration refers to the JDBC extraction class mentioned above:

table extraction configuration

The following example event was read out with the above configuration:

<event>
<attribute type="WF_ITEMS-ID">000000532656</attribute>
<attribute type="WF_ITEMS-CREATOR">Creating an adjustment reason due to event...</attribute>
<attribute type="WF_ITEMS-STAT">COMPLETED</attribute>
<attribute type="WF_ITEMS-TEXT">Create a benefit adjustment reason for employee 00001448 due to any event.</attribute>
<attribute type="WF_ITEMS-TYPE">F</attribute>
‘ <attribute type="WF_ITEM_ATTRIBUTES-DATEOFVALIDITY ">20020101</attribute>
<attribute type="WF_ITEM_ATTRIBUTES-EMPLOYEENUMBER ">00001448</attribute>
<attribute type="WF_ITEM_ATTRIBUTES-EVENT ">CREATED</attribute>
<attribute type="WF_ITEM_ATTRIBUTES-OBJECTTYPE ">FAMILY</attribute>
<attribute type="WF_ITEM_ATTRIBUTES-_WF_INITIATOR ">USJOSWIGT</attribute>
<attribute type="WF_ITEM_ATTRIBUTES-_WF_PRIORITY ">5</attribute>
</event>

Special characters in keys

The values of the key columns are used in the type XML attribute of the attribute XML elements. These values can contain characters that must not be used in this XML attribute. For this reason, the values of the type attribute of the attribute XML element are checked for invalid XML characters and such characters are filtered out if necessary.