Rearrange XML Element using UDF
Posted on Apr. 27, 2010 at 06.49 PM - Kuala Lumpur, Malaysia
Sometime, we need to rearrange element from source to target XML. one typical example is purchase order at the source system (customer perspective), which is normally combined as one. However at the target system (vendor perspective), PO has to be split based on the company that supply the product.
Source XML Example
<?xml version="1.0" encoding="UTF-8"?>
<ns0:POCombined xmlns:ns0="urn:education.com:BIT460:mapping">
<Header>
<Name>Test</Name>
<Address>Jelatek</Address>
</Header>
<Detail>
<Supplier>SA</Supplier>
<ProductID>PA</ProductID>
<Quantity>1</Quantity>
<Price>1</Price>
</Detail>
<Detail>
<Supplier>SA</Supplier>
<ProductID>PC</ProductID>
<Quantity>1</Quantity>
<Price>1</Price>
</Detail>
<Detail>
<Supplier>SB</Supplier>
<ProductID>PB</ProductID>
<Quantity>2</Quantity>
<Price>2</Price>
</Detail>
</ns0:POCombined>
Target XML Example
<?xml version="1.0" encoding="UTF-8"?>
<ns0:POSplit xmlns:ns0="urn:education.com:BIT460:mapping">
<Orders>
<Header>
<toCompany>SA</toCompany>
<custName>Test</custName>
<custAddress>Jelatek</custAddress>
</Header>
<Items>
<productNo>PA</productNo>
<quantity>1</quantity>
<price>1</price>
</Items>
<Items>
<productNo>PC</productNo>
<quantity>1</quantity>
<price>1</price>
</Items>
</Orders>
<Orders>
<Header>
<toCompany>SB</toCompany>
<custName>Test</custName>
<custAddress>Jelatek</custAddress>
</Header>
<Items>
<productNo>PB</productNo>
<quantity>2</quantity>
<price>2</price>
</Items>
</Orders>
</ns0:POSplit>
Step 1: Create UDF to get unique Supplier (Method getSupplier)
Vector v = new Vector();
for (int i=0; i<supplier.length; i++) {
if (!v.contains(supplier[i]))
v.add(supplier[i]);
}
for (int i=0; i<v.size(); i++) {
result.addValue((String)v.get(i));
}
Step 2: Create UDF to populate number of Item (Method assignItem)
Vector v = new Vector();
for (int i=0; i<supplier.length; i++) {
if (!v.contains(supplier[i]))
v.add(supplier[i]);
}
for (int i=0; i<v.size(); i++) {
String s = (String) v.get(i);
for (int j=0; j<supplier.length; j++) {
if (s.equals(supplier[j]))
result.addValue(" ");
}
result.addContextChange();
}
Step 3: Create UDF to get Item Field (Method assignSupplier)
Vector v = new Vector();
for (int i=0; i<supplier.length; i++) {
if (!v.contains(supplier[i]))
v.add(supplier[i]);
}
for (int i=0; i<v.size(); i++) {
String s = (String) v.get(i);
for (int j=0; j<supplier.length; j++) {
if (s.equals(supplier[j]))
result.addValue(detailField[j]);
}
}
Step 4: Create Header & Header Field Mapping
The only different between header and header field mapping, is that standard function SplitByValue is used for header field mapping
Step 5: Create Item Mapping
Step 6: Create Item Field Mapping
Life is beautiful! Let's make it meaningful and colorful!