Capture value btwn XML tags, split it and use it elsewhere

Get help with installation and running here.

Moderators: DataMystic Support, Moderators, DataMystic Support, Moderators, DataMystic Support, Moderators

Post Reply
DM_Cal
Posts: 10
Joined: Thu Oct 18, 2007 9:08 pm

Capture value btwn XML tags, split it and use it elsewhere

Post by DM_Cal »

I am trying to do the following. Search for a value held between a pair of XML tags(<ENVELOPE_PARM><VALUE>) then take that value and split it into two parts and then use them elsewhere in the xml file. Also I need to then overwrite that original value with one of the two parts.
So if I get this in:-
<ENVELOPE>
<CONTROL_NUMBER>321</CONTROL_NUMBER>
<FUNCTION>0</FUNCTION>
<SENDER_ID>Company X</SENDER_ID>
<RECEIVER_ID>Company Y</RECEIVER_ID>
<ENVELOPE_PARM>
<NAME>LookupAlias</NAME>
<VALUE>ACBTYPE</VALUE>
</ENVELOPE_PARM>
</ENVELOPE>
I need to search for the value given between <VALUE> and </VALUE> within the <ENVELOPE_PARM> and then capture that value and split it into two...part A which will the first 2-4 chars and part B that can either be BTYPE or ATYPE. The first 2-4 chars varies and could be anything up to 100 different values (eg could be AC as per above or ABCD or WXYZ or PQ etc). I need to then output part one in the RECEIVER_ID field and part two in the SENDER_ID field. I also need to overwrite the VALUE with whatever part two is. Therefore I should get out from the above this:-

<ENVELOPE>
<CONTROL_NUMBER>321</CONTROL_NUMBER>
<FUNCTION>0</FUNCTION>
<SENDER_ID>BTYPE</SENDER_ID>
<RECEIVER_ID>AC</RECEIVER_ID>
<ENVELOPE_PARM>
<NAME>LookupAlias</NAME>
<VALUE>BTYPE</VALUE>
</ENVELOPE_PARM>
</ENVELOPE>

Obviously some restrict needs to be done but I'm struggling with the capture and split of the variable part. Can you give any pointers please?
many thanks
User avatar
DataMystic Support
Site Admin
Posts: 2227
Joined: Mon Jun 30, 2003 12:32 pm
Location: Melbourne, Australia
Contact:

Re: Capture value btwn XML tags, split it and use it elsewhere

Post by DataMystic Support »

Why not just use

Code: Select all

<VALUE>(.*)([AB]TYPE)</VALUE>
and replace with

Code: Select all

<RECEIVER_ID>$1</RECEIVER_ID> 
<VALUE>$2</VALUE>
DM_Cal
Posts: 10
Joined: Thu Oct 18, 2007 9:08 pm

Re: Capture value btwn XML tags, split it and use it elsewhere

Post by DM_Cal »

I tried this using 'find perl pattern' (which was the only one that seemed to 'work') and I got almost what I wanted except that these are in the wrong place:-

<ENVELOPE>
<CONTROL_NUMBER>321</CONTROL_NUMBER>
<FUNCTION>0</FUNCTION>
<SENDER_ID>Company X</SENDER_ID>
<RECEIVER_ID>Company Y</RECEIVER_ID>
<ENVELOPE_PARM>
<NAME>LookupAlias</NAME>
<SENDER_ID>BTYPE</SENDER_ID>
<RECEIVER_ID>AC</RECEIVER_ID>
<VALUE>BTYPE</VALUE>

</ENVELOPE_PARM>
</ENVELOPE>

I need the existing <sender_id>/r<eceiver_id> information to be overwritten with the information from the <value> but I need them to be kept in the same sequence as the original file(please see example of output I was looking for in first post). Can this be done?
User avatar
DataMystic Support
Site Admin
Posts: 2227
Joined: Mon Jun 30, 2003 12:32 pm
Location: Melbourne, Australia
Contact:

Re: Capture value btwn XML tags, split it and use it elsewhere

Post by DataMystic Support »

Sure - precede this filter with one to remove

Code: Select all

<SENDER_ID>.*</SENDER_ID>
and

Code: Select all

<RECEIVER_ID>.*</RECEIVER_ID>
and then change the search pattern to anchor it to the desired location:

e.g.

Code: Select all

</FEEDBACK>(.*)<VALUE>(.*)([AB]TYPE)</VALUE>
replace with

Code: Select all

</FEEDBACK>$1
<RECEIVER_ID>$2</RECEIVER_ID>
<VALUE>$3</VALUE>
or similar. You just need to match the text in between and ensure it gets re-instated in the right spot.
DM_Cal
Posts: 10
Joined: Thu Oct 18, 2007 9:08 pm

Re: Capture value btwn XML tags, split it and use it elsewhere

Post by DM_Cal »

Fantastic. It worked as you intimated if I removed the <Sender_ID> and <Receiver_ID> and then did a:-

FIND
<ENVELOPE>(.*)</FUNCTION>(.*)<VALUE>(.*)([AB]TYPE)</VALUE>(.*)

REPLACE WITH
<ENVELOPE>$1
<SENDER_ID>$4</SENDER_ID>
<RECEIVER_ID>$3</RECEIVER_ID>$2<VALUE>$4</VALUE>$5

Had to remove a line with a space in it afterwards but that aside it does what was required

many thanks
Post Reply