Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
1 reply Latest Post - ‏2012-08-13T17:16:45Z by MatthiasNicola
SystemAdmin
SystemAdmin
230 Posts
ACCEPTED ANSWER

Pinned topic XMLTABLE query

‏2012-08-13T12:57:42Z |
Hi,

Here is the XMLDOCOUMENT.

<AccountOffer>
<offer>
<OfferCd>A</OfferCd>
<OfferSrcCd>AB</OfferSrcCd>
<OfferTypeCd>I</OfferTypeCd>
</offer>
<offer>
<OfferCd>B</OfferCd>
<OfferSrcCd>CD</OfferSrcCd>
<OfferTypeCd>Y</OfferTypeCd>
</offer>
</AccountOffer>

I want to return the "OfferCd" and "OfferSrcCd" only if "OfferTypeCd" = 'Y'.

So, I am trying to achieve this using XMLTABLE as mentioned below, but I am not able to see any result. I am not getting any error for the below code, but not getting the results back. The result-set is empty.


XMLTABLE(
'for $j in $D_PRFL/AccountOffer/offer where $j/OfferTypeCd=$CHNL_CD return $j/OfferCd' passing D.XML_TX as 
"D_PRFL", 
'Y' as 
"CHNL_CD" COLUMNS OFF_CD           VARCHAR(20)     PATH 
'.' )AS P_OFFER


Could anybody let me know what is missing in the above code. Many thanks.

Regards,
Tilak
Updated on 2012-08-13T17:16:45Z at 2012-08-13T17:16:45Z by MatthiasNicola
  • MatthiasNicola
    MatthiasNicola
    321 Posts
    ACCEPTED ANSWER

    Re: XMLTABLE query

    ‏2012-08-13T17:16:45Z  in response to SystemAdmin
    Hi Tilak,

    your XMLTABLE functions seems to work correctly and it does return the expected result. See the following examples which I ran in the DB2 Command Line Processor:

    
    create table mytable(doc XML);   insert into mytable values (
    ' <AccountOffer> <offer> <OfferCd>A</OfferCd> <OfferSrcCd>AB</OfferSrcCd> <OfferTypeCd>I</OfferTypeCd> </offer> <offer> <OfferCd>B</OfferCd> <OfferSrcCd>CD</OfferSrcCd> <OfferTypeCd>Y</OfferTypeCd> </offer> </AccountOffer>
    ');   SELECT P_OFFER.* FROM mytable, XMLTABLE(
    'for $j in $D_PRFL/AccountOffer/offer  where $j/OfferTypeCd=$CHNL_CD 
    
    return $j/OfferCd
    '   passing doc as 
    "D_PRFL", 
    'Y' as 
    "CHNL_CD" COLUMNS OFF_CD           VARCHAR(20)     PATH 
    '.') AS P_OFFER;     OFF_CD -------------------- B   1 record(s) selected.       SELECT P_OFFER.* FROM mytable, XMLTABLE(
    'for $j in $D_PRFL/AccountOffer/offer  where $j/OfferTypeCd=$CHNL_CD 
    
    return $j
    '   passing doc as 
    "D_PRFL", 
    'Y' as 
    "CHNL_CD" COLUMNS OFF_CD           VARCHAR(20)     PATH 
    'OfferCd', OFF_SRC          VARCHAR(20)     PATH 
    'OfferSrcCd') AS P_OFFER;   OFF_CD               OFF_SRC -------------------- -------------------- B                    CD   1 record(s) selected.     SELECT P_OFFER.* FROM mytable, XMLTABLE(
    '$DOC/AccountOffer/offer[OfferTypeCd=$CHNL_CD]' passing  
    'Y' as 
    "CHNL_CD" COLUMNS OFF_CD           VARCHAR(20)     PATH 
    'OfferCd', OFF_SRC          VARCHAR(20)     PATH 
    'OfferSrcCd') AS P_OFFER;   OFF_CD               OFF_SRC -------------------- -------------------- B                    CD   1 record(s) selected.
    


    Can you double-check these examples on your end?

    If you still see no result, please post the complete query and table definition.

    Thanks,

    Matthias


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/