MQSeries.net :: View topic - SELECT on XML

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 » SELECT on XML - Value not there.

Goto page 1, 2 Next

SELECT on XML - Value not there.

« View previous topic :: View next topic »

Author

Message

bdaoust

Posted: Wed Nov 04, 2015 2:14 pm Post subject: SELECT on XML - Value not there.

Centurion

Joined: 23 Sep 2010
Posts: 128

Trying to populate a variable DECLARED as CHAR

XML

Code:

<PremiumIssueAge>
<PremiumOnPolicyAtYear>2015</PremiumOnPolicyAtYear>
<PremiumAgeOrDuration>18</PremiumAgeOrDuration>
<TotalRiderCombination>406.48</TotalRiderCombination>
<TotalAnnualPremium>839.38</TotalAnnualPremium>
<DiscountedAnnualPremium>637.04</DiscountedAnnualPremium>]

Code:

SET TotalDiscountedGuaranteedAnnualPremiumAge18 = THE (SELECT ITEM FIELDVALUE(Y.PremiumIssueAge.TotalAnnualPremium)FROM InputRoot.XMLNSC.DIPolicyIssueData.PolicyInfo.Elements.ElementalPremiumsSection[] AS Y WHERE FIELDVALUE(Y.PremiumIssueAge.PremiumAgeOrDuration) = '18');

TotalDiscountedGuaranteedAnnualPremiumAge18 and TotalAnnualPremium are both CHARS.

In debugger I see Y as the values I want, but TotalAnnualPremium is set as null.

Thanks

timber

Posted: Wed Nov 04, 2015 3:11 pm Post subject:

Grand Master

Joined: 25 Aug 2015
Posts: 1280

Interesting. What does a debug-level user trace say? ( just the relevant portion, please!)

bdaoust

Posted: Wed Nov 04, 2015 4:16 pm Post subject:

Centurion

Joined: 23 Sep 2010
Posts: 128

Unfortunately, I don't have access to the server the broker runs on, so I can't do a user trace. i can just do a trace node, but that I don't think will give me much else then I can see in debugger.

bdaoust

Posted: Wed Nov 04, 2015 6:09 pm Post subject:

Centurion

Joined: 23 Sep 2010
Posts: 128

Looks like the input data has some padding.

Code:

(0x01000000:Folder):PremiumIssueAge = (
(0x03000000:PCDataField):PremiumOnPolicyAtYear = '2052' (CHARACTER)
(0x03000000:PCDataField):PremiumAgeOrDuration = ' 66' (CHARACTER)
(0x03000000:PCDataField):TotalRiderCombination = ' 830.55' (CHARACTER)
(0x03000000:PCDataField):TotalAnnualPremium = ' 2976.05' (CHARACTER)

Now need to see if there is a quick way to clean this up. The incoming XML is huge.

maurito

Posted: Thu Nov 05, 2015 12:13 am Post subject:

Partisan

Joined: 17 Apr 2014
Posts: 358

bdaoust wrote:

Looks like the input data has some padding.

Code:

Now need to see if there is a quick way to clean this up. The incoming XML is huge.

The padding will not make any difference to the problem. If you don't want the leading spaces you can deal with that later ( using the TRIM function or casting as decimal ).
First thing to look at is the path to the fields you want. Y may be pointing to the right place, but you might have a level missing between Y and the fields you want ?... difficult to tell as you only included very small part of the message.
Then again, as Timber pointed out, a user trace with debug level would tell you exactly what the problem is. If you don't have access to the server, just write a small flow in your laptop ( surely you have a laptop and IIB installed, don't you ? )
MQInput->Compute with just the SELECT , deploy and trace.

bdaoust

Posted: Thu Nov 05, 2015 4:14 am Post subject:

Centurion

Joined: 23 Sep 2010
Posts: 128

Well the padding did seem to make a difference when I was doing the SELECT.

This worked:

Code:

BUT subsequent SELECTS do not -

Code:

SET TotalDiscountedGuaranteedAnnualPremiumAge28 = THE (SELECT ITEM TRIM(FIELDVALUE(Y.PremiumIssueAge.TotalAnnualPremium)) FROM InputRoot.XMLNSC.DIPolicyIssueData.PolicyInfo.Elements.ElementalPremiumsSection[] AS Y WHERE TRIM(FIELDVALUE(Y.PremiumIssueAge.PremiumAgeOrDuration)) = 28);
SET TotalDiscountedGuaranteedAnnualPremiumAge29 = THE (SELECT ITEM TRIM(FIELDVALUE(Y.PremiumIssueAge.TotalAnnualPremium)) FROM InputRoot.XMLNSC.DIPolicyIssueData.PolicyInfo.Elements.ElementalPremiumsSection[] AS Y WHERE TRIM(FIELDVALUE(Y.PremiumIssueAge.PremiumAgeOrDuration)) = 29);
SET TotalDiscountedGuaranteedAnnualPremiumAge30 = THE (SELECT ITEM TRIM(FIELDVALUE(Y.PremiumIssueAge.TotalAnnualPremium)) FROM InputRoot.XMLNSC.DIPolicyIssueData.PolicyInfo.Elements.ElementalPremiumsSection[] AS Y WHERE TRIM(FIELDVALUE(Y.PremiumIssueAge.PremiumAgeOrDuration)) = 30);
SET TotalDiscountedGuaranteedAnnualPremiumAge31 = THE (SELECT ITEM TRIM(FIELDVALUE(Y.PremiumIssueAge.TotalAnnualPremium)) FROM InputRoot.XMLNSC.DIPolicyIssueData.PolicyInfo.Elements.ElementalPremiumsSection[] AS Y WHERE TRIM(FIELDVALUE(Y.PremiumIssueAge.PremiumAgeOrDuration)) = 31);

Y Looks lie :

Code:

(0x01000000:Folder):PremiumIssueAge = (
(0x03000000:PCDataField):PremiumOnPolicyAtYear = '2052' (CHARACTER)
(0x03000000:PCDataField):PremiumAgeOrDuration = ' 29' (CHARACTER)
(0x03000000:PCDataField):TotalRiderCombination = ' 830.55' (CHARACTER)
(0x03000000:PCDataField):TotalAnnualPremium = ' 2976.05' (CHARACTER
(0x01000000:Folder):PremiumIssueAge = (
(0x03000000:PCDataField):PremiumOnPolicyAtYear = '2052' (CHARACTER)
(0x03000000:PCDataField):PremiumAgeOrDuration = ' 30' (CHARACTER)
(0x03000000:PCDataField):TotalRiderCombination = ' 830.55' (CHARACTER)
(0x03000000:PCDataField):TotalAnnualPremium = ' 2976.05' (CHARACTER

So the select for 30 does not work. The only thing I can think of is that 29 is the first item. But shouldn't the SELECT extract the value based on the condition?

I might try to go back to using a DFDL and a mapping node, but as you may have noticed from other messages, my DFDLs weren't deploying in a timely fashion.

Unfortunately, I don't have a local run time yet. I'm working with our software team to do that for me - (we are pretty locked down so I just cant install anything)

maurito

Posted: Thu Nov 05, 2015 4:57 am Post subject:

Partisan

Joined: 17 Apr 2014
Posts: 358

bdaoust wrote:

Code:

well, from the bit of message you inserted, I can see you need PremiumIssueAge[] somewhere in your path, and as you do not have it, it is correct that only the first one is searched.

is ElementalPremiumsSection a repeating element ? and PremiumIssueAge a repeating element within it ?

The [] means it is an array that needs to be searched. so probably you need y pointing to

Code:

ElementalPremiumsSection.PremiumIssueAge[]

bdaoust

Posted: Thu Nov 05, 2015 5:05 am Post subject:

Centurion

Joined: 23 Sep 2010
Posts: 128

I saw that as well so I tried:

Code:

SET TotalDiscountedGuaranteedAnnualPremiumAge29 = THE (SELECT ITEM TRIM(FIELDVALUE(Y.PremiumIssueAge.TotalAnnualPremium)) FROM InputR;oot.XMLNSC.DIPolicyIssueData.PolicyInfo.Elements.ElementalPremiumsSection.PremiumIssueAge[] AS Y WHERE TRIM(FIELDVALUE(Y.PremiumIssueAge.PremiumAgeOrDuration)) = 29)
SET TotalDiscountedGuaranteedAnnualPremiumAge30 = THE (SELECT ITEM TRIM(FIELDVALUE(Y.PremiumIssueAge.TotalAnnualPremium)) FROM InputR;oot.XMLNSC.DIPolicyIssueData.PolicyInfo.Elements.ElementalPremiumsSection.PremiumIssueAge[] AS Y WHERE TRIM(FIELDVALUE(Y.PremiumIssueAge.PremiumAgeOrDuration)) = 30)

Still didn't get 30, just 29

XML:

Code:

maurito

Posted: Thu Nov 05, 2015 5:13 am Post subject:

Partisan

Joined: 17 Apr 2014
Posts: 358

bdaoust wrote:

I saw that as well so I tried:

Code:

Still didn't get 30, just 29

XML:

Code:

Of course it would not. I have done enough debugging for you. Now you need to revisit your code and see where the value of Y.etc.etc is pointing at.
and I have my doubts about you getting the value for 29, as you are still pointing to the wrong place.

and by the way, have you not heard of REFERENCE s ?... what you are doing is very inefficient.

bdaoust

Posted: Thu Nov 05, 2015 5:26 am Post subject:

Centurion

Joined: 23 Sep 2010
Posts: 128

Not sure why InputRoot was wrong here but it's not that way in the code

Code:

SET TotalDiscountedGuaranteedAnnualPremiumAge29 = THE (SELECT ITEM TRIM(FIELDVALUE(Y.PremiumIssueAge.TotalAnnualPremium)) FROM InputRoot.XMLNSC.DIPolicyIssueData.PolicyInfo.Elements.ElementalPremiumsSection.PremiumIssueAge[] AS Y WHERE TRIM(FIELDVALUE(Y.PremiumIssueAge.PremiumAgeOrDuration)) = 29)
SET TotalDiscountedGuaranteedAnnualPremiumAge30 = THE (SELECT ITEM TRIM(FIELDVALUE(Y.PremiumIssueAge.TotalAnnualPremium)) FROM InputRoot.XMLNSC.DIPolicyIssueData.PolicyInfo.Elements.ElementalPremiumsSection.PremiumIssueAge[] AS Y WHERE TRIM(FIELDVALUE(Y.PremiumIssueAge.PremiumAgeOrDuration)) = 30)

Still not working but wanted to make that correction

mgk

Posted: Thu Nov 05, 2015 6:14 am Post subject:

Padawan

Joined: 31 Jul 2003
Posts: 1638

Your WHERE clause in the latest example is wrong as the "PremiumIssueAge" needs to be removed from the WHERE clause as you now include it in the SELECT clause.

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

fjb_saper

Posted: Thu Nov 05, 2015 8:53 am Post subject:

Grand High Poobah

Joined: 18 Nov 2003
Posts: 20697
Location: LI,NY

You need to use the Input Node setting to cast the values to their defined types.
Your input tree shows the values as being CHAR with a leading spaces.

Your select goes against the trim'd values (still char) but tries to check with numbers.

Code:

(where .... = 30) should be (where .... = '30') to match types...

So there is a type mismatch in your select that is inherent and will not let you return the right / expected values.

Have fun

_________________
MQ & Broker admin

mgk

Posted: Thu Nov 05, 2015 12:50 pm Post subject:

Padawan

Joined: 31 Jul 2003
Posts: 1638

Quote:

So there is a type mismatch in your select that is inherent and will not let you return the right / expected values

No, that's not right - there are implicit casts for comparison between lots of types, including CHARACTER and INTEGER, see here:

http://www-01.ibm.com/support/knowledgecenter/SSMKHH_10.0.0/com.ibm.etools.mft.doc/ak05700_.htm?lang=en

Quote:

You need to use the Input Node setting to cast the values to their defined types.

So this would not be needed in this case.

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

fjb_saper

Posted: Fri Nov 06, 2015 5:25 am Post subject:

Grand High Poobah

Joined: 18 Nov 2003
Posts: 20697
Location: LI,NY

I stand corrected. Still not feeling too cosy about implicit casts as you might end up casting to the wrong type (int instead of decimal)...

_________________
MQ & Broker admin

bdaoust

Posted: Mon Nov 09, 2015 1:18 pm Post subject:

Centurion

Joined: 23 Sep 2010
Posts: 128

Ok I was able to get the desired result,

Trying to take it a step further.

If I do this:

Code:

SET OutputRoot.XMLNSC.myTest[] = (SELECT * FROM InputRoot.XMLNSC.DIPolicyIssueData.PolicyInfo.Elements.ElementalPremiumsSection.PremiumIssueAge[] AS R WHERE FIELDVALUE(R.PremiumAgeOrDuration) = 30);
SET TotalDiscountedGuaranteedAnnualPremiumAge65 = THE (SELECT ITEM FIELDVALUE(H.ElementAgePremium) FROM OutputRoot.XMLNSC.BDD.ElementalPremiumsDetailsSection.ElementalPremiumDetails[] AS H WHERE FIELDVALUE(H.PremiumElementShortDescription) = 'FIO');

myTest looks like this:

Code:

myTest
PremiumOnPolicyAtYear:CHARACTER:2016
PremiumAgeOrDuration:CHARACTER: 30
TotalRiderCombination:CHARACTER: 430.48
TotalAnnualPremium:CHARACTER: 905.18
DiscountedAnnualPremium:CHARACTER: 686.39
ElementalPremiumsDetailsSection
      ElementalPremiumDetails
         PremiumElementShortDescription:CHARACTER:TT342
         lementAgePremium:CHARACTER: 444.70
      ElementalPremiumDetails
         PremiumElementShortDescription:CHARACTER:FIO
         ElementAgePremium:CHARACTER: 39.93
      ElementalPremiumDetails

I like to have an ESQL variable with the ElementAgePremium of 39.93, but can't seem to get the syntax right on the subquery:

I used to docs example on nested selects:

Code:

SET TotalFIOPX = THE
(SELECT ITEM FIELDVALUE(TT.ElementAgePremium)
FROM (
   SELECT *
   FROM InputRoot.XMLNSC.DIPolicyIssueData.PolicyInfo.Elements.ElementalPremiumsSection.PremiumIssueAge[] AS RR
   WHERE FIELDVALUE(RR.PremiumAgeOrDuration) = 30
)
   AS TT WHERE FIELDVALUE(TT.PremiumElementShortDescription) = 'FIO'
);

ESQL is showing two syntax errors. One shows just syntax error and the other says valid options almost as though I'm missing a closely ) or something, but I can't seem to find it.

I'd appreciate any help.

Display posts from previous:

Goto page 1, 2 Next

Page 1 of 2

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » SELECT on XML - Value not there.

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