| Author |
Message
|
| deepak.gowder |
 Posted: Wed Sep 19, 2012 8:07 am Post subject: Convert base64 in ESQL to extract underlying XML elments |
 |
|
Novice
Joined: 19 Sep 2012
Posts: 20
|
Hi,
I have gone thru some topics related to this, but I'm still not sure how to get it done. Apologies!
I'm reading a message with one of the elements being a base64Binary and that has an XML message. I wanted to extract few values using ESQL and put it into a queue. Is there a way to do it in ESQL? Even if not how can I achieve this?
Thanks! Deepak |
|
| Back to top |
|
 |
| lancelotlinc |
| Posted: Wed Sep 19, 2012 8:09 am Post subject: Re: Convert base64 in ESQL to extract underlying XML elments |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| deepak.gowder wrote: |
| Is there a way to do it in ESQL? |
Yes. There are a few ways which you can know this information.
1. Coach
2. Mentor
3. InfoCentre
4. Training
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| mgk |
| Posted: Wed Sep 19, 2012 8:17 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
| Quote: |
| Is there a way to do it in ESQL |
Yes, as of 7.0.0.2, ESQL has had BASE64ENCODE and BASE64DECODE functions which you can use. You can find the documentation for these function in the infocenter.
Kind regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| deepak.gowder |
| Posted: Wed Sep 19, 2012 9:43 am Post subject: |
|
|
Novice
Joined: 19 Sep 2012
Posts: 20
|
| mgk wrote: |
| Quote: |
| Is there a way to do it in ESQL |
Yes, as of 7.0.0.2, ESQL has had BASE64ENCODE and BASE64DECODE functions which you can use. You can find the documentation for these function in the infocenter.
Kind regards, |
I'm still running on 7.0.0. A sample code could help?
Cheers! |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Wed Sep 19, 2012 10:40 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| deepak.gowder wrote: |
| mgk wrote: |
| Quote: |
| Is there a way to do it in ESQL |
Yes, as of 7.0.0.2, ESQL has had BASE64ENCODE and BASE64DECODE functions which you can use. You can find the documentation for these function in the infocenter.
Kind regards, |
I'm still running on 7.0.0. A sample code could help?
Cheers! |
Message Broker version numbers have four digits. Update your toolkit and runtime to 7.0.0.4.
"A sample code could help?" Sample code? Did you read the Infocentre articles?
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| deepak.gowder |
| Posted: Wed Sep 19, 2012 12:45 pm Post subject: |
|
|
Novice
Joined: 19 Sep 2012
Posts: 20
|
| Quote: |
| "A sample code could help?" Sample code? Did you read the Infocentre articles? |
The Infocenter helped. I confused with picking up things from different posts... But it was straight easy.
Here's the ex from Infocenter:
Examples
The base64 encoding of a BLOB source string and subsequent decoding back to BLOB is shown by the following example:
DECLARE original BLOB X'48656c6c6f';
DECLARE encoded CHARACTER BASE64ENCODE(original);
DECLARE decoded BLOB BASE64DECODE(encoded);
The base64 encoding of a CHARACTER source string that is first automatically converted to UTF-8 and later decoded is shown by the following example:
DECLARE original CHARACTER 'Hello World!';
DECLARE encoded CHARACTER BASE64ENCODE(original);
DECLARE decoded BLOB BASE64DECODE(encoded);
DECLARE decoded2 CHARACTER CAST(decoded AS CHARACTER CCSID 1208); |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Thu Sep 20, 2012 4:42 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
|
| Back to top |
|
|
| deepak.gowder |
| Posted: Thu Sep 20, 2012 7:25 am Post subject: |
|
|
Novice
Joined: 19 Sep 2012
Posts: 20
|
Need more help!!
I have this XML coming in as base64 along with other elements.
TXLife Version="2.23.00" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://ACORD.org/Standards/Life/2">
<TXLifeRequest id="TXLifeRequest_a1fc84d9-1fc6-4479-9cd6-cf34541f0b16" PrimaryObjectID="Holding_fd8e853f-2039-4ac3-9fb3-6e043cd031f9">
<TransRefGUID>beafac2e-c44e-4107-879b-566c05ab5383</TransRefGUID>
<TransType tc="103">OLI_TRANS_NBSUB</TransType>
<TransExeDate>2012-09-14</TransExeDate>
<TransExeTime>08:53:02.1596290-06:00</TransExeTime>
<OLifE>
<Holding id="Holding_fd8e853f-2039-4ac3-9fb3-6e043cd031f9">
<HoldingTypeCode tc="2">OLI_HOLDTYPE_POLICY</HoldingTypeCode>
<Policy CarrierPartyID="Party_23ccb721-ee84-4920-830d-7c87ce95ba04">
<PolNumber/>
<ProductType tc="2">OLI_PRODTYPE_TERM</ProductType>
<CarrierCode>AMA</CarrierCode>
<Jurisdiction tc="1">OLI_USA_AL</Jurisdiction>
<Life>
<Coverage id="Coverage_badb240f-e9eb-44e4-b9e2-0047fc5bf2fc">
<LifeCovTypeCode tc="6">OLI_COVTYPE_TERMLEVEL</LifeCovTypeCode>
<IndicatorCode tc="1">OLI_COVIND_BASE</IndicatorCode>
<LifeParticipant PartyID="Party_78b7ae67-b695-4a99-8371-21cd9d446dd7"/>
</Coverage>
</Life>
<ApplicationInfo>
<ApplicationJurisdiction tc="1">LE_ASUSA_FEL</ApplicationJurisdiction>
</ApplicationInfo>
</Policy>
</Holding>
<Party id="Party_78b7ae67-b695-4a99-8371-21cd9d446dd7">
<PartyTypeCode tc="1">OLI_PT_PERSON</PartyTypeCode>
<Address id="Address_604056fd-2c77-4568-9b1b-ce26fd38344b">
<AddressTypeCode tc="1">LEI_ADTYPE_HOME</AddressTypeCode>
</Address>
</Party>
<Party id="Party_23ccb721-ee84-4920-830d-7c87ce95ba04">
<PartyTypeCode tc="2">LWI_PW_OAR</PartyTypeCode>
<FullName>Custom</FullName>
<Organization/>
<Carrier>
<CarrierCode>WERA</CarrierCode>
</Carrier>
</Party>
</OLifE>
</TXLifeRequest>
</TXLife>
And here's the code that I have to read this as Char -
DECLARE DecodedAcord BLOB base64Decode(InputRoot.XMLNSC.ns17:submit103.acord103);
DECLARE Decoded2Acord CHARACTER CAST(DecodedAcord AS CHARACTER CCSID 1208);
I can't get to extract the elements that I need from this XML, ie. from Decoded2Acord field. While I'm trying to find out a way to do this, your help/ suggestions will be of much help.
Thanks!
|
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Thu Sep 20, 2012 7:28 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
Can you post your code within [c o d e] tags?
Parse the blob into XML. Blob is a blob and a blob is not a message tree.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| deepak.gowder |
| Posted: Thu Sep 20, 2012 7:37 am Post subject: |
|
|
Novice
Joined: 19 Sep 2012
Posts: 20
|
| lancelotlinc wrote: |
Can you post your code within [c o d e] tags?
Parse the blob into XML. Blob is a blob and a blob is not a message tree. |
| Code: |
DECLARE DecodedAcord BLOB base64Decode(InputRoot.XMLNSC.ns17:submit103.acord103);
DECLARE Decoded2Acord CHARACTER CAST(DecodedAcord AS CHARACTER CCSID 1208);
---
---
CREATE PROCEDURE base64Decode(IN source CHARACTER)
RETURNS BLOB
LANGUAGE JAVA
EXTERNAL NAME "com.ibm.broker.javacompute.Base64.decode";
|
What am I doing wrong here? |
|
| Back to top |
|
|
| kimbert |
| Posted: Thu Sep 20, 2012 7:42 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
You have two different problems to solve.
a) how to decode your base64 data into a BLOB
b) how to parse the decoded BLOB to produce a message tree ( only required when the base64 is an XML document )
a) is a solved problem. If you want to avoid writing ESQL for this then you can ask XMLNSC to do it automatically. Just switch on validation and set 'Build tree using schema types' on the input node. This only works if your XML schema is accurate.
b) is easy. Use ESQL's CREATE statement with a PARSE clause to parse the BLOB into a message tree. Search this forum for examples - this question comes up all the time. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Thu Sep 20, 2012 7:54 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
Use Tagged Encoded Length which supports handling AL3 messages containing message groups unknown to the message dictionary.
http://publib.boulder.ibm.com/infocenter/wmbhelp/v7r0m0/index.jsp?topic=%2Fcom.ibm.etools.mft.doc%2Fac00590_.htm
Alternately, I always recommend people take the WMB Dev I and Dev II training classes.
WebSphere Message Broker has the ability to leverage ACORD and other open standards with support for ACORD AL3, CSV, EDIFACT, FIX, HL7, SWIFT, TLOG, and X12.
http://publib.boulder.ibm.com/infocenter/wmbhelp/v8r0m0/index.jsp?topic=%2Fcom.ibm.etools.mft.doc%2Fad09520_.htm
Each group with an ACORD AL3 message has a header consisting of a one-digit number, three letters, plus a three-digit total length count. These first seven characters can be modeled as a tag. The data within the headers is fixed length.
http://publib.boulder.ibm.com/infocenter/wmbhelp/v8r0m0/index.jsp?topic=%2Fcom.ibm.etools.mft.doc%2Fad09530_.htm
http://publib.boulder.ibm.com/infocenter/wmbhelp/v8r0m0/index.jsp?topic=%2Fcom.ibm.etools.mft.doc%2Fad09540_.htm
http://publib.boulder.ibm.com/infocenter/wmbhelp/v8r0m0/index.jsp?topic=%2Fcom.ibm.etools.mft.doc%2Fad09550_.htm
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| deepak.gowder |
| Posted: Thu Sep 20, 2012 10:30 am Post subject: |
|
|
Novice
Joined: 19 Sep 2012
Posts: 20
|
Thank you for the suggestions. It helped, I was able to parse the BLOB into an XML using this code:
| Code: |
CREATE LASTCHILD OF OutputRoot.XMLNSC.CheckInMessage.Acord103 DOMAIN('XMLNSC') PARSE(DecodedAcord, InputProperties.Encoding, InputProperties.CodedCharSetId, 'BLOB', 'XML');
|
And the Output looked like this:
| Code: |
XMLNSC
CheckInMessage
PolicyNumber
SSN:CHARACTER:123-34-6789
LastName:CHARACTER:Test
FirstName:CHARACTER:User
PDF:CHARACTER:JVBERi0xLjQNJeLjz9MNCjI1ODAgM
Acord103
XMLNSC
XmlDeclaration
Version:CHARACTER:1.0
Encoding:CHARACTER:UTF-16
StandAlone:CHARACTER:no
TXLife
xsd:CHARACTER:http://www.w3.org/2001/XMLSchema
xsi:CHARACTER:http://www.w3.org/2001/XMLSchema-instance
xmlns:CHARACTER:http://ACORD.org/Standards/Life/2
Version:CHARACTER:2.23.00
TXLifeRequest
id:CHARACTER:TXLifeRequest_a1fc84d9-1fc6-4479-9cd6-cf34541f0b16
PrimaryObjectID:CHARACTER:Holding_fd8e853f-2039-4ac3-9fb3-6e043cd031f9
TransRefGUID:CHARACTER:beafac2e-c44e-4107-879b-566c05ab5383
TransType:CHARACTER:OLI_TRANS_NBSUB
TransExeDate:CHARACTER:2012-09-14
TransExeTime:CHARACTER:08:53:02.1596290-06:00
OLifE
Holding
id:CHARACTER:Holding_fd8e853f-2039-4ac3-9fb3-6e043cd031f9
HoldingTypeCode:CHARACTER:OLI_HOLDTYPE_POLICY
Policy
CarrierPartyID:CHARACTER:Party_23ccb721-ee84-4920-830d-7c87ce95ba04
PolNumber
ProductType:CHARACTER:OLI_PRODTYPE_TERM
CarrierCode:CHARACTER:AMA
Jurisdiction:CHARACTER:OLI_USA_AL
Life
Coverage
id:CHARACTER:Coverage_badb240f-e9eb-44e4-b9e2-0047fc5bf2fc
LifeCovTypeCode:CHARACTER:OLI_COVTYPE_TERMLEVEL
IndicatorCode:CHARACTER:OLI_COVIND_BASE
LifeParticipant
PartyID:CHARACTER:Party_78b7ae67-b695-4a99-8371-21cd9d446dd7
ApplicationInfo
ApplicationJurisdiction:CHARACTER:OLI_USA_AL
Party
id:CHARACTER:Party_78b7ae67-b695-4a99-8371-21cd9d446dd7
PartyTypeCode:CHARACTER:OLI_PT_PERSON
Address
id:CHARACTER:Address_604056fd-2c77-4568-9b1b-ce26fd38344b
AddressTypeCode:CHARACTER:OLI_ADTYPE_HOME
Party
id:CHARACTER:Party_23ccb721-ee84-4920-830d-7c87ce95ba04
PartyTypeCode:CHARACTER:OLI_PT_ORG
|
Sorry, couldn't get the alignment right.
Now I'm trying to read the individual elements from this.
| Code: |
| SET Environment.Variables.TestVariable = Acord103.XMLNSC.TXLife.TXLifeRequest.TransRefGUID; |
Can't get to read the element, what am I doing wrong here? Might be silly, I think, but trying to get over it.
Cheers! |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Thu Sep 20, 2012 10:33 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| Code: |
| SET Environment.Variables.TestVariable = OutputRoot.XMLNSC.CheckInMessage.Acord103.XMLNSC.TXLife.TXLifeRequest.TransRefGUID; |
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| deepak.gowder |
| Posted: Thu Sep 20, 2012 10:42 am Post subject: |
|
|
Novice
Joined: 19 Sep 2012
Posts: 20
|
Nice of you. Can I get some donations now?? |
|
| Back to top |
|
|
|
|
