Results 1 to 4 of 4
  1. #1
    Join Date
    May 2009
    Posts
    16

    Unanswered: Error in View creation on XML columns

    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

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You might forgot a quotation mark at
    PATH Facility', "LLP_LOCATION" VARCHAR(100)

  3. #3
    Join Date
    May 2009
    Posts
    16
    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.

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

Posting Permissions

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