| Author |
Message
|
| LH33 |
 Posted: Mon Jun 16, 2003 12:43 pm Post subject: Concatening multiple fields based on content |
 |
|
Master
Joined: 21 Nov 2002
Posts: 200
|
HI! I have a requirement to concatenate multiple input XML tags (that have data) delimited by a space together to form one output XML tag. The input XML data will vary from transaction to transaction as to whether data is present in the tags or not. In the example below, can someone help me with suggestions how to code it to get one concatenated field:
<NAME>CHARLES</NAME> Should be included in output tag.
<TYPE></TYPE Should not be included in output tag.
<DIR>NW</DIR> Should be included in output tag.
<CIVIC></CIVIC Should not be included in output tag.
<ART>SOUTH</ART Should be included in output tag.
<APPT></APPT Should not be included in output tag.
Is there any way to do this without a lot of If statements? The output tag should look like the following:
<PRIMARY>CHARLES NW SOUTH></PRIMARY>
Thanks for any help!! |
|
| Back to top |
|
 |
| kirani |
| Posted: Mon Jun 16, 2003 3:37 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
I hope this code will work for you.
I have not tested this code, so you might have to modify it a little bit.
| Code: |
DECLARE DATA CHAR;
DECLARE index int;
set index = 1;
DECLARE inref REFERENCE TO InputRoot.XML.Data;
move inref FIRSTCHILD;
WHILE (LASTMOVE(inref)) DO
if ( inref <> '' or inref <> null ) then
if ( index = 1 ) then
SET DATA = inref || ' ';
else
SET DATA = DATA || ' ' || inref;
end if;
end if;
SET index = index + 1;
move inref NEXTSIBLING;
END WHILE;
SET OutputRoot.XML.PRIMARY = DATA;
|
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| LH33 |
| Posted: Mon Jun 16, 2003 4:32 pm Post subject: |
|
|
Master
Joined: 21 Nov 2002
Posts: 200
|
Kiran,
Thanks! I will try this and let you know! You have been a great help!
Thanks again, Lisa |
|
| Back to top |
|
|
| LH33 |
| Posted: Tue Jun 17, 2003 7:26 am Post subject: |
|
|
Master
Joined: 21 Nov 2002
Posts: 200
|
Kiran,
I have coded the following:
DECLARE inref REFERENCE to InputRoot.XML.CompleteOdlJob.DataArea.ODL_LIGHT_INFORMATION.ODL_PRIMARY_STREET;
Move inref FIRSTCHILD;
WHILE (LASTMOVE(inref)) DO
if (inref <> ' ' or inref IS NOT null) then
if (index = 1) then
SET PRIMARY = inref || ' ';
else
SET PRIMARY = PRIMARY || ' ' || inref;
end if;
end if;
SET index = index + 1;
if index = 7 then
set index = 200;
move inref NEXTSIBLING;
end if;
END WHILE;
SET OutputRoot.XML.CompleteOdlJob.DataArea.ODL_LIGHT_INFORMATION.ODL_PRIMARY_STREET = PRIMARY;
I set the index = 200 to force LASTMOVE to be FALSE because I only want to perform the DO WHILE for 7 fields (see bolded tags below) and then end it. Can you help me understand inref? It is not working the way I have it coded. Does inref point to a tag or the data within the tag? Any help will be appreciated. I have also included my input XML. Thanks, Lisa
<CompleteOdlJob>
<ApplicationArea>
<Sender>
<Component>ODLCompletion Flow</Component>
<Confirmation>OnError</Confirmation>
<AuthorizationId>Identify the User of Origin</AuthorizationId>
</Sender>
<CreationDateTime>CCYY-MM-DDThh:mm:ss</CreationDateTime>
<BODId>GUID - Globally Unique Identifier</BODId>
</ApplicationArea>
<DataArea>
<ODL_LIGHT_INFORMATION>
<ODL_PRIMARY_HOUSE_NUMBER>1234</ODL_PRIMARY_HOUSE_NUMBER>
<ODL_PRIMARY_STREET/>
<ODL_STREET_NAME1>main</ODL_STREET_NAME1>
<ODL_P_STREET_TYPE1>st</ODL_P_STREET_TYPE1>
<ODL_P_DIR1>s</ODL_P_DIR1>
<ODL_ALPHA_CIVIC>a</ODL_ALPHA_CIVIC>
<ODL_P_ARTICLE1>r</ODL_P_ARTICLE1>
<ODL_APPT_NO>33</ODL_APPT_NO>
<ODL_COMMERCIAL_LOCAL>n</ODL_COMMERCIAL_LOCAL>
<ODL_P_ARTICLE2>3</ODL_P_ARTICLE2>
<ODL_STREET_NAME2>ttttttt</ODL_STREET_NAME2>
<ODL_P_STREET_TYPE2>st</ODL_P_STREET_TYPE2>
<ODL_P_DIR2>s</ODL_P_DIR2>
<ODL_PRIMARY_CITY>LINTHICU1</ODL_PRIMARY_CITY>
<ODL_PRIMARY_ZIP>21229</ODL_PRIMARY_ZIP>
<ODL_CHARGEABLE_FLAG/>
<ODL_DEFECTIVE_FLAG/>
<ODL_SECONDARY_STREET/>
<ODL_SECONDARY_HOUSE_NUMBER/>
<ODL_DIAGNOSIS_CODE_1>PANEL</ODL_DIAGNOSIS_CODE_1>
<ODL_DIAGNOSIS_CODE_2>BULB</ODL_DIAGNOSIS_CODE_2>
<ODL_DIAGNOSIS_CODE_3>POLE</ODL_DIAGNOSIS_CODE_3>
<ODL_DIAGNOSIS_CODE_4>ARM</ODL_DIAGNOSIS_CODE_4>
<ODL_DIAGNOSIS_CODE_5>GLOBE</ODL_DIAGNOSIS_CODE_5>
<ODL_KY_LAMP_GRID_NO>4567</ODL_KY_LAMP_GRID_NO>
<ODL_STLT_FLAG/>
<ODL_PAL_FLAG/>
<ODL_KY_LAMP_NO1>2345</ODL_KY_LAMP_NO1>
<ODL_KY_LAMP_NO2/>
<ODL_KY_LAMP_NO3/>
<ODL_KY_LAMP_NO4/>
<ODL_NO_MECH_NUMBER_7/>
<ODL_NO_MECH_NUMBER_9/>
<ODL_OWNERSHIP_ID>STHIGH</ODL_OWNERSHIP_ID>
<ODL_MUNICIPALITY/>
<ODL_NO_POLE/>
<ODL_REMARKS/>
<ODL_SR_NUMBER>SR:9999999999</ODL_SR_NUMBER>
<ODL_VANDALIZED_FLAG/>
</ODL_LIGHT_INFORMATION>
<ODL_RELATED_INFORMATION>
<ODL_NO_MECH_NUMBER_1/>
<ODL_NO_MECH_NUMBER_2/>
<ODL_NO_MECH_NUMBER_3/>
<ODL_NO_MECH_NUMBER_4/>
<ODL_NO_MECH_NUMBER_5/>
<ODL_NO_MECH_NUMBER_6/>
<ODL_NO_MECH_NUMBER_8/>
<ODL_ADDL_MECH_NUMBER_1/>
<ODL_ADDL_MECH_NUMBER_2/>
<ODL_FUSE_LOC>RELAY</ODL_FUSE_LOC>
<ODL_LOC_LOOSE_CONN>BASE</ODL_LOC_LOOSE_CONN>
<ODL_PHOTOCELL_LOC>TIMER</ODL_PHOTOCELL_LOC>
<ODL_BGE_NOT_MAINTAIN>ROMEX</ODL_BGE_NOT_MAINTAIN>
<ODL_BURNING_OK>SERVICED</ODL_BURNING_OK>
</ODL_RELATED_INFORMATION>
<ODL_OTHER_INFORMATION>
<ODL_C_ORDER_NUMBER/>
<ODL_D_ORDER_NUMBER/>
<ODL_PICKUP_FLAG/>
<ODL_ROUTED_FLAG/>
<ODL_DT_ISSUED/>
<ODL_CIS_BILLINGNAME/>
<ODL_DISTRICT_CODE>BELAIR</ODL_DISTRICT_CODE>
<ODL_NO_CIS_TICKET_NUMBER>4567</ODL_NO_CIS_TICKET_NUMBER>
<ODL_LIGHTS_AFFECTED/>
<ODL_PRIMARY_PHONE_NUMBER/>
<ODL_PRIORITY_CODE>60</ODL_PRIORITY_CODE>
<ODL_REAR_FLAG/>
<ODL_NO_WMS_NUMBER/>
<ODL_REPAIRED_BY_1/>
<ODL_ARRIVAL_TIME_1/>
<ODL_DEPART_TIME_1/>
<ODL_REPORT_NUMBER>45678</ODL_REPORT_NUMBER>
<ODL_DT_COMPLETE/>
<ODL_DT_ISSUED/>
<ODL_LIGHT_TYPE>PAL</ODL_LIGHT_TYPE>
<ODL_PROXIMITY>REAR</ODL_PROXIMITY>
<ODL_ORIGIN>GUI</ODL_ORIGIN>
<ODL_DESC_LOC>This is a test to see if the information gets split correctly into two fields </ODL_DESC_LOC>
</ODL_OTHER_INFORMATION>
</DataArea>
</CompleteOdlJob> |
|
| Back to top |
|
|
| kirani |
| Posted: Tue Jun 17, 2003 9:09 am Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Looking at your input message, I think your code should be changed to following:
| Code: |
DECLARE COUNT INT;
SET COUNT = 1;
DECLARE inref REFERENCE to InputRoot.XML.CompleteOdlJob.DataArea.ODL_LIGHT_INFORMATION.ODL_STREET_NAME1;
WHILE (COUNT <= 7) DO
if (inref <> ' ' AND inref IS NOT NULL) then
if (index = 1) then
SET PRIMARY = inref || ' ';
else
SET PRIMARY = PRIMARY || ' ' || inref;
end if;
SET index = index + 1;
end if;
move inref NEXTSIBLING;
SET COUNT = COUNT + 1;
END WHILE;
SET OutputRoot.XML.CompleteOdlJob.DataArea.ODL_LIGHT_INFORMATION.ODL_PRIMARY_STREET = PRIMARY;
|
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| Chuck831 |
| Posted: Wed Jun 18, 2003 6:55 am Post subject: Concatening multiple fields based on content |
|
|
Apprentice
Joined: 23 Dec 2002
Posts: 28
|
Lisa - the following code will easier to use. Additionally it is much easier to maintain. I have included the source XML and output XML. Look no IF statements.
If you have any questions give me a call.
| Code: |
Declare StartPos INTEGER;
SET OutputRoot.XML.DATA.PRIMARY = InputRoot.XML.DATA.STREET1 || ' ' || InputRoot.XML.DATA.TYPE
|| ' ' || InputRoot.XML.DATA.DIR1 || ' ' || InputRoot.XML.DATA.CIVIC
|| ' ' || InputRoot.XML.DATA.ARTICLE1 || ' ' || InputRoot.XML.DATA.APPT;
SET StartPos = POSITION(' ' IN OutputRoot.XML.DATA.PRIMARY);
WHILE StartPos > 0 DO
SET OutputRoot.XML.DATA.PRIMARY = OVERLAY(OutputRoot.XML.DATA.PRIMARY PLACING ' ' FROM StartPos FOR 2);
SET StartPos = POSITION(' ' IN OutputRoot.XML.DATA.PRIMARY);
END WHILE;
SET OutputRoot.XML.DATA.PRIMARY = RTRIM(OutputRoot.XML.DATA.PRIMARY);
|
| Code: |
Input 1 XML:
<DATA>
<STREET1>CHARLES</STREET1>
<TYPE>ST</TYPE>
<DIR1>NW</DIR1>
<CIVIC></CIVIC>
<ARTICLE1>SOUTH</ARTICLE1>
<APPT></APPT>
</DATA>
Output 1 XML:
<DATA>
<STREET1>CHARLES</STREET1>
<TYPE>ST</TYPE>
<DIR1>NW</DIR1>
<CIVIC></CIVIC>
<ARTICLE1>SOUTH</ARTICLE1>
<APPT></APPT>
<PRIMARY>CHARLES ST NW SOUTH</PRIMARY>
</DATA>
Input 2 XML:
<DATA>
<STREET1>CHARLES</STREET1>
<TYPE>ST</TYPE>
<DIR1>NW</DIR1>
<CIVIC>123</CIVIC>
<ARTICLE1>SOUTH</ARTICLE1>
<APPT></APPT>
</DATA>
Output 2 XML:
<DATA>
<STREET1>CHARLES</STREET1>
<TYPE>ST</TYPE>
<DIR1>NW</DIR1>
<CIVIC>123</CIVIC>
<ARTICLE1>SOUTH</ARTICLE1>
<APPT></APPT>
<PRIMARY>CHARLES ST NW 123 SOUTH</PRIMARY>
</DATA>
|
|
|
| Back to top |
|
|
|
|
