| Author |
Message
|
| zhumingvictor |
 Posted: Mon Jul 29, 2013 7:29 am Post subject: How to select all leaf node from xml |
 |
|
Novice
Joined: 10 May 2013
Posts: 17
|
Hi I am trying to do:
SET MY_COUNT= (SELECT COUNT(*)
FROM InputRoot.XMLNSC.myroot.batches.batch[].transactions.transaction[] as trans
WHERE trans.transType = 'ABC');
But I got Syntax error : cannot specify '[]' in the middle of a field reference.
The list specifier ('[]') can only be used at the end of a field reference.
Anybody know how I can correct this?
Thanks. |
|
| Back to top |
|
 |
| lancelotlinc |
| Posted: Mon Jul 29, 2013 7:38 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
Set a reference. InputRoot.XMLNSC.myroot.batches
Then iterate through, selecting batch[I].transactions.transaction[J].
Its basically what you were trying to do in one-shot, split up into two steps.
Good luck.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| zhumingvictor |
| Posted: Mon Jul 29, 2013 7:39 am Post subject: |
|
|
Novice
Joined: 10 May 2013
Posts: 17
|
| lancelotlinc wrote: |
Set a reference. InputRoot.XMLNSC.myroot.batches
Then iterate through, selecting batch[I].transactions.transaction[J].
Its basically what you were trying to do in one-shot, split up into two steps.
Good luck. |
No way to do it without looping through? |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Mon Jul 29, 2013 7:45 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
|
| Back to top |
|
|
| dogorsy |
| Posted: Mon Jul 29, 2013 8:35 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
you need to use nested selects and then you don't need to loop.
something like
SET MY_COUNT= SELECT
( SELECT COUNT(*)
FROM I.transactions.transaction[] as trans
WHERE trans.transType = 'ABC')
from InputRoot.XMLNSC.myroot.batches.batch[] as I;
in terms of performance it is way faster than looping. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Mon Jul 29, 2013 8:54 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| dogorsy wrote: |
| in terms of performance it is way faster than looping. |
Absolutely korek. Unless s/he needs to loop anyway for other data capture.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| digoshc |
| Posted: Mon Jul 29, 2013 9:35 am Post subject: |
|
|
Apprentice
Joined: 11 Jul 2013
Posts: 25
|
| Hi, got a question. Do you guys could give a brief explanation why nested selects would be faster than a loop, please? Thank you. |
|
| Back to top |
|
|
| dogorsy |
| Posted: Mon Jul 29, 2013 9:39 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
| it has been performance tested. |
|
| Back to top |
|
|
| kimbert |
| Posted: Mon Jul 29, 2013 11:42 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
| Quote: |
| Do you guys could give a brief explanation why nested selects would be faster than a loop, please? |
I didn't say that, and I wouldn't. It might depend on what you're SELECTing from and how complex the SELECT statements are.
As always, the performance of your message flow is your responsibility, so you should put a performance test framework in place and do some experiments.
_________________
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 |
|
|
| zhumingvictor |
| Posted: Mon Jul 29, 2013 12:00 pm Post subject: |
|
|
Novice
Joined: 10 May 2013
Posts: 17
|
| dogorsy wrote: |
you need to use nested selects and then you don't need to loop.
something like
SET MY_COUNT= SELECT
( SELECT COUNT(*)
FROM I.transactions.transaction[] as trans
WHERE trans.transType = 'ABC')
from InputRoot.XMLNSC.myroot.batches.batch[] as I;
in terms of performance it is way faster than looping. |
It works!  I don't need each item so nested query is good. Thanks to all of you. |
|
| Back to top |
|
|
| digoshc |
| Posted: Mon Jul 29, 2013 12:36 pm Post subject: |
|
|
Apprentice
Joined: 11 Jul 2013
Posts: 25
|
| @kimbert: I agree. Overall, I believe ESQL might have a better performance than others transformations approaches since it's a DSL built specifically to handle message trees, but a performance test would help to prove this hypothesis. |
|
| Back to top |
|
|
| dogorsy |
| Posted: Mon Jul 29, 2013 10:03 pm Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
| zhumingvictor wrote: |
| dogorsy wrote: |
you need to use nested selects and then you don't need to loop.
something like
SET MY_COUNT= SELECT
( SELECT COUNT(*)
FROM I.transactions.transaction[] as trans
WHERE trans.transType = 'ABC')
from InputRoot.XMLNSC.myroot.batches.batch[] as I;
in terms of performance it is way faster than looping. |
It works! I don't need each item so nested query is good. Thanks to all of you. |
Good !. Even if you do need items from the message, you can code a complex nested SELECT.
for more info, see
"Transforming a complex message" topic in the infocenter |
|
| Back to top |
|
|
|
|
