| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| using SELECT to get XML tag |
« View previous topic :: View next topic » |
| Author |
Message
|
| miron |
 Posted: Thu Jun 27, 2002 6:53 am Post subject: using SELECT to get XML tag |
 |
|
Apprentice
Joined: 27 Jun 2002
Posts: 39
Location: New York
|
Hello,
I have two questions I hope somebody can help me with.
One is how to find out what is the subscript of a tag that satisfy my criteria without going thru the loop and whether
such tag exists at all. Example here would be to find out if there is KBETR within any COUNTRY and any SALESCHANNEL
that satisfy my criteria and also what (index) of KBETR field within let's say LAND1="DE" has value of '12590' .
In this xml example it is second one.
Second question is how to pull value of same field KBETR where attr.VTWEG="00" and DATBI="99991231"
and UPD_IND(if exists) in ('','I','U') and assign it to my variable?
I tried this esql but can not get it to work. I am getting this error:
BIP2406E: (27, 99) : Syntax error : cannot specify '[]' in the middle of a field reference.
It looks like i have to specify subscript of <SALESCHANNEL>.
Thanks.Miron
I use WMQI2.1 , CSD02.
loop thru <COUNTRY> with R...
set TEMP1 =
THE (select BTR.KBETR from InputRoot.XML.PRICEDATA.COUNTRY[R].SALESCHANNEL[] as BTR
where InputRoot.XML.PRICEDATA.COUNTRY[R].SALESCHANNEL[].(XML.attr)VTWEG ="00"
and InputRoot.XML.PRICEDATA.COUNTRY[R].SALESCHANNEL[].KBETR.(XML.attr)DATBI = "99991231"
and InputRoot.XML.PRICEDATA.COUNTRY[R].SALESCHANNEL[].KBETR.(XML.attr)KSCHL = "ZR00"
and coalesce(InputRoot.XML.PRICEDATA.COUNTRY[R].SALESCHANNEL[].KBETR.(XML.attr)UPD_IND,' ') in (' ','U','I'));
<PRICEDATA>
<COUNTRY LAND1="AT">
<SALESCHANNEL VTWEG="00">
<KBETR DATAB="19981026" DATBI="19990128" WAERS="ATS" KSCHL="ZR00" KMEIN="ST">102300</KBETR>
<KBETR DATAB="19990129" DATBI="99991231" WAERS="ATS" KSCHL="ZR00" KMEIN="ST" UPD_IND="D">88490</KBETR>
</SALESCHANNEL>
<SALESCHANNEL VTWEG="J">
<KBETR DATAB="20010307" DATBI="99991231" WAERS="GBP" KSCHL="ZR00" KMEIN="ST">300</KBETR>
</SALESCHANNEL>
</COUNTRY>
<COUNTRY LAND1="DE">
<SALESCHANNEL VTWEG="00">
<KBETR DATAB="19981026" DATBI="19990128" WAERS="DEM" KSCHL="ZR00" KMEIN="ST">13110</KBETR>
<KBETR DATAB="19990129" DATBI="19990705" WAERS="DEM" KSCHL="ZR00" KMEIN="ST">12590</KBETR>
<KBETR DATAB="19990706" DATBI="99991231" WAERS="DEM" KSCHL="ZR00" KMEIN="ST" UPD_IND="U">12585</KBETR>
</SALESCHANNEL>
</COUNTRY>
<COUNTRY LAND1="GB">
<SALESCHANNEL VTWEG="00">
<KBETR DATAB="19981026" DATBI="20010320" WAERS="GBP" KSCHL="ZR00" KMEIN="ST">4356</KBETR>
<KBETR DATAB="20010321" DATBI="99991231" WAERS="GBP" KSCHL="ZR00" KMEIN="ST">300</KBETR>
</SALESCHANNEL>
<SALESCHANNEL VTWEG="J">
<KBETR DATAB="20010307" DATBI="99991231" WAERS="GBP" KSCHL="ZR00" KMEIN="ST">300</KBETR>
</SALESCHANNEL>
</COUNTRY>
</PRICEDATA> |
|
| Back to top |
|
 |
| CodeCraft |
| Posted: Fri Jun 28, 2002 6:04 am Post subject: |
|
|
Disciple
Joined: 05 Sep 2001
Posts: 195
|
| Miron, you might give the group the benefit of my response on this. Someone may correct or embellish the response. |
|
| Back to top |
|
|
| miron |
| Posted: Fri Jun 28, 2002 7:05 am Post subject: using SELECT to get XML tag |
|
|
Apprentice
Joined: 27 Jun 2002
Posts: 39
Location: New York
|
here is a response from Carl to my post.
Any comments are welcome....
RE: Mirons Question On SELECT Statement
On investigation it appears you cannot do this with one select statement. The fundamental problem is that you have a nested structure, wheras ESQL acts a bit like SQL looking for a tabular structure, so given:
PRICEDATA
COUNTRY
SALESCHANNEL
KBETR
We can perform operations on COUNTRY[], or SALESCHANNEL[] or KBETR[] as tables, but not for instance on KBETR[] referencing SALESCHANNEL[] because they are at different depths in the hierarchy. What looks to be happening in this is an attempt to join SALESCHANNEL[] to KBETR[] and this produces unpredicatable results because there are no keys, and, because ESQL joins seem to produce wierd results anyway!
I've also tried using subselects, but, where you use subselects to access a nested source, the result of the subselect will also be nested, which is not what you want. (For each subselect you need to specify for the target as an As clause, and the As of a subselect becomes a child of the result of it's parent).
I believe the get a flat KBETR[] result, from a nested XML source, you will need to iterate over the source using a loop. You could try looking at the FOR ALL construct in the ESQL manual for a nice way of doing this.
For your information, here is a sample ESQL and trace which shows how to pull all of the KBETR items from the hierarchy using SubSelects. I have not applied the selection criteria: The main trick is to be able to reference all KBETRs, after that, it should easily be possible to tailor the selects to pick only the KBETRs, SALESCHANNELs and COUNTRYs you want. Because the result is nested, you will still need to be able to navigate the hiearchy using a loop, but, it may be more efficient for you to filter the result using a SELECT provided (with added where clauses), and then navigrate the (limited) result, instead of looping over the various levels of the full input source.
<PRICEDATA>
<COUNTRY LAND1="AT">
<SALESCHANNEL VTWEG="00">
   <KBETR DATAB="19981026" DATBI="19990128" WAERS="ATS" KSCHL="ZR00" KMEIN="ST">102300</KBETR>
     <KBETR DATAB="19990129" DATBI="99991231" WAERS="ATS" KSCHL="ZR00" KMEIN="ST" UPD_IND="D">88490</KBETR>
   </SALESCHANNEL>
   <SALESCHANNEL VTWEG="J">
    <KBETR DATAB="20010307" DATBI="99991231" WAERS="GBP" KSCHL="ZR00" KMEIN="ST">300</KBETR>
   </SALESCHANNEL>
</COUNTRY>
<COUNTRY LAND1="DE">
  <SALESCHANNEL VTWEG="00">
   <KBETR DATAB="19981026" DATBI="19990128" WAERS="DEM" KSCHL="ZR00" KMEIN="ST">13110</KBETR>
     <KBETR DATAB="19990129" DATBI="19990705" WAERS="DEM" KSCHL="ZR00" KMEIN="ST">12590</KBETR>
     <KBETR DATAB="19990706" DATBI="99991231" WAERS="DEM" KSCHL="ZR00" KMEIN="ST" UPD_IND="U">12585</KBETR>
   </SALESCHANNEL>
</COUNTRY>
<COUNTRY LAND1="GB">
  <SALESCHANNEL VTWEG="00">
   <KBETR DATAB="19981026" DATBI="20010320" WAERS="GBP" KSCHL="ZR00" KMEIN="ST">4356</KBETR>
     <KBETR DATAB="20010321" DATBI="99991231" WAERS="GBP" KSCHL="ZR00" KMEIN="ST">300</KBETR>
   </SALESCHANNEL>
   <SALESCHANNEL VTWEG="J">
     <KBETR DATAB="20010307" DATBI="99991231" WAERS="GBP" KSCHL="ZR00" KMEIN="ST">300</KBETR>
   </SALESCHANNEL>
</COUNTRY>
</PRICEDATA>
Set OutputLocalEnvironment.TempResult[] =
( Select
( Select
( Select Z.KBETR[]
As DEEP3.KBETR[]
From Y.SALESCHANNEL[] As Z
)
As DEEP2.KBETR[]
From X.COUNTRY[] As Y
)
As DEEP1.KBETR[]
From InputRoot.XML.PRICEDATA[] As X
);
(0x1000000)TempResult = (
(0x1000000)DEEP1 = (
(0x1000000)KBETR = (
(0x1000000)DEEP2 = (
(0x1000000)KBETR = (
(0x1000000)DEEP3 = (
(0x3000000)KBETR = '102300'
(0x3000000)KBETR = '88490'
)
)
(0x1000000)KBETR = (
(0x1000000)DEEP3 = (
(0x3000000)KBETR = '300'
)
)
)
)
(0x1000000)KBETR = (
(0x1000000)DEEP2 = (
(0x1000000)KBETR = (
(0x1000000)DEEP3 = (
(0x3000000)KBETR = '13110'
(0x3000000)KBETR = '12590'
(0x3000000)KBETR = '12585'
)
)
)
)
(0x1000000)KBETR = (
(0x1000000)DEEP2 = (
(0x1000000)KBETR = (
(0x1000000)DEEP3 = (
(0x3000000)KBETR = '4356'
(0x3000000)KBETR = '300'
)
)
(0x1000000)KBETR = (
(0x1000000)DEEP3 = (
(0x3000000)KBETR = '300'
)
)
)
)
)
)
) |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
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
|
|
|
|
