cant use XMLTABLE in db2 i

marlu · #1 (**permalink**) 08-10-12, 07:02

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

tonkuma · #2 (**permalink**) 08-10-12, 09:12

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

marlu · #3 (**permalink**) 08-10-12, 10:30

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

papadi · #4 (**permalink**) 08-10-12, 14:36

Quote:

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.

tonkuma · #5 (**permalink**) 08-10-12, 19:50

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.

marlu · #6 (**permalink**) 08-13-12, 02:27

Quote:

Originally Posted by papadi

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

marlu · #7 (**permalink**) 08-13-12, 02:34

Quote:

Originally Posted by tonkuma

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

marlu · #8 (**permalink**) 08-13-12, 03:38

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..

tonkuma · #9 (**permalink**) 08-13-12, 04:02

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
;

marlu · #10 (**permalink**) 08-13-12, 05:56

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

tonkuma · #11 (**permalink**) 08-13-12, 06:44

I think my last code(Example 2) violates the following restriction.

Quote:

... 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
)
...

marlu · #12 (**permalink**) 08-14-12, 03:08

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

Internet Services

Web Development

Digital Marketing

Consumer Tech