Topic
2 replies Latest Post - ‏2012-08-07T19:13:13Z by Sigmazen
Sigmazen
Sigmazen
19 Posts
ACCEPTED ANSWER

Pinned topic Repeating tags to columns

‏2012-08-07T04:03:07Z |
Hi
I'm trying to get a single column list. The example uses a common-reference-table for ease of data, but when I try to put functions around it (eg the concat) I get the SQL16003N item() error.

WITH MY_TABLE(ID, XML) AS ( SELECT 1, XMLPARSE(DOCUMENT(
'<Offers><Offer>ABC</Offer><Offer>DEF</Offer><Offer>GHI</Offer></Offers>'))FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT 2, XMLPARSE(DOCUMENT(
'<Offers><Offer>MNO</Offer><Offer>PQR</Offer><Offer>STU</Offer></Offers>'))FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT 3, XMLPARSE(DOCUMENT(
'<Offers><Offer>FGH</Offer><Offer>PQR</Offer><Offer>STU</Offer></Offers>'))FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT 4, XMLPARSE(DOCUMENT(
'<Offers><Offer>FGH</Offer><Offer>IJK</Offer><Offer>LMO</Offer></Offers>'))FROM SYSIBM.SYSDUMMY1)   SELECT OFFER_CD FROM MY_TABLE AS B , XMLTABLE(
'$xml' passing B.XML as 
"xml" COLUMNS OFFER_CD           VARCHAR(20)   PATH 
'for $j in Offers return $j/Offer' )AS Y


but now if I put the additional concat function in I get an error:

COLUMNS OFFER_CD           VARCHAR(20)   PATH 
'concat("%", for $j in Offers return $j/Offer, "%")'
Updated on 2012-08-07T19:13:13Z at 2012-08-07T19:13:13Z by Sigmazen
  • MatthiasNicola
    MatthiasNicola
    309 Posts
    ACCEPTED ANSWER

    Re: Repeating tags to columns

    ‏2012-08-07T05:07:37Z  in response to Sigmazen
    The error you are hitting is because each argument to the fn:concat function must be an atomic value. But, the expression "for $j in Offers return $j/Offer" produces a sequence, and not an atomic value.

    Also see:
    http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.xml.doc/doc/xqrfncat.html

    Maybe this is what you wanted:

    
    WITH MY_TABLE(ID, XML) AS ( SELECT 1, XMLPARSE(DOCUMENT(
    '<Offers><Offer>ABC</Offer><Offer>DEF</Offer><Offer>GHI</Offer></Offers>'))FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT 2, XMLPARSE(DOCUMENT(
    '<Offers><Offer>MNO</Offer><Offer>PQR</Offer><Offer>STU</Offer></Offers>'))FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT 3, XMLPARSE(DOCUMENT(
    '<Offers><Offer>FGH</Offer><Offer>PQR</Offer><Offer>STU</Offer></Offers>'))FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT 4, XMLPARSE(DOCUMENT(
    '<Offers><Offer>FGH</Offer><Offer>IJK</Offer><Offer>LMO</Offer></Offers>'))FROM SYSIBM.SYSDUMMY1) SELECT OFFER_CD FROM MY_TABLE AS B , XMLTABLE(
    '$xml/Offers/Offer' passing B.XML as 
    "xml" COLUMNS OFFER_CD           VARCHAR(20)   PATH 
    'concat("%", . ,"%")' )AS Y ; OFFER_CD -------------------- %FGH% %PQR% %STU% %MNO% %PQR% %STU% %ABC% %DEF% %GHI% %FGH% %IJK% %LMO%   12 record(s) selected.
    


    Does this help?


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
  • Sigmazen
    Sigmazen
    19 Posts
    ACCEPTED ANSWER

    Re: Repeating tags to columns

    ‏2012-08-07T19:13:13Z  in response to Sigmazen
    Hi
    Perfect ... works a treat. Thanks a million for the super quick response
    Cheers