I get the following problem when i tried to create the view using the following statement

CREATE OR REPLACE VIEW TAFJV_FBNK_CUSTOMER_POSITION as
SELECT a.RECID, a.XMLRECORD "THE_RECORD"
,a.RECID "ID"
,XMLCAST(XMLQUERY('$d/row/c1[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "DEAL_CCY"
,XMLCAST(XMLQUERY('$d/row/c2[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "DEAL_AMOUNT"
FROM
"FBNK_CUSTOMER_POSITION" a
LEFT OUTER JOIN "F_CATEGORY" b
ON b.RECID = XMLCAST(XMLQUERY('$d/row/c15[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000))

sqlcode: -338

sqlstate: 42972

The sqlcode says that An ON clause associated with a JOIN operator or in a MERGE statement is not valid .On futher analysis, i could find that XMLQuery function has the restriction that cannot be specified as a part of ON clause that is associated with a JOIN operator.

Is there any other way to perform outer join on XMLQuery function?

Try XMLTABLE() instead.

I tried to use the XMLTABLE in the on clause as follows

CREATE OR REPLACE VIEW TAFJV_FBNK_CUSTOMER_POSITION as
SELECT a.RECID, a.XMLRECORD "THE_RECORD"
,a.RECID "ID"
,XMLCAST(XMLQUERY('$d/row/c1[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "DEAL_CCY"
,XMLCAST(XMLQUERY('$d/row/c2[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "DEAL_AMOUNT"
FROM
"FBNK_CUSTOMER_POSITION" a
LEFT OUTER JOIN "F_CATEGORY" b
ON b.RECID = XMLTABLE('$d/row' passing a.XMLRECORD as "d"
COLUMNS
TEST VARCHAR(4000) PATH 'c2')

I get the SQL -104 error code that the statement is incorrect. Is it possible use select statement in a ON Clause?

That's not what I meant. You use XMLTABLE() to represent your XML data as a, well, table, then join that table with whatever you need on whatever column makes sense.

thanks for your reply.

Is it possible to do all these things in a single query. Can you please re write the above query?