Splitting XML string as separate fields in message mapping

[former_member182412]

Hi Experts,

I am getting XML string in one field from source message, i need to separate those fields and map it to target fields.

Sorce message:

<?xml version="1.0" encoding="UTF-8"?>

<ns0:test_source xmlns:ns0="urn:gp:prototype">

<Document>

<store>3021</store>

<date>2011-03-24</date>

<type>3002</type>

<till>32</till>

<transaction>1478</transaction>

<data><![CDATA[<buy><merchantID>006001007031992</merchantID><laneID>29</laneID><referenceID>PP0323095107</referenceID><localDate>2011-03-2302:00</localDate><localTime>09:51:0702:00</localTime></buy>]]></data>

</Document>

</ns0:test_source>

Target Message:

<?xml version="1.0" encoding="UTF-8"?>

<ns0:test_target xmlns:ns0="urn:gp:prototype">

<item>

<storeID>3021</storeID>

<businessdate>2011-03-24</businessdate>

<Typecode>3002</Typecode>

<workstation>32</workstation>

<SeqNum>1478</SeqNum>

<XX>006001007031992</XX>

<YY>29</YY>

<PP>PP0323095107</PP>

<QQ>2011-03-23+02:00</QQ>

<RR>09:51:07+02:00</RR>

</item>

</ns0:test_target>

Can anyone please suggest me how can we do this???

[baskar_gopalakrishnan2 · Accepted solution]

You can easily do both using standard function or simple udf

Use indexOf and substring to achieve this...

Simple UDF

String var1="><![CDATA<buy><merchantID>006001007031992</merchantID><laneID>29</laneID><referenceID>PP0323095107</referenceID><localDate>2011-03-23+02:00</localDate><localTime>09:51:07+02:00</localTime></buy>]>";
String search = "<merchantID>";
if(var1.indexOf(search) != -1){
int pointer = var1.indexOf(search);
return var1.substring(pointer+12, pointer+27);
}else{
return "";
}

similarly for laneid

search ="<laneID>";
if(var1.indexOf(search)!= -1){
   int pointer = var1.indexOf(search);
    return var1.substring(pointer+8,pointer+10);
}else{
   return "";					
}

Same way you can do for all the elements creating seperate method for each element or just use standard function to achieve this.

Edited by: Baskar Gopal on Mar 24, 2011 10:13 AM

[former_member182412 · Accepted solution]

Hi Baskar,

I did exactly similar solution but i have more than 20 fields in that XML string, but i wrote one function with two parameters, one is XML string and another one is the tag name(like merchantID) which i want, but still i need to call that function 20 times.

String rslt = "";

int cnt1 = var1.indexOf(var2);

if (cnt1!= -1){

int cnt2 = var1.indexOf(">",cnt1);

int cnt3 = var1.indexOf("<",cnt2);

rslt = var1.substring(cnt2+1,cnt3);

}

return rslt;

what i am thinking is can we write one function to get all the 20 tagname values into one fixedlength string and save it in a graphical variable, after that we can use substring function for all the fields, this will give us better performance.

Can anyone accept this?? if accept can you please provide me the UDF for this, because i am not java expert.

[RKothari · Accepted solution]

Hello,

May be you use below code for the logic you have mentioned.

You need to define an array of 20 and store the field names which you would like to search in the input xml string.

String [] arr = new String[]{"MerchantID","LaneID" };
String rslt = "";
int i, cnt1, cnt2, cnt3;

for(i=0;i<arr.length;i++)
{
	cnt1 = var1.indexOf(arr<i>);
	if (cnt1!= -1)
	{
		cnt2 = var1.indexOf(">",cnt1);
		cnt3 = var1.indexOf("<",cnt2);
		if(rslt.equals(""))
		{
			rslt = va1.substring(cnt2+1,cnt3);
		}
		else
		{
			rslt = rslt + ":" + var1.substring(cnt2+1,cnt3);
		}
	}
cnt1 = 0;
cnt2= 0;
cnt3= 0;
}
return rslt;

The result should return you a string separated by ":" .

-Rahul

[Former Member · Accepted solution]

Why not going for regular expressions ? It's more powerful and flexible if your source message content changes (different field lengths, content etc) ?

Chris

[Former Member]

You can use an UDF function, with a similar code:

import java.io.File;

import org.w3c.dom.Document;

import org.w3c.dom.*;

import javax.xml.parsers.DocumentBuilderFactory;

import javax.xml.parsers.DocumentBuilder;

import org.xml.sax.SAXException;

import org.xml.sax.SAXParseException;

try {

Document doc = docBuilder.parse (XML_DATA);

// normalize text representation

doc.getDocumentElement ().normalize ();

NodeList LV_TAG1 = doc.getElementsByTagName("TAGNAME");

Element LV_ELEMENT = (Element)LV_TAG1.item(0);

NodeList LV_LIST = LV_ELEMENT.getChildNodes();

}

return = ((Node)LV_LIST.item(0)).getNodeValue().trim());