MQSeries.net :: View topic - map repeating elements with ESQL SELECT statement

MQSeries.net

Tech Exchange

Education

Certifications

Library

Info Center

SupportPacs

FAQÂ Â

Usergroups

RSS Feed - WebSphere MQ Support

RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » map repeating elements with ESQL SELECT statement

map repeating elements with ESQL SELECT statement

« View previous topic :: View next topic »

Author

Message

j.f.sorge

Posted: Fri Nov 28, 2008 2:27 am Post subject: map repeating elements with ESQL SELECT statement

Master

Joined: 27 Feb 2008
Posts: 218

I want to map repeating elements in XML structure into repeating elements in COBOL / CWF structure using ESQL SELECT. When I try to use the following lines

Code:

...
,I.tub:identifikation[].tub:ausfIdTp AS AUSF_ID[].AUSF_ID_TP
,I.tub:identifikation[].tub:ausfId AS AUSF_ID[].AUSF_ID
...

deployment fails because I used the [] in the middle of the line where I should use it only at the end.

Is there any other solution than mapping "by hand" with references looping over each of these elements?

Thanks in advance!

mgk

Posted: Fri Nov 28, 2008 4:45 am Post subject:

Padawan

Joined: 31 Jul 2003
Posts: 1642

Hi.

Can you paste your whole ESQL Select and an example Input message and Expected output message so I can understand what you are trying to achieve please?

Cheers,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions.

kimbert

Posted: Fri Nov 28, 2008 4:48 am Post subject:

Jedi Council

Joined: 29 Jul 2003
Posts: 5542
Location: Southampton

I'm almost certain that you can do this elegantly. It's just a case of working out how.

ESQL SELECT: http://publib.boulder.ibm.com/infocenter/wmbhelp/v6r1m0/topic/com.ibm.etools.mft.doc/ac67150_.htm

Or XPath: http://publib.boulder.ibm.com/infocenter/wmbhelp/v6r1m0/topic/com.ibm.etools.mft.doc/ak65010_.htm

j.f.sorge

Posted: Fri Nov 28, 2008 5:00 am Post subject: the SELECT statement

Master

Joined: 27 Feb 2008
Posts: 218

I am using the following statement

Code:

SET refOutputBody
= (SELECT H.tub:Sender          AS HDR_SENDER
   ,H.tub:Receiver          AS HDR_RECEIVER
   ,H.tub:SenderProzessId    AS HDR_S_PROZESSID
   ,H.tub:ReceiverProzessId AS HDR_R_PROZESSID
   ,COALESCE(H.tub:TS, CURRENT_TIMESTAMP) AS HDR_TS
   ,H.tub:MessageType       AS HDR_MSGTYP
   ,H.tub:Version          AS HDR_VERS
   ,H.tub:MessageTypeVersion AS HDR_MSGTYP_VERS
   ,COALESCE(H.tub:ErrorCode, '') AS HDR_ERR_CODE
   ,COALESCE(H.tub:ErrorText, '') AS HDR_ERR_TXT
   ,COALESCE(H.tub:FreeArea, '') AS HDR_FREEAREA
   ,I.tub:verarbTP       AS VERARB_TP
   ,I.tub:primId       AS PRIM_ID
   ,COALESCE(I.tub:srefPrimId, '')    AS SREF_PRIM_ID
   ,COALESCE(I.tub:stornoTxt, '')    AS STORNO_TXT
-- AUSF_ID    will be mapped afterwards
   ,COALESCE(I.tub:hknftSysTs, CURRENT_TIMESTAMP) AS HKNFT_SYS_TS
   ,COALESCE(I.tub:hknftSysErf, '') AS HKNFT_SYS_ERF
   ,I.tub:abschlussTg       AS ABSCHLUSS_TG
   ,I.tub:abschlussZeit    AS ABSCHLUSS_ZEIT
   ,I.tub:schlussTg       AS SCHLUSS_TG
   ,I.tub:valuta       AS VALUTA
   ,I.tub:handWhrg       AS HAND_WHRG
   ,I.tub:devkHandEurKez AS DEVK_HAND_EUR_KEZ
   ,I.tub:devkHandEur AS DEVK_HAND_EUR
   ,COALESCE(I.tub:abreWhrg, '')    AS ABRE_WHRG
   ,I.tub:devkAbreEurKez AS DEVK_ABRE_EUR_KEZ
   ,I.tub:devkAbreEur AS DEVK_ABRE_EUR
   ,I.tub:geschArt       AS GESCH_ART
   ,I.tub:wpIdTp       AS WP_ID_TP
   ,I.tub:wpId          AS WP_ID
   ,I.tub:handPlatzIdTp AS HAND_PLATZ_ID_TP
   ,I.tub:handPlatzId    AS HAND_PLATZ_ID
   ,I.tub:ausbKez       AS AUSB_KEZ
   ,COALESCE(I.tub:abrNettoKez, '') AS ABR_NETTO_KEZ
   ,I.tub:ausfKurs    AS AUSF_KURS
   ,COALESCE(I.tub:ausfKursNotierung, '') AS AUSF_KURS_NOTIER
-- KURS    will be mapped afterwards
   ,COALESCE(I.tub:festpreisKez, '')    AS FESTPREIS_KEZ
   ,I.tub:nomktKursKez    AS NOMKT_KURS_KEZ
   ,I.tub:ausfBtrg AS AUSF_BTRG
   ,COALESCE(I.tub:ausfBtrgTp, '')    AS AUSF_BTRG_TP
   ,COALESCE(I.tub:ausfBtrgWhrg, '') AS AUSF_BTRG_WHRG
   ,COALESCE(I.tub:verwCode, '')    AS VERW_CODE
   ,COALESCE(I.tub:lagerland, '')    AS LAGER_LAND
   ,COALESCE(I.tub:lgrstlDepot, '') AS LGRSTL_DEPOT
   ,COALESCE(I.tub:vrwaTp, '')       AS VRWA_TP
   ,I.tub:depotNr       AS DEPOT_NR
   ,I.tub:abrKtoNr    AS ABR_KTO_NR
   ,COALESCE(I.tub:p9InfoTp, '')    AS P9_INFO_TP
   ,COALESCE(I.tub:auftGbrTp, '')    AS AUFT_GEBER_TP
   ,COALESCE(I.tub:AbweichenderAuftraggeber.tub:abwAuftGbrIdTp, '') AS ABW_AUFT_GBR_ID_TP
   ,COALESCE(I.tub:AbweichenderAuftraggeber.tub:abwAuftGbrId, '')    AS ABW_AUFT_GBR_ID
   ,COALESCE(I.tub:kundeKunden.tub:kundeKundenIdTp, '') AS KD_KD_ID_TP
   ,COALESCE(I.tub:kundeKunden.tub:kundeKundenId, '')    AS KD_KD_ID
   ,COALESCE(I.tub:kundeKundenAuftGbrTp, '')       AS KD_KD_AUFT_GBR_TP
   ,COALESCE(I.tub:Zwischenkommissionaer.tub:zwKommIdTp, '') AS ZW_KOMM_ID_TP
   ,COALESCE(I.tub:Zwischenkommissionaer.tub:zwKommId, '')    AS ZW_KOMM_ID
   ,I.tub:abwcklGeschTp AS ABWCKL_GESCH_TP
   ,COALESCE(I.tub:kontrahent.tub:kontrIdTp, '')    AS KONTR_ID_TP
   ,COALESCE(I.tub:kontrahent.tub:kontrId, '')       AS KONTR_ID
   ,COALESCE(I.tub:gegenparteiTp, '') AS GEGENPARTEI_TP
   ,COALESCE(I.tub:auftragsArtTp, '') AS AUFTRAGS_ART_TP
   ,COALESCE(I.tub:kursLimitTp, '') AS KURS_LIMIT_TP
   ,I.tub:emissionKez AS EMISSION_KEZ
   ,COALESCE(I.tub:mfdVerPhaseTp, '') AS MFD_VER_PHASE_TP
   ,COALESCE(I.tub:kdAbrTxt, '')    AS KD_ABR_TXT
   ,COALESCE(I.tub:intText, '')    AS INT_TEXT
   ,COALESCE(I.tub:Stueckzinstage.tub:stkZinsTg, '') AS STK_ZINS_TG
   ,COALESCE(I.tub:Stueckzinstage.tub:stkZinsTgVz, '') AS STK_ZINS_TG_VZ
   ,I.tub:Stueckzinsbetrag.tub:stkZinsBtrg AS STK_ZINS_BTRG
   ,COALESCE(I.tub:Stueckzinsbetrag.tub:stkZinsBtrgVz, '')             AS STK_ZINS_BTRG_VZ
   ,I.tub:stkZinsValAbw AS STK_ZINS_VAL_ABW
-- ABRPOS    will be mapped afterwards
-- AUSF_ZUS will be mapped afterwards
-- ,I.tub:FOR_FUTURE_USE AS LEIHE_ART
-- ,I.tub:FOR_FUTURE_USE AS LEIHE_VON
-- ,I.tub:FOR_FUTURE_USE AS LEIHE_BIS
FROM InputBody          AS I
      ,InputBody.tub:Header AS H
);

mgk

Posted: Fri Nov 28, 2008 5:35 am Post subject:

Padawan

Joined: 31 Jul 2003
Posts: 1642

Sorry, I don't see the lines from your first message in your second.

Also, the [] can only be used within a path if you specify an index, such as [1]. So if you need to iterate over a sub-part of the message you may want to look as doing a nested SELECT for those parts

And it would still be nice to see example Input and expected Output messages

Regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions.

j.f.sorge

Posted: Fri Nov 28, 2008 5:50 am Post subject: the statement I wanted to use

Master

Joined: 27 Feb 2008
Posts: 218

This is the statement I wanted to use

Code:

SET refOutputBody
= (SELECT H.tub:Sender          AS HDR_SENDER
      ,H.tub:Receiver          AS HDR_RECEIVER
      ,H.tub:SenderProzessId    AS HDR_S_PROZESSID
      ,H.tub:ReceiverProzessId AS HDR_R_PROZESSID
      ,COALESCE(H.tub:TS, CURRENT_TIMESTAMP) AS HDR_TS
      ,H.tub:MessageType       AS HDR_MSGTYP
      ,H.tub:Version          AS HDR_VERS
      ,H.tub:MessageTypeVersion AS HDR_MSGTYP_VERS
      ,COALESCE(H.tub:ErrorCode, '') AS HDR_ERR_CODE
      ,COALESCE(H.tub:ErrorText, '') AS HDR_ERR_TXT
      ,COALESCE(H.tub:FreeArea, '') AS HDR_FREEAREA
      ,I.tub:verarbTP       AS VERARB_TP
      ,I.tub:primId       AS PRIM_ID
      ,COALESCE(I.tub:srefPrimId, '')    AS SREF_PRIM_ID
      ,COALESCE(I.tub:stornoTxt, '')    AS STORNO_TXT
      ,I.tub:identifikation[].tub:ausfIdTp AS AUSF_ID[].AUSF_ID_TP
      ,I.tub:identifikation[].tub:ausfId    AS AUSF_ID[].AUSF_ID
      ,COALESCE(I.tub:hknftSysTs, CURRENT_TIMESTAMP) AS HKNFT_SYS_TS
      ,COALESCE(I.tub:hknftSysErf, '') AS HKNFT_SYS_ERF
      ,I.tub:abschlussTg       AS ABSCHLUSS_TG
      ,I.tub:abschlussZeit    AS ABSCHLUSS_ZEIT
      ,I.tub:schlussTg       AS SCHLUSS_TG
      ,I.tub:valuta       AS VALUTA
      ,I.tub:handWhrg       AS HAND_WHRG
      ,I.tub:devkHandEurKez AS DEVK_HAND_EUR_KEZ
      ,I.tub:devkHandEur AS DEVK_HAND_EUR
      ,COALESCE(I.tub:abreWhrg, '')    AS ABRE_WHRG
      ,I.tub:devkAbreEurKez AS DEVK_ABRE_EUR_KEZ
      ,I.tub:devkAbreEur AS DEVK_ABRE_EUR
      ,I.tub:geschArt       AS GESCH_ART
      ,I.tub:wpIdTp       AS WP_ID_TP
      ,I.tub:wpId          AS WP_ID
      ,I.tub:handPlatzIdTp AS HAND_PLATZ_ID_TP
      ,I.tub:handPlatzId    AS HAND_PLATZ_ID
      ,I.tub:ausbKez       AS AUSB_KEZ
      ,COALESCE(I.tub:abrNettoKez, '') AS ABR_NETTO_KEZ
      ,I.tub:ausfKurs    AS AUSF_KURS
      ,COALESCE(I.tub:ausfKursNotierung, '') AS AUSF_KURS_NOTIER
      ,I.tub:Kurse[].tub:kursTp       AS KURS[].KURS_TP
      ,I.tub:Kurse[].tub:kurs          AS KURS[].KURS
      ,I.tub:Kurse[].tub:kursNotierung AS KURS[].KURS_NOTIERUNG
      ,COALESCE(I.tub:festpreisKez, '')    AS FESTPREIS_KEZ
      ,I.tub:nomktKursKez    AS NOMKT_KURS_KEZ
      ,I.tub:ausfBtrg AS AUSF_BTRG
      ,COALESCE(I.tub:ausfBtrgTp, '')    AS AUSF_BTRG_TP
      ,COALESCE(I.tub:ausfBtrgWhrg, '') AS AUSF_BTRG_WHRG
      ,COALESCE(I.tub:verwCode, '')    AS VERW_CODE
      ,COALESCE(I.tub:lagerland, '')    AS LAGER_LAND
      ,COALESCE(I.tub:lgrstlDepot, '') AS LGRSTL_DEPOT
      ,COALESCE(I.tub:vrwaTp, '')       AS VRWA_TP
      ,I.tub:depotNr       AS DEPOT_NR
      ,I.tub:abrKtoNr    AS ABR_KTO_NR
      ,COALESCE(I.tub:p9InfoTp, '')    AS P9_INFO_TP
      ,COALESCE(I.tub:auftGbrTp, '')    AS AUFT_GEBER_TP
      ,COALESCE(I.tub:AbweichenderAuftraggeber.tub:abwAuftGbrIdTp, '') AS ABW_AUFT_GBR_ID_TP
      ,COALESCE(I.tub:AbweichenderAuftraggeber.tub:abwAuftGbrId, '')    AS ABW_AUFT_GBR_ID
      ,COALESCE(I.tub:kundeKunden.tub:kundeKundenIdTp, '') AS KD_KD_ID_TP
      ,COALESCE(I.tub:kundeKunden.tub:kundeKundenId, '')    AS KD_KD_ID
      ,COALESCE(I.tub:kundeKundenAuftGbrTp, '')       AS KD_KD_AUFT_GBR_TP
      ,COALESCE(I.tub:Zwischenkommissionaer.tub:zwKommIdTp, '') AS ZW_KOMM_ID_TP
      ,COALESCE(I.tub:Zwischenkommissionaer.tub:zwKommId, '')    AS ZW_KOMM_ID
      ,I.tub:abwcklGeschTp AS ABWCKL_GESCH_TP
      ,COALESCE(I.tub:kontrahent.tub:kontrIdTp, '')    AS KONTR_ID_TP
      ,COALESCE(I.tub:kontrahent.tub:kontrId, '')       AS KONTR_ID
      ,COALESCE(I.tub:gegenparteiTp, '') AS GEGENPARTEI_TP
      ,COALESCE(I.tub:auftragsArtTp, '') AS AUFTRAGS_ART_TP
      ,COALESCE(I.tub:kursLimitTp, '') AS KURS_LIMIT_TP
      ,I.tub:emissionKez AS EMISSION_KEZ
      ,COALESCE(I.tub:mfdVerPhaseTp, '') AS MFD_VER_PHASE_TP
      ,COALESCE(I.tub:kdAbrTxt, '')    AS KD_ABR_TXT
      ,COALESCE(I.tub:intText, '')    AS INT_TEXT
      ,COALESCE(I.tub:Stueckzinstage.tub:stkZinsTg, '') AS STK_ZINS_TG
      ,COALESCE(I.tub:Stueckzinstage.tub:stkZinsTgVz, '') AS STK_ZINS_TG_VZ
      ,I.tub:Stueckzinsbetrag.tub:stkZinsBtrg AS STK_ZINS_BTRG
      ,COALESCE(I.tub:Stueckzinsbetrag.tub:stkZinsBtrgVz, '')             AS STK_ZINS_BTRG_VZ
      ,I.tub:stkZinsValAbw AS STK_ZINS_VAL_ABW
      ,I.tub:Abrechnungsposten[].tub:abrPosTp       AS ABRPOS[].ABRPOS_TP
      ,I.tub:Abrechnungsposten[].tub:abrPosBtrg    AS ABRPOS[].ABRPOS_BTRG
      ,I.tub:Abrechnungsposten[].tub:abrPosBtrgVz    AS ABRPOS[].ABRPOS_BTRG_VZ
      ,I.tub:Abrechnungsposten[].tub:abrPosBtrgWhrg AS ABRPOS[].ABRPOS_BTRG_WRG
      ,I.tub:Abrechnungsposten[].tub:abrPosPrz    AS ABRPOS[].ABRPOS_PRZ
      ,I.tub:Abrechnungsposten[].tub:abrPosPrzBasis AS ABRPOS[].ABRPOS_PRZ_BAS
      ,I.tub:Zusatz[].tub:zusatzTp AS AUSF_ZUS[].AUSF_ZUS_TP
      ,I.tub:Zusatz[].tub:ZusatzId AS AUSF_ZUS[].AUSF_ZUS_ID
--    ,I.tub:FOR_FUTURE_USE AS LEIHE_ART
--    ,I.tub:FOR_FUTURE_USE AS LEIHE_VON
--    ,I.tub:FOR_FUTURE_USE AS LEIHE_BIS
FROM InputBody          AS I
      ,InputBody.tub:Header AS H
);

Inputfile

Code:

<?xml version="1.0"?>
<NS1:ausfuehrung xmlns:NS1="http://www.example.org" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xml="http://www.w3.org/XML/1998/namespace">
<NS1:Header>
   <NS1:Sender>SEND</NS1:Sender>
   <NS1:Receiver>RECV</NS1:Receiver>
   <NS1:SenderProzessId>PF52060</NS1:SenderProzessId>
   <NS1:TS>2008-08-05T18:08:00.256099</NS1:TS>
   <NS1:MessageType>AUSFUEHRUNG</NS1:MessageType>
   <NS1:Version>01.01.01</NS1:Version>
   <NS1:MessageTypeVersion>01.01.01</NS1:MessageTypeVersion>
</NS1:Header>
<NS1:verarbTP>NEU</NS1:verarbTP>
<NS1:primId>EMCF2007-08-17000066391DEL</NS1:primId>
<NS1:hknftSysTs>2008-08-05T18:08:01.256099</NS1:hknftSysTs>
<NS1:hknftSysErf>EMCFCHIX</NS1:hknftSysErf>
<NS1:abschlussTg>2007-08-17</NS1:abschlussTg>
<NS1:abschlussZeit>18:08:01</NS1:abschlussZeit>
<NS1:schlussTg>2007-08-17</NS1:schlussTg>
<NS1:valuta>2007-08-22</NS1:valuta>
<NS1:handWhrg>GBP</NS1:handWhrg>
<NS1:devkHandEurKez>N</NS1:devkHandEurKez>
<NS1:devkAbreEurKez>N</NS1:devkAbreEurKez>
<NS1:geschArt>KAUF</NS1:geschArt>
<NS1:wpIdTp>ISIN</NS1:wpIdTp>
<NS1:wpId>GB00B16BCD56</NS1:wpId>
<NS1:handPlatzIdTp>INT</NS1:handPlatzIdTp>
<NS1:handPlatzId>EMCF</NS1:handPlatzId>
<NS1:ausbKez>J</NS1:ausbKez>
<NS1:ausfKurs>99.193103</NS1:ausfKurs>
<NS1:ausfKursNotierung>STK</NS1:ausfKursNotierung>
<NS1:nomktKursKez>N</NS1:nomktKursKez>
<NS1:ausfBtrg>145</NS1:ausfBtrg>
<NS1:ausfBtrgTp>STK</NS1:ausfBtrgTp>
<NS1:lagerland>GB</NS1:lagerland>
<NS1:lgrstlDepot>GB-Depot</NS1:lgrstlDepot>
<NS1:vrwaTp>WR</NS1:vrwaTp>
<NS1:depotNr>1234567890</NS1:depotNr>
<NS1:abwcklGeschTp>KEIN</NS1:abwcklGeschTp>
<NS1:kontrahent>
   <NS1:kontrIdTp>GEOS</NS1:kontrIdTp>
   <NS1:kontrId>K983832</NS1:kontrId>
</NS1:kontrahent>
<NS1:emissionKez>N</NS1:emissionKez>
<NS1:mfdVerPhaseTp>NZUV</NS1:mfdVerPhaseTp>
<NS1:Abrechnungsposten>
   <NS1:abrPosTp>FRSP</NS1:abrPosTp>
   <NS1:abrPosBtrg>0</NS1:abrPosBtrg>
</NS1:Abrechnungsposten>
<NS1:Abrechnungsposten>
   <NS1:abrPosTp>GESM</NS1:abrPosTp>
   <NS1:abrPosBtrg>173</NS1:abrPosBtrg>
   <NS1:abrPosBtrgVz>+</NS1:abrPosBtrgVz>
   <NS1:abrPosBtrgWhrg>GBP</NS1:abrPosBtrgWhrg>
</NS1:Abrechnungsposten>
<NS1:Zusatz>
   <NS1:zusatzTp>STLMTNM</NS1:zusatzTp>
   <NS1:zusatzId>CHIXGB</NS1:zusatzId>
</NS1:Zusatz>
</NS1:ausfuehrung>

Outputfile

Code:

SENDRECVPF52060 2008-08-05-18.08.00.256099AUSFUEHRUNG 01.01.0101.01.01 NEU EMCF2007-08-17000066391DEL 20080805180801EMCFCHIX 200708171808012007081720070822GBPN N KAUF ISIN GB00B16BCD56 INT EMCF J 000000000099193103STK N000000000001450000STK GBGB-Depot WR 1234567890 KEIN GEOS K983832 NNZUV 00000 FRSP 000000000000000000 GESM 000000000001730000+GBP STLMTNM CHIXGB

Display posts from previous:

Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » map repeating elements with ESQL SELECT statement

Jump to:

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Protected by Anti-Spam ACP