Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2009
    Posts
    5

    Unanswered: Help needed in XML functions

    Kindly advise on what is going wrong in the below query :

    SELECT XMLSERIALIZE(
    CONTENT XMLELEMENT(
    NAME "TR",
    XMLELEMENT (NAME "TD", ACC.ACCTNUM),
    XMLELEMENT (NAME "TD", ACC.ACCT_OPN_DT),
    XMLELEMENT (NAME "TD", ACC.ACCT_CLS_DT),
    XMLELEMENT (NAME "TD", ACC.XCORRNUM)
    ) AS CHAR(200)
    ) AS ACCOUNT_DETAILS
    FROM UDB2PNAM.ACCT_TBL ACC
    WHERE ACCTNUM = '254856957'
    ;

    I am just trying to learn XML functions but the above query gives the error -
    SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD XMLELEMENT.
    AS ORDER WAS EXPECTED

    What is the mistake in the query. Help me figure it out.

    Thanks,
    Siddharth.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Your query works fine for me on DB2 LUW V9.5. Maybe you'll let us know which version of DB2 you are using on which platform?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What platform and DB2 version/release are you using?

    It worked on my DB2 Express-C 9.7 for Windows. Like this:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     ACCT_TBL(ACCTNUM, ACCT_OPN_DT, ACCT_CLS_DT, XCORRNUM) AS (
    VALUES
     ('124578157', DATE('2009-10-15'), DATE('2010-03-31'), 1013 )
    ,('254856957', DATE('2009-11-30'), DATE('2009-12-20'), 1092 )
    ,('078573165', DATE('2009-07-10'), DATE('2009-11-27'), 1107 )
    )
    SELECT XMLSERIALIZE( 
    CONTENT XMLELEMENT( 
    NAME "TR", 
    XMLELEMENT (NAME "TD", ACC.ACCTNUM), 
    XMLELEMENT (NAME "TD", ACC.ACCT_OPN_DT),
    XMLELEMENT (NAME "TD", ACC.ACCT_CLS_DT),
    XMLELEMENT (NAME "TD", ACC.XCORRNUM) 
    ) AS CHAR(200) 
    ) AS ACCOUNT_DETAILS 
    FROM ACCT_TBL ACC 
    WHERE ACCTNUM = '254856957' 
    ;
    ------------------------------------------------------------------------------
    
    ACCOUNT_DETAILS                                                                                                                                                                                         
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    <TR><TD>254856957</TD><TD>2009-11-30</TD><TD>2009-12-20</TD><TD>1092</TD></TR>                                                                                                                          
    
      1 record(s) selected.

  4. #4
    Join Date
    Dec 2009
    Posts
    5
    I am trying to execute this using DB2 Version 8 on z/OS

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Looking into and comparing "DB2 Version 9.1 for z/OS SQL Reference" and "DB2 Universal Database for z/OS Version 8 SQL Reference",
    XMLSERIALIZE is newly supported on DB2 Version 9.1 for z/OS.

    But, I thought that the error message you got is not directly related with that.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You can try XML2CLOB in DB2 v8 for z/OS, like this:
    (You will see that a comma is paired with a following XML-element-content in syntax diagram of XMLELEMENT.)
    (A comma is not stick to "NAME XML-element-name".)
    Code:
    SELECT XML2CLOB( 
              XMLELEMENT( 
                 NAME "TR"
               , XMLELEMENT (NAME "TD" , ACC.ACCTNUM)
               , XMLELEMENT (NAME "TD" , ACC.ACCT_OPN_DT)
               , XMLELEMENT (NAME "TD" , ACC.ACCT_CLS_DT)
               , XMLELEMENT (NAME "TD" , ACC.XCORRNUM) 
              )
           ) AS ACCOUNT_DETAILS 
      FROM ACCT_TBL ACC
     WHERE ACCTNUM = '254856957' 
    ;
    Last edited by tonkuma; 12-14-09 at 09:28.

  7. #7
    Join Date
    Dec 2009
    Posts
    5
    Thanks Tonkuma !! That worked !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •