| Author |
Message
|
| sravan |
 Posted: Fri Nov 22, 2013 1:36 pm Post subject: ESQL question |
 |
|
Centurion
Joined: 02 Apr 2010
Posts: 104
Location: Charlotte
|
I am having below Sample XML need to write ESQL to get the values from the elements,could not model below based on the XSD given -
Please suggest me the options -
<?xml version="1.0" encoding="UTF-8"?>
<connector name="WBE" version="2.2">
<connector-bundle id="274DD21014071053B911E3F8834CD34A" name="Cash" type="Action" workflow="TEST">
<connector-object name="SampleObject">
<field name="TERM_OWNER" type="String">EXAMPLE</field>
<field name="PRIMY_ACCT_NO" type="String">1234</field>
<field name="BANK_FIID" type="String">0099</field>
<field name="bepTypeID" type="String">CBP08766-0001-1000 </field>
<field name="TERM_COUNTRY" type="String">US</field>
<field name="emitTimeStamp" type="DateTime">2012-11-15T15:00:04Z</field>
<field name="TERM_ADDR" type="String">*LAB</field>
<field name="POS_CARD_NO" type="Real">4352760760148085</field>
<field name="AUTH_AVAIL_BAL" type="Real">342203.41</field>
<field name="FUTURE_DAY_AMT" type="Real">0</field>
<field name="CUSTOMER_REFERENCE" type="String">0000000000</field>
<field name="bepVersion" type="String">1.0.0.0</field>
<field name="SYSTEM_TRACE_NO" type="String">000284</field>
<field name="REF_NO" type="String">7695</field>
<field name="correlID" type="String">000284</field>
<field name="TRAN_TYPE_IND" type="String">C</field>
<field name="sorID" type="String">FAST</field>
<field name="TRAN_AMT" type="Real">378.45</field>
<field name="CAPTURE_DATE_MMDD" type="String">1123</field>
<field name="IMMED_CREDIT_AMT" type="Real">378.45</field>
<field name="TRANSMIT_DATE" type="DateTime">2012-11-22T21:00:05Z</field>
<field name="Event_Channel" type="String">ATM</field>
<field name="TERM_STATE" type="String">GA</field>
<field name="TERM_CITY" type="String">GA CITY</field>
</connector-object>
</connector-bundle>
<system>example.com</system>
<timestamp>2013-11-22T15:00:05.505-06:00</timestamp>
<loginfo>IBM</loginfo>
</connector>
_________________
skr_wmb |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Fri Nov 22, 2013 1:56 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
What have you tried?
What were the problems you encountered?
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| ganesh |
| Posted: Fri Nov 22, 2013 5:23 pm Post subject: |
|
|
Master
Joined: 18 Jul 2010
Posts: 294
|
| Use esql and get the elements from the tree, there are articles in infocenter that show how to access elements in a tree. |
|
| Back to top |
|
|
| sravan |
| Posted: Sat Nov 23, 2013 10:39 am Post subject: |
|
|
Centurion
Joined: 02 Apr 2010
Posts: 104
Location: Charlotte
|
I have used XMLSNC parser in the input node and below is the message tree stored in an environment variable It does not have the field name attribute & name It has only values -
So I am only able to get the values of fields with below,Please let me know if it a parser issue or any other suggestion to get the values by the attribute like
<field name="PRIMY_ACCT_NO" type="String">004770768089</field> -
FIELDVALUE(InputRoot.XMLNSC.connector."connector-bundle"."connector-object".field[1]);
Environment
( ['MQROOT' : 0x2ef06080]
(0x01000000:Name):Variables = (
(0x03000000:NameValue):UUID = '97778d98-eaed-45b1-b1bc-2ef33ad8f2f3' (CHARACTER)
(0x03000000:NameValue):FLOW_TYPE = 'request' (CHARACTER)
(0x03000000:NameValue):RPREQUEST_ID = '97778d98-eaed-45b1-b1bc-2ef33ad8f2f3' (CHARACTER)
(0x03000000:NameValue):RPREFERENCEID = '97778d98-eaed-45b1-b1bc-2ef33ad8f2f3' (CHARACTER)
(0x03000000:NameValue):IFC_NM = 'EBEP' (CHARACTER)
(0x03000000:NameValue):MSG_FORMAT = 'XML' (CHARACTER)
(0x03000000:NameValue):PASSWORD = 'get2mrp_LLE' (CHARACTER)
(0x03000000:NameValue):TRAN_TYP_NM = 'CurrentDayReport' (CHARACTER)
(0x03000000:NameValue):TXN_AUDIT = 'TRUE' (CHARACTER)
(0x03000000:NameValue):CLIENT_RETURN_CODE = '0' (CHARACTER)
(0x03000000:NameValue):GUARANTEED = 'TRUE' (CHARACTER)
(0x03000000:NameValue):REQ_ID = '97778d98-eaed-45b1-b1bc-2ef33ad8f2f3' (CHARACTER)
(0x03000000:NameValue):FLOW_STATUS = 'AdapterInbound' (CHARACTER)
(0x03000000:NameValue):MESSAGE_ID = 'X'c3e2d840d8e3f9f44040404040404040cc4eae4faccd910b'' (CHARACTER)
(0x03000000:NameValue):INPUT_MSG = '<?xml version="1.0" encoding="UTF-8"?>
<connector name="WBE" version="2.2"><connector-bundle id="274DD21014071053B911E3F8834CD34A" name="CashproATMTeller" type="Action" workflow="FACE"><connector-object name="CashproATMTellerActionObject"><field name="TERM_OWNER" type="String">ABC</field><field name="PRIMY_ACCT_NO" type="String">004770768089</field><field name="BANK_FIID" type="String">0099</field><field name="bepTypeID" type="String">CBP08766-0001-1000 </field><field name="TERM_COUNTRY" type="String">US</field><field name="emitTimeStamp" type="DateTime">2012-11-15T15:00:04Z</field><field name="TERM_ADDR" type="String">*CLT LAB</field><field name="POS_CARD_NO" type="Real">4352760760148085</field><field name="AUTH_AVAIL_BAL" type="Real">342203.41</field><field name="FUTURE_DAY_AMT" type="Real">0</field><field name="CUSTOMER_REFERENCE" type="String">0000000000</field><field name="bepVersion" type="String">1.0.0.0</field><field name="SYSTEM_TRACE_NO" type="String">000284</field><field name="REF_NO" type="String">7695</field><field name="correlID" type="String">000284</field><field name="TRAN_TYPE_IND" type="String">C</field><field name="sorID" type="String">FAST</field><field name="TRAN_AMT" type="Real">378.45</field><field name="CAPTURE_DATE_MMDD" type="String">1123</field><field name="IMMED_CREDIT_AMT" type="Real">378.45</field><field name="TRANSMIT_DATE" type="DateTime">2012-11-22T21:00:05Z</field><field name="Event_Channel" type="String">ATM</field><field name="TERM_STATE" type="String">TX</field><field name="TERM_CITY" type="String">TX CITY</field></connector-object></connector-bundle><system>lkcme29586.bankofamerica.com</system><timestamp>2013-11-22T15:00:05.505-06:00</timestamp><loginfo>This is an event from IBM WebSphere Business Events</loginfo></connector>
' (CHARACTER)
(0x03000000:NameValue):Application_ID = 'EBEP' (CHARACTER)
(0x01000000:Name ):TEMP3 = (
(0x01000000:Name):XmlDeclaration = (
(0x03000000:NameValue):Version = '1.0' (CHARACTER)
(0x03000000:NameValue):Encoding = 'UTF-8' (CHARACTER)
)
(0x01000000:Name):connector = (
(0x03000000:NameValue):name = 'WBE' (CHARACTER)
(0x03000000:NameValue):version = '2.2' (CHARACTER)
(0x01000000:Name ):connector-bundle = (
(0x03000000:NameValue):id = '274DD21014071053B911E3F8834CD34A' (CHARACTER)
(0x03000000:NameValue):name = 'CPr' (CHARACTER)
(0x03000000:NameValue):type = 'Action' (CHARACTER)
(0x03000000:NameValue):workflow = 'FACE' (CHARACTER)
(0x01000000:Name ):connector-object = (
(0x03000000:NameValue):name = 'CashproATMTellerActionObject' (CHARACTER)
(0x03000000:NameValue):field = ABC (CHARACTER)
(0x03000000:NameValue):field = '004770768089' (CHARACTER)
(0x03000000:NameValue):field = '0099' (CHARACTER)
(0x03000000:NameValue):field = 'CBP08766-0001-1000 ' (CHARACTER)
(0x03000000:NameValue):field = 'US' (CHARACTER)
(0x03000000:NameValue):field = '2012-11-15T15:00:04Z' (CHARACTER)
(0x03000000:NameValue):field = '*CHARLOTTE LAB' (CHARACTER)
(0x03000000:NameValue):field = '4352760760148085' (CHARACTER)
(0x03000000:NameValue):field = '342203.41' (CHARACTER)
(0x03000000:NameValue):field = '0' (CHARACTER)
(0x03000000:NameValue):field = '0000000000' (CHARACTER)
(0x03000000:NameValue):field = '1.0.0.0' (CHARACTER)
(0x03000000:NameValue):field = '000284' (CHARACTER)
(0x03000000:NameValue):field = '7695' (CHARACTER)
(0x03000000:NameValue):field = '000284' (CHARACTER)
(0x03000000:NameValue):field = 'C' (CHARACTER)
(0x03000000:NameValue):field = 'FAST' (CHARACTER)
(0x03000000:NameValue):field = '378.45' (CHARACTER)
(0x03000000:NameValue):field = '1123' (CHARACTER)
(0x03000000:NameValue):field = '378.45' (CHARACTER)
(0x03000000:NameValue):field = '2012-11-22T21:00:05Z' (CHARACTER)
(0x03000000:NameValue):field = 'ATM' (CHARACTER)
(0x03000000:NameValue):field = 'TX' (CHARACTER)
(0x03000000:NameValue):field = 'TX CITY' (CHARACTER)
)
)
_________________
skr_wmb |
|
| Back to top |
|
|
| ganesh |
| Posted: Sat Nov 23, 2013 3:02 pm Post subject: |
|
|
Master
Joined: 18 Jul 2010
Posts: 294
|
Below piece of code returns the xml shown for the input message you have posted. Where are you stuck ?
| Code: |
SET OutputRoot.XMLNSC.Result.xmlFV = FIELDVALUE(InputRoot.XMLNSC."connector"."connector-bundle"."connector-object".field[1]);
SET OutputRoot.XMLNSC.Result.xmlAttriV = FIELDVALUE(InputRoot.XMLNSC."connector"."connector-bundle"."connector-object".field[1].(XMLNSC.Attribute)name);
|
| Quote: |
<Result>
<xmlFV>EXAMPLE</xmlFV>
<xmlAttriV>TERM_OWNER</xmlAttriV>
</Result>
|
|
|
| Back to top |
|
|
| sravan |
| Posted: Sat Nov 23, 2013 4:56 pm Post subject: |
|
|
Centurion
Joined: 02 Apr 2010
Posts: 104
Location: Charlotte
|
Hi Ganesh,
I dont want to use field[1] etc to get the values I want to refer to the Attribute and get the value in it.
I am able to loop in and get all the values example below -
DECLARE IREF REFERENCE TO InputRoot.XMLNSC.connector."connector-bundle"."connector-object";
IF (IREF.field[I].name = 'TERM_OWNER') THEN
SET EREF.TERM_OWNER = IREF.field[I] ;
END IF;
_________________
skr_wmb |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Sat Nov 23, 2013 11:14 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
It would be a lot more efficient (IMHO) to use a case statement
Extract the value of the name Attribute and case that.
It is a lot easier to read as well.
Something like this (not tested etc)
| Code: |
DECLARE IREF REFERENCE TO InputRoot.XMLNSC.connector."connector-bundle"."connector-object".field;
--setup loop for, while etc
set cFieldAttr = IREF.(XMLNSC.Attribute)name);
case cFieldAttr
when 'TERM_OWNER' then
...
...
end case;
-- end loop not forgetting to move to the next element
|
A 'while' or a 'for' loop would be better (IMHO) than using incrementing counters to traverse the incoming data.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| sravan |
| Posted: Sun Nov 24, 2013 4:28 am Post subject: |
|
|
Centurion
Joined: 02 Apr 2010
Posts: 104
Location: Charlotte
|
Thanks Davis & Ganesh,I did used one for loop to get all values and I am able to use it sucessfully.
_________________
skr_wmb |
|
| Back to top |
|
|
| kimbert |
| Posted: Mon Nov 25, 2013 6:32 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
When you post XML, please use [c o d e] tags to make it readable.
| Quote: |
| I have used XMLSNC parser in the input node and below is the message tree stored in an environment variable It does not have the field name attribute & name It has only values |
You have made a very simple mistake. When you created the field in the Environment tree, you did not assign the XMLNSC parser to it.
See http://www.mqseries.net/phpBB/viewtopic.php?p=183839&sid=c07e52f45ec1ff3dc9e141eaa583a238 for the solution ( the reply by jefflowrey )
_________________
Before you criticize someone, walk a mile in their shoes. That way you're a mile away, and you have their shoes too. |
|
| Back to top |
|
|
| sravan |
| Posted: Mon Nov 25, 2013 10:27 am Post subject: |
|
|
Centurion
Joined: 02 Apr 2010
Posts: 104
Location: Charlotte
|
Thanks Kimbert for pointing this for not able to seein the Environment.
But as suggested by Gangesh & Davies the issue was resolved.
_________________
skr_wmb |
|
| Back to top |
|
|
|
|
