| Author |
Message
|
| jgooch |
 Posted: Wed Feb 19, 2003 4:15 am Post subject: SELECT on input tree. |
 |
|
Acolyte
Joined: 29 May 2002
Posts: 63
Location: UK
|
Folks,
I'm trying to run a SELECT against an XML document. However it only seems to work where the one that I want is the first one in the group.
The XML looks like:-
<message>
...snip...
<instrument>
<identifier system="SEDOL">3056044</identifier>
<identifier system="ISIN">CH0012056047</identifier>
<identifier system="CUSIP">dummy</identifier>
</instrument>
...snip...
</message>
The ESQL currently looks like:-
SET temp = THE (
SELECT
ITEM S.identifier
FROM
InputRoot.XML.message.instrument[] AS S
WHERE
S.identifier.(XML.attr)system = 'SEDOL'
);
I've tried it with and without ITEM/THE but with no luck. A trace returns the following clue:-
2003-02-19 12:45:43.056836 7 UserTrace BIP2572W: Node: 'AXAROS.POSITIONS.XML.RECON.Convert to DB XML': (42, 1 : Finding one and only SELECT result.
Can anyone see why the identifier list is not being made available to the SELECT?
We're running v2.0.1 on Solaris (soon to be upgraded to v2.1, we hope!).
Thanks,
J. |
|
| Back to top |
|
 |
| yaakovd |
| Posted: Thu Feb 20, 2003 12:33 am Post subject: |
|
|
Partisan
Joined: 20 Jan 2003
Posts: 319
Location: Israel
|
This code worked on AIX machine with your XML.
| Quote: |
SET "OutputRoot"."MQMD" = "InputRoot"."MQMD";
SET "OutputRoot"."MQMD"."Format" = 'MQSTR';
SET OutputRoot.Properties.MessageFormat = 'XML';
DECLARE temp INTEGER; --CHAR also working
SET temp = THE ( SELECT ITEM S.identifier FROM InputRoot.XML.message.instrument[] AS S WHERE
S.identifier.(XML.attr)system = 'SEDOL' );
SET OutputRoot.XML."D"."a" = 'hghagsdhja';
SET OutputRoot.XML."D"."b" = temp; |
_________________
Best regards.
Yaakov
SWG, IBM Commerce, Israel |
|
| Back to top |
|
|
| jgooch |
| Posted: Thu Feb 20, 2003 1:12 am Post subject: |
|
|
Acolyte
Joined: 29 May 2002
Posts: 63
Location: UK
|
OK perhaps my example was flawed (or at best slightly misleading).
It worked because SEDOL was the first one in the group.
Try changing the WHERE clause to S.identifier.(XML.attr)system = 'ISIN' (or change the XML to have the SEDOL identifier tag second).
Apologies for the confusion. Can you try again (with the above change), please? I think you'll find that the SELECT will return null.
Ta,
J. |
|
| Back to top |
|
|
| yaakovd |
| Posted: Thu Feb 20, 2003 2:14 am Post subject: |
|
|
Partisan
Joined: 20 Jan 2003
Posts: 319
Location: Israel
|
Correct - it takes first element
Use following ( I don't know if "instrument" also repeating):
| Quote: |
DECLARE I INTEGER;
SET I=1;
WHILE I < CARDINALITY("InputRoot"."XML"."message"."instrument"."identifier"[]) DO
IF ("InputRoot"."XML"."message"."instrument"."identifier"[I].(XML.attr)system = 'SEDOL' ) THEN
SET temp = "InputRoot"."XML"."message"."instrument"."identifier"[I];
END IF;
SET I=I+1;
END WHILE; |
_________________
Best regards.
Yaakov
SWG, IBM Commerce, Israel |
|
| Back to top |
|
|
| jgooch |
| Posted: Thu Feb 20, 2003 2:24 am Post subject: |
|
|
Acolyte
Joined: 29 May 2002
Posts: 63
Location: UK
|
That doesn't look very efficient to me. What if there were many elements within the group?
The documentation claims that you can run SELECT statements against input trees. This seems invalidated if it only works in cases where the element you are after happens to occur first in the group.
Perhaps it's a "feature" of MQSI...
J. |
|
| Back to top |
|
|
| yaakovd |
| Posted: Thu Feb 20, 2003 3:42 am Post subject: |
|
|
Partisan
Joined: 20 Jan 2003
Posts: 319
Location: Israel
|
use:
| Quote: |
SET temp = THE( SELECT ITEM S FROM InputRoot.XML.message.instrument.identifier[] AS S WHERE
S.(XML.attr)system = 'SEDOL');
|
I think you must decide wich element is repeating in your structure: instrument or identifier.
_________________
Best regards.
Yaakov
SWG, IBM Commerce, Israel
Last edited by yaakovd on Thu Feb 20, 2003 4:25 am; edited 1 time in total |
|
| Back to top |
|
|
| jgooch |
| Posted: Thu Feb 20, 2003 4:13 am Post subject: |
|
|
Acolyte
Joined: 29 May 2002
Posts: 63
Location: UK
|
That's the ticket!
Thanks for the help. |
|
| Back to top |
|
|
|
|
