Executing XQuery expressions in embedded SQL applications

• An XML type must be used to retrieve XML values in a static SQL SELECT INTO statement.
• If a CHAR, VARCHAR, CLOB, or BLOB host variable is used for input where an XML value is expected, the value will be subject to an XMLPARSE function operation with default white space (STRIP) handling. Otherwise, an XML host variable is required.

To issue XQuery expressions in embedded SQL application directly, prepend the expression with the "XQUERY" keyword. For static SQL use the XMLQUERY function. When the XMLQUERY function is called, the XQuery expression is not prefixed by "XQUERY".

These examples return data from the XML documents in table CUSTOMER from the sample database.

Example 1: Executing XQuery expressions directly in C and C++ dynamic SQL by prepending the "XQUERY" keyword

In C and C++ applications, XQuery expressions can be issued in the following way:

    EXEC SQL INCLUDE SQLCA;
    EXEC SQL BEGIN DECLARE SECTION;
      char stmt[16384];
      SQL TYPE IS XML AS BLOB( 10K ) xmlblob;
    EXEC SQL END DECLARE SECTION;

    sprintf( stmt, "XQUERY (for $a in db2-fn:xmlcolumn("CUSTOMER.INFO")
            /*:customerinfo[*:addr/*:city = "Toronto"]/@Cid return data($a))");

    EXEC SQL PREPARE s1 FROM :stmt;
    EXEC SQL DECLARE c1 CURSOR FOR s1;
    EXEC SQL OPEN c1;

    while( sqlca.sqlcode == SQL_RC_OK )
    {
      EXEC SQL FETCH c1 INTO :xmlblob;
      /* Display results */
    }

    EXEC SQL CLOSE c1;
    EXEC SQL COMMIT;
   

Example 2: Executing XQuery expressions in static SQL using the XMLQUERY function and XMLEXISTS predicate

SQL statements containing the XMLQUERY function can be prepared statically, as follows:

    EXEC SQL INCLUDE SQLCA;
    EXEC SQL BEGIN DECLARE SECTION;
      SQL TYPE IS XML AS BLOB( 10K ) xmlblob;
    EXEC SQL END DECLARE SECTION;

    EXEC SQL DECLARE C1 CURSOR FOR SELECT XMLQUERY(data($INFO/*:customerinfo/@Cid)') 
    FROM customer 
    WHERE XMLEXISTS('$INFO/*:customerinfo[*:addr/*:city = "Toronto"]');

    EXEC SQL OPEN c1;

    while( sqlca.sqlcode == SQL_RC_OK )
    {
      EXEC SQL FETCH c1 INTO :xmlblob;
      /* Display results */
    }

    EXEC SQL CLOSE c1;
    EXEC SQL COMMIT;
  

Example 3: Executing XQuery expressions in COBOL embedded SQL applications

In COBOL applications, XQuery expressions can be issued in the following way:

    EXEC SQL BEGIN DECLARE SECTION END-EXEC.
         01 stmt pic x(80).
    	  01 xmlBuff USAGE IS SQL TYPE IS XML AS BLOB (10K).
    EXEC SQL END DECLARE SECTION END-EXEC.

    MOVE "XQUERY (for $a in db2-fn:xmlcolumn("CUSTOMER.INFO")/*:customerinfo
                 [*:addr/*:city = "Toronto"]/@Cid return data($a)))" TO stmt.
    EXEC SQL PREPARE s1 FROM :stmt END-EXEC.
    EXEC SQL DECLARE c1 CURSOR FOR s1 END-EXEC.
    EXEC SQL OPEN c1 USING :host-var END-EXEC.

    *Call the FETCH and UPDATE loop.
    Perform Fetch-Loop through End-Fetch-Loop
       until SQLCODE does not equal 0.

    EXEC SQL CLOSE c1 END-EXEC.
    EXEC SQL COMMIT END-EXEC.

    Fetch-Loop Section.
        EXEC SQL FETCH c1 INTO :xmlBuff END-EXEC.
        if SQLCODE not equal 0
           go to End-Fetch-Loop.
    * Display results
    End-Fetch-Loop. exit.