| Author |
Message
|
| PRKUMAR |
 Posted: Tue Jan 21, 2014 8:11 am Post subject: Query in esql |
 |
|
Apprentice
Joined: 04 Sep 2012
Posts: 36
|
There is an XML message like below:
<Industry>
<Department id='01'></Department>
<Department id='02'></Department>
<Department id='03'></Department>
<Department id='04'></Department>
</Industry>
I would like to know the occurrence number of Department in <Industry> where id = '03'. In the above example, the occurrence number is 3( 3rd child)
I can do this using esql like below:
SET deptCount = CARDINALITY(InputRoot.XMLNSC.Industry.Department[]);
SET deptIndex = 1;
D: WHILE( deptIndex <= deptCount) DO
IF (Industry.Department.(XMLNSC.Attribute)id = '3') THEN
LEAVE D;
END IF;
SET deptIndex = deptIndex + 1;
END WHILE;
Now, deptIndex contains value as '3'.
Is there any function available or using SELECT statement can we retrieve this value ? instead of writing the code like above?
Please help me in minimizing the above process.
|
|
| Back to top |
|
 |
| Vitor |
| Posted: Tue Jan 21, 2014 9:06 am Post subject: Re: Query in esql |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| PRKUMAR wrote: |
I would like to know the occurrence number of Department in <Industry> where id = '03'. In the above example, the occurrence number is 3( 3rd child) |
Why? What business value does that give you? The only valid reason for knowing what the index has is to allow you to access the data in that child, and SELECT does that.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| PRKUMAR |
| Posted: Tue Jan 21, 2014 10:33 am Post subject: |
|
|
Apprentice
Joined: 04 Sep 2012
Posts: 36
|
Hi Vitor,
First of all thanks for your response.
The business value is something more than I specified above. I have given just brief info only. I am getting input as CSV file having more than 500 records. I need to construct a single output XML message having four departments having id's ranging from 1 to 4. The input csv records may not come in particular order. Based on some value of input csv record, i need to map the record elements to one of the department. In this scenario, I am looking for a simpler kind of logic for to find particular department.
Input: CSV, Output: XML, MB Version: 8.0.0.1 |
|
| Back to top |
|
|
| Vitor |
| Posted: Tue Jan 21, 2014 11:18 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| PRKUMAR wrote: |
| In this scenario, I am looking for a simpler kind of logic for to find particular department. |
Which is exactly the scenario I postulated above, as it's the most common reason people think they need an index.
So what you are trying to do is SELECT the element where the attibute has a given value (3 in your example) and process the data associated with it (presumably in the format of child elements).
Notice how I've put the solution in CAPS 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| dogorsy |
| Posted: Wed Jan 22, 2014 2:03 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
According to your original post, your input is an XML message. Subsequently you said it is a CSV file with more than 500 records. If you are not clear about what your input is, it is extremely difficult to find a solution , either by yourself or anyone in this forum.
If your input is a CSV file, then probably you will be reading one record at a time. You may also want to read the whole file, but that needs to be decided by you, and will affect the logic in your compute node. |
|
| Back to top |
|
|
| kimbert |
| Posted: Wed Jan 22, 2014 3:18 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
The simplest solution is four SELECT statements ( one per department ).
That will only work if you are reading the entire file. If you need to process the file one record at a time ( in order to keep memory usage nice and low ) then you will need to build up the output one record at a time.
Either way, I don't think it will help to know the index of a particular array element. If you want to construct OutputRoot.XMLNSC field-by-field then you could maintain four REFERENCE variables and use CREATE NEXTSIBLING statements to append records to each output array.
_________________
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 |
|
|
| mqsiuser |
| Posted: Thu Jan 23, 2014 6:45 am Post subject: Re: Query in esql |
|
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
| PRKUMAR wrote: |
There is an XML message like below:
<Industry>
<Department id='01'></Department>
<Department id='02'></Department>
<Department id='03'></Department>
<Department id='04'></Department>
</Industry>
Is there any function available or using SELECT statement can we retrieve this value ? instead of writing the code like above?
|
I guess you want to get to that element... and you shouldn't do that by getting it's index (with CARDINALITY) and then accessing it (by using INDEX ("Department[3]")... you can, but there are other ways, which result in a significant better performance!
Also quering (with SELECT ...) may (does likely) reduce performance. Quering is great when working with databases, but not for message transformation.
I guess it gets applied too often (for msg transformation), because people know it from databases (you may know it from there).
You can use "myOutStructure.{id}.myField" or write custom code (using references, as kimbert suggests) or probably by "moving to it (on the output root) based on a condition (or create, when missing)". Your condition is that "id is equal to something (e.g. 01, 02, 03, 04, ...)".
_________________
Just use REFERENCEs |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Jan 23, 2014 6:59 am Post subject: Re: Query in esql |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| mqsiuser wrote: |
| Also quering (with SELECT ...) may (does likely) reduce performance. Quering is great when working with databases, but not for message transformation. |
Cite your sources.
SELECT (as provided by WMB) is built for handling a message tree and is highly performant.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| dogorsy |
| Posted: Thu Jan 23, 2014 7:01 am Post subject: Re: Query in esql |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
| mqsiuser wrote: |
Also quering (with SELECT ...) may (does likely) reduce performance. |
Pardon me ?!!... can you justify that ?! |
|
| Back to top |
|
|
| mqsiuser |
| Posted: Thu Jan 23, 2014 7:46 am Post subject: |
|
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
Ah, sorry, it is "if you repeatedly query your input-message (to build the/one output msg), then this will (likely) reduce performance".
If you manage to build your output based on a single SELECT-clause, bravo  , then it's like vitor says !
_________________
Just use REFERENCEs |
|
| Back to top |
|
|
| dogorsy |
| Posted: Thu Jan 23, 2014 7:51 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
| mqsiuser wrote: |
Ah, sorry, it is "if you repeatedly query your input-message (to build the/one output msg), then this will (likely) reduce performance".
If you manage to build your output based on a single SELECT-clause, bravo , then it's like vitor says ! |
??? I am still at a loss, what are you proposing ?!!
And by the way ( before Vitor bites you ), it is Vitor with uppercase V. |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Jan 23, 2014 7:55 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| dogorsy wrote: |
| ??? I am still at a loss, what are you proposing ?!! |
Me too
| dogorsy wrote: |
| And by the way ( before Vitor bites you ), it is Vitor with uppercase V. |
I'm good; at least he didn't add a "c".
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| kimbert |
| Posted: Thu Jan 23, 2014 7:57 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
Just for PRKUMAR's benefit ( because he is probably getting completely confused by the last few posts )...
mqsiuser is very, very focused on performance. He is concerned that executing four SELECT statements will be slower than some other, single-statement solution. I'm not sure what solution he has in mind, but I am sure that four SELECTs will not kill the performance of your message flow. Unless you have some fairly stringent QoS requirements.
@mqsiuser: In many message flows, transformation of the message tree does not dominate the performance of the flow. Reducing CPU usage is sometimes very important, but not always.
_________________
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 |
|
|
| mqsiuser |
| Posted: Thu Jan 23, 2014 8:00 am Post subject: |
|
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
| kimbert wrote: |
| The simplest solution is four SELECT statements ( one per department ). |
That will go into the message 4 times. With references you will go into your message only ONCE (if done right). If this is just a simple example (and the OP told, that the actual file has 500 rows), then that matters: The time it takes to go into a message 4 times is about 4 times more than if you go through (with a reference) ONE time  .
I don't understand, why this isn't obvious.
Exceptions may apply
_________________
Just use REFERENCEs |
|
| Back to top |
|
|
| dogorsy |
| Posted: Thu Jan 23, 2014 8:11 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
| mqsiuser wrote: |
| The time it takes to go into a message 4 times is about 4 times more than if you go through (with a reference) ONE time |
Is that so ? can you provide evidence ?
I do not support that view ( an neither performance tests ). |
|
| Back to top |
|
|
|
|
