Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2012
    Posts
    13

    Unanswered: cant use XMLTABLE in db2 i

    Hi, i can't seem to make this syntax work. Could anybody point out what I am missing. Am i dealing with version issues? Im using db2 i v7r1 (various articles are doing it on this version though)

    select *
    From xmlparse2,
    XMLTABLE ('$d/AddOSRItems/AddOSRItem' passing xmladdosrrequest as "d"
    COLUMNS
    ReqCode VARCHAR(10) PATH 'RequirementCode',
    PullDate NUMERIC(8,0) PATH 'PullDate',
    OrderedDate NUMERIC(8,0) PATH 'OrderedDate',
    Message VARCHAR(15) PATH 'Message'
    ) as X

    having token issues on this part : '$d/AddOSRItems/AddOSRItem'

    here's the xml on xmlparse2.xmladdosrrequest

    <AddOSRItems>
    <AddOSRItem>
    <RequirementCode>01</RequirementCode><PullDate>05/02/12</PullDate>
    <OrderedDate>04/02/12</OrderedDate><Message>Test</Message><Complexity>30</Complexity><RequiredOrPerson>REQUIRED
    </RequiredOrPerson></AddOSRItem>
    <AddOSRItem><RequirementCode>02</RequirementCode><PullDate>05/02/12</PullDate><OrderedDate>04/02/12</OrderedDate>
    <Message>Test</Message><Complexity>20</Complexity><RequiredOrPerson>INS</RequiredOrPerson>
    </AddOSRItem>
    </AddOSRItems>

    thank you so much

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What error message did you got?

    I found the following description in Informartion Center.
    What's new for IBM i 7.1
    Code:
    What's new as of April 2012
      Obfuscation for SQL procedures and SQL functions
      XMLTABLE table function
      INSERT where the target table and the select-statement reference different relational databases

  3. #3
    Join Date
    Jul 2012
    Posts
    13
    tonkuma, I think thats the problem too, since almost all i've read are using db2 v9 or has a technology refresh patch for v7. sad thats the only way I see how to process Xml.

    the error is : 'A syntax error was detected at token' and highlight this part : '$d/AddOSRItems/AddOSRItem'


    Do you have any other idea on how to select in an XML parameter in db2? i mean xml is available way before that update, there must have some other ways dba's and developer do.

    thanks

  4. #4
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    i mean xml is available way before that update
    Yes, xml was available for use, but was it available in db2 v7 earlier?

    I don't know - just asking.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    My idea is to convert XML data(column) to VARCHAR, then search tags.

    Here is an example tested on DB2 9.7.5 on Windows.
    You might want to adjust to iSeries syntax and/or your requirements.

    Though it might be unnecessary such concrete/deailed example, SQL programming is my fun.

    CREATE test table and populate it:
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE xmlparse2
    ( id SMALLINT NOT NULL
                  GENERATED ALWAYS AS IDENTITY
    , xmladdosrrequest XML
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO xmlparse2
    ( xmladdosrrequest )
    VALUES
    XMLPARSE( DOCUMENT
    '<AddOSRItems>
    <AddOSRItem>
    <RequirementCode>01</RequirementCode><PullDate>05/02/12</PullDate>
    <OrderedDate>04/02/12</OrderedDate><Message>Test</Message><Complexity>30</Complexity><RequiredOrPerson>REQUIRED
    </RequiredOrPerson></AddOSRItem>
    <AddOSRItem><RequirementCode>02</RequirementCode><PullDate>05/02/12</PullDate><OrderedDate>04/02/12</OrderedDate>
    <Message>Test</Message><Complexity>20</Complexity><RequiredOrPerson>INS</RequiredOrPerson>
    </AddOSRItem>
    </AddOSRItems>'
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.

    Query:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     items_varchar AS (
    SELECT id
         , XMLSERIALIZE( xmladdosrrequest AS VARCHAR(500) ) AS items
     FROM  xmlparse2
    )
    , find_item_position
    ( id , k , pos ) AS (
    SELECT id , 0 , 1
     FROM  xmlparse2
    UNION ALL
    SELECT pre.id
         , k + 1
         , COALESCE(
              NULLIF(
                 LOCATE('<AddOSRItem>' , items , pos + 1)
               , 0
              )
            , LENGTH(items) + 1
           )
     FROM  find_item_position pre
         , items_varchar      new
     WHERE pre.k   < 1000
       AND pre.pos < LENGTH(items)
       AND new.id  = pre.id
    )
    SELECT id
         , k
         , VARCHAR(
              SUBSTR(item , r_s , LOCATE('</RequirementCode>' , item , r_s) - r_s)
            , 10
           )  AS ReqCode
         , DATE(
              INSERT(
                 SUBSTR(item , p_s , LOCATE('</PullDate>'     , item , p_s) - p_s)
               , 7 , 0 , '20'
              )
           ) AS PullDate
         , DATE(
              INSERT(
                 SUBSTR(item , o_s , LOCATE('</OrderedDate>'  , item , o_s) - o_s)
               , 7 , 0 , '20'
              )
           ) AS OrderedDate
         , VARCHAR(
              SUBSTR(item , m_s , LOCATE('</Message>'         , item , m_s) - m_s)
            , 15
           ) AS Message
     FROM  (SELECT t.id
                 , s.k
                 , SUBSTR(items , s.pos , n.pos - s.pos) AS item
             FROM  items_varchar      t
             INNER JOIN
                   find_item_position s
              ON   s.id = t.id
               AND s.k  > 0
             INNER JOIN
                   find_item_position n
              ON   n.id = s.id
               AND n.k  = s.k + 1
           ) r
     CROSS JOIN
           LATERAL
           (VALUES
               (   LOCATE('<RequirementCode>' , item) + 17
                 , LOCATE('<PullDate>'        , item) + 10
                 , LOCATE('<OrderedDate>'     , item) + 13
                 , LOCATE('<Message>'         , item) +  9
               )
           ) q( r_s , p_s , o_s , m_s )
    ;
    ------------------------------------------------------------------------------
    
    ID     K           REQCODE    PULLDATE   ORDEREDDATE MESSAGE        
    ------ ----------- ---------- ---------- ----------- ---------------
         1           1 01         2012-05-02 2012-04-02  Test           
         1           2 02         2012-05-02 2012-04-02  Test           
    
      2 record(s) selected.

  6. #6
    Join Date
    Jul 2012
    Posts
    13
    Quote Originally Posted by papadi View Post
    Yes, xml was available for use, but was it available in db2 v7 earlier?

    I don't know - just asking.
    hi papadi,
    i believe yes, some articles about xml on v7 and older aversion was dated 5 years ago or so but I think they are using DB2's XML Extender - another utility and unfortunately I dont have it

  7. #7
    Join Date
    Jul 2012
    Posts
    13
    Quote Originally Posted by tonkuma View Post
    My idea is to convert XML data(column) to VARCHAR, then search tags.

    Here is an example tested on DB2 9.7.5 on Windows.
    You might want to adjust to iSeries syntax and/or your requirements.

    Though it might be unnecessary such concrete/deailed example, SQL programming is my fun.
    tonkuma, that was very detailed! I wish we have the same db2!

    do i just copy and paste your code directly, cause i successfully created the table and the data, but having the problem in the last code you provided, the token stops at this one with this error message

    The keyword NEW was not expected here. A syntax error was detected at keyword NEW.

    Code:
        
     FROM  find_item_position pre
         , items_varchar      new
    what's the 'new' syntax aims to do, so i can find a work around. tnx

  8. #8
    Join Date
    Jul 2012
    Posts
    13
    argh i think I found it db2 v7 needs extender to process XML. Sad, I guess I'll find another way without using XML. Thanks..

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    NEW is used as alias for items_varchar.

    ISO/ANSI SQL2003 and other DB2 database products include reserved words that
    are not enforced by DB2 Database for Linux, UNIX, and Windows.

    DB2i check more strictly those keywords.

    So, it might be worth to replace "new" with another non-reserved word like "iv".

    But, I hope you to try the following revised shorter query.

    Example 2:
    Code:
    WITH
     extract_item
    ( id , k , item , rest ) AS (
    SELECT id
         , 0
         , CAST('' AS VARCHAR(500) )
         , XMLSERIALIZE( xmladdosrrequest AS VARCHAR(500) )
     FROM  xmlparse2
    UNION ALL
    SELECT id
         , k + 1
         , SUBSTR(rest , pos_s , pos_e - pos_s )
         , SUBSTR(rest , pos_e + 13)
     FROM  (SELECT pre.*
                 , LOCATE( '<AddOSRItem>' , rest) + 12 AS pos_s
                 , LOCATE('</AddOSRItem>' , rest)      AS pos_e
             FROM  extract_item pre
             WHERE pre.k            < 1000
               AND LENGTH(pre.rest) > 14
           ) p
    )
    SELECT id
         , k
         , VARCHAR( SUBSTR(item , r_s , r_e - r_s) , 10 ) AS ReqCode
         , DATE(
              INSERT( SUBSTR(item , p_s , p_e - p_s) , 7 , 0 , '20' )
           ) AS PullDate
         , DATE(
              INSERT( SUBSTR(item , o_s , o_e - o_s) , 7 , 0 , '20' )
           ) AS OrderedDate
         , VARCHAR( SUBSTR(item , m_s , m_e - m_s) , 15 ) AS Message
     FROM  extract_item
     INNER JOIN
           LATERAL
           (VALUES
               (   LOCATE('<RequirementCode>' , item) + 17
                 , LOCATE('<PullDate>'        , item) + 10
                 , LOCATE('<OrderedDate>'     , item) + 13
                 , LOCATE('<Message>'         , item) +  9
               )
           ) q( r_s , p_s , o_s , m_s )
      ON   k > 0
     CROSS JOIN
           LATERAL
           (VALUES
               (   LOCATE('</RequirementCode>' , item , r_s)
                 , LOCATE('</PullDate>'        , item , p_s)
                 , LOCATE('</OrderedDate>'     , item , o_s)
                 , LOCATE('</Message>'         , item , m_s)
               )
           ) r( r_e , p_e , o_e , m_e )
     ORDER BY
           id , k
    ;

  10. #10
    Join Date
    Jul 2012
    Posts
    13
    Tonkuma, thanks for the code. Im have this error message on your last code.

    SQL State: 42836
    Vendor Code: -345
    Message: [SQL0345] Recursive common table expression EXTRACT_ITEM is not valid. Cause . . . . . : The recursive common table expression is not valid for one of the following reasons: -- The initialization fullselect of the common table expression cannot refer to itself. -- Grouping, aggregate functions, and ORDER BY are not allowed within the fullselects of the UNION that define the common table expression. -- EXCEPT DISTINCT is not allowed if the common table is specified as the right operand of a join. -- LEFT OUTER JOIN and FULL OUTER JOIN are not allowed if the common table is specified as the right operand of the join. -- RIGHT OUTER JOIN and FULL OUTER JOIN are not allowed if the common table is specified as the left operand of the join. -- The common table expression cannot be referenced more than once in a FROM clause and cannot be referenced in a subquery. Recovery . . . : Correct the recursive common table expression. Try the request again.

    but the first one worked!. had to change the naming conventions. thank you so much. so I guess i need to go all the trouble of long codes and must know before hand the XML format to be passed in order to create a code to cater it.

    in case youre interested on what im into, Im taking XML as parameter and individually perform insert and update statements on their new table/location

    thanks

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think my last code(Example 2) violates the following restriction.
    ... and cannot be referenced in a subquery. ...
    How about this?
    Code:
    ...
    UNION ALL
    SELECT id
         , k + 1
         , SUBSTR(
              rest
            , LOCATE( '<AddOSRItem>' , rest) + 12
            , LOCATE('</AddOSRItem>' , rest) - LOCATE( '<AddOSRItem>' , rest) - 12
           )
         , SUBSTR(
              rest
            , LOCATE('</AddOSRItem>' , rest) + 13
           )
     FROM  extract_item
     WHERE k            < 1000
       AND LENGTH(rest) > 14
    )
    ...

  12. #12
    Join Date
    Jul 2012
    Posts
    13
    tonkuma, applied the latest one but with error still.

    Code:
    WITH
     extract_item
    ( id , k , item , rest ) AS (
    SELECT id
         , 0
         , CAST('' AS VARCHAR(500) )
         , XMLSERIALIZE( xmladdosrrequest AS VARCHAR(500) )
     FROM  xmlparse2
    UNION ALL
    SELECT id
         , k + 1
         , SUBSTR(
              rest
            , LOCATE( '<AddOSRItem>' , rest) + 12
            , LOCATE('</AddOSRItem>' , rest) - LOCATE( '<AddOSRItem>' , rest) - 12
           )
         , SUBSTR(
              rest
            , LOCATE('</AddOSRItem>' , rest) + 13
           )
     FROM  extract_item
     WHERE k            < 1000
       AND LENGTH(rest) > 14
    )
    
    SELECT id
         , k
         , VARCHAR( SUBSTR(item , r_s , r_e - r_s) , 10 ) AS ReqCode
         , DATE(
              INSERT( SUBSTR(item , p_s , p_e - p_s) , 7 , 0 , '20' )
           ) AS PullDate
         , DATE(
              INSERT( SUBSTR(item , o_s , o_e - o_s) , 7 , 0 , '20' )
           ) AS OrderedDate
         , VARCHAR( SUBSTR(item , m_s , m_e - m_s) , 15 ) AS Message
     FROM  extract_item
     INNER JOIN
           LATERAL
           (VALUES
               (   LOCATE('<RequirementCode>' , item) + 17
                 , LOCATE('<PullDate>'        , item) + 10
                 , LOCATE('<OrderedDate>'     , item) + 13
                 , LOCATE('<Message>'         , item) +  9
               )
           ) q( r_s , p_s , o_s , m_s )
      ON   k > 0
     CROSS JOIN
           LATERAL
           (VALUES
               (   LOCATE('</RequirementCode>' , item , r_s)
                 , LOCATE('</PullDate>'        , item , p_s)
                 , LOCATE('</OrderedDate>'     , item , o_s)
                 , LOCATE('</Message>'         , item , m_s)
               )
           ) r( r_e , p_e , o_e , m_e )
     ORDER BY
           id , k
    ;
    errormessage
    Code:
    SQL State: 58004
    Vendor Code: -901
    Message: [SQL0901] SQL system error. Cause . . . . . :   An SQL system error has occurred.  
    The current SQL statement cannot be completed successfully. 
     The error will not prevent other SQL statements from being processed. 
    Previous messages may indicate that there is a problem with the SQL statement 
    and SQL did not correctly diagnose the error. The previous message identifier was CPF4204. 
    Internal error type 3002 has occurred. If precompiling, processing will not continue beyond this statement. 
    Recovery  . . . :   See the previous messages to determine if there is a problem with the SQL statement. 
    To view the messages, use the DSPJOBLOG command if running interactively, or the WRKJOB command to view the output of a precompile.
      An application program receiving this return code may attempt further SQL statements.  
    Correct any errors and try the request again.
    thanks

Tags for this Thread

Posting Permissions

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