| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
12-14-09, 01:20
|
|
Registered User
|
|
Join Date: Dec 2009
Posts: 5
|
|
|
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.
|
|
12-14-09, 02:51
|
|
Registered User
|
|
Join Date: Jan 2007
Location: Jena, Germany
Posts: 2,662
|
|
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
|
|
12-14-09, 02:54
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
|
|
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.
|
|
12-14-09, 03:38
|
|
Registered User
|
|
Join Date: Dec 2009
Posts: 5
|
|
I am trying to execute this using DB2 Version 8 on z/OS
|
|
12-14-09, 07:43
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
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.
|
|
12-14-09, 08:08
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
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 08:28.
|
12-14-09, 08:34
|
|
Registered User
|
|
Join Date: Dec 2009
Posts: 5
|
|
Thanks Tonkuma !! That worked !
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Help needed in XML functions
