Hi ,

I am trying to create an view over table having xml column as below

CREATE VIEW TQS_PR_PARTS_V ( "PR_SYS_ID", "FORM_ID", "MRN", "TRAC_ID", "TRAC_SN", "VISIT_NUMBER", "FACILITY", "LLP_LOCATION", “DISPLAY_DATE", "TRAC_DATE", "TRAC_TIME", "SUBMITTER" ) AS select B."PR_SYS_ID", B."FORM_ID", B."MRN",B."TRAC_ID", B."TRAC_SN", T.VISIT_NUMBER, T.FACILITY, T.LLP_LOCATION, T.DISPLAY_DATE,T.TRAC_DATE, T.TRAC_TIME, T.SUBMITTER from TQS.TQS_PRUMENT_INDEX_PARTS B, xmltable('db2-fn:xmlcolumn("TQS.TQS_PRUMENT_INDEX_PARTS.INDEX_PA RTS")/Index_Elements/DocIndex' COLUMNS "VISIT_NUMBER" CHAR(6) PATH 'VisitNumber', "FACILITY" CHAR(30) PATH Facility', "LLP_LOCATION" VARCHAR(100) PATH 'LLPLocation', "DISPLAY_DATE" DATE PATH 'DisplayDate', "TRAC_DATE" VARCHAR(10) PATH 'TracDate', "TRAC_TIME" VARCHAR(10) PATH 'TracTime', "SUBMITTER" Char(30) PATH 'Submitter') AS T

It says error as DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0010N The string constant beginning with "" Char(30) PATH 'Submitter')
AS T" does not have an ending string delimiter. SQLSTATE=42603.

But overall I have mentioned all the required delimiters and properly closed the brackets.


Also when i try running only teh XMLTABLe querry like

Select T.* from xmltable('db2-fn:xmlcolumn("TQS.TQS_PRUMENT_INDEX_PARTS.INDEX_PA RTS")/Index_Elements/DocIndex' COLUMNS "VISIT_NUMBER" CHAR(6) PATH 'VisitNumber', "FACILITY" CHAR(30) PATH Facility', "LLP_LOCATION" VARCHAR(100) PATH 'LLPLocation', "DISPLAY_DATE" DATE PATH 'DisplayDate', "TRAC_DATE" VARCHAR(10) PATH 'TracDate', "TRAC_TIME" VARCHAR(10) PATH 'TracTime', "SUBMITTER" Char(30) PATH 'Submitter') AS T.

i dont get any error but result shows 0 records which is even wrong , teh table is having more tahn 2000 rows with xml column populated.

Thanks in advance for all your preciuos answers.

Regards

You might forgot a quotation mark at
PATH Facility', "LLP_LOCATION" VARCHAR(100)

No its not the quotation mark missing, i had made teh correction and still i am getting the same error.
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0010N The string constant beginning with "" Char(30) PATH
'Submitter') AS T" does not have an ending string delimiter. SQLSTATE=42603.

I haven't used XML at all as yet, but i do see that you have 15 apostrophes('). Shouldn't there be an even number of them? I thinkk that is what Tonkuma was getting at with the line "FACILITY" CHAR(30) PATH Facility', shouldn't it be "FACILITY" CHAR(30) PATH 'Facility'
Dave Nance