Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
7 replies Latest Post - ‏2014-06-09T08:53:51Z by MatthiasNicola
aitkenhead
aitkenhead
5 Posts
ACCEPTED ANSWER

Pinned topic Double-slash at root element confusion

‏2014-06-06T17:57:27Z |

I am trying to develop a DB2 embedded XML subroutine whose purpose is "Given document A, a unique element named B of unknown depth within A, please insert element C as last into B" making use of FLWOR. My procedure works OK, as long as 'B' is not the root tag.

Here is the dynamic statement: 

XQuery copy $new := %s modify do insert %s as last into $new//%s return $new

In which the first %s is the old document, the second %s is the element to be inserted, and the third %s is the child element that should receive a new element within.

It works fine unless child == root. It traces back to the double-slash producing no results if child == root. But shouldn't this work too?

 

To put it another way, the first two if these produces one result each, but the third does not. In the Path evaluator of Altova XMLSpy produces one result.

XQuery <a><b><c/></b></a>//c (works, one result)

XQuery <a><b><c/></b></a>//b (works, one result)

XQuery <a><b><c/></b></a>//a (no result, but shouldn't it work?)

 

 

 

  • MatthiasNicola
    MatthiasNicola
    321 Posts
    ACCEPTED ANSWER

    Re: Double-slash at root element confusion

    ‏2014-06-07T00:08:41Z  in response to aitkenhead

    Hi...

    I didn't have a chance to test it out for your cases above, but I think the behavior that you observe is related to a missing document node. You should get the desired behavior if you use the document node constructor document{} to explicitly create an XML an document, like this:

    XQuery document{<a><b><c/></b></a>}//c

    XQuery document{<a><b><c/></b></a>}//b

    XQuery document{<a><b><c/></b></a>}//a

     

    The path expressions //a examines all descendants of the context node to look for occurrences of the element "a". 

    In the expression <a><b><c/></b></a>//a  the context node is the top level element <a> and the descendents are b and c. The is no "a" among the descendants of the context node and hence the result is empty.

    If you add a document node, i.e. document{<a><b><c/></b></a>}//a , then the context node is the document node and its descendants are the elements, a, b, and c. Then the expression //a finds an "a" among these descendents.

    Does that make sense?

    - Matthias

     

    • MatthiasNicola
      MatthiasNicola
      321 Posts
      ACCEPTED ANSWER

      Re: Double-slash at root element confusion

      ‏2014-06-07T05:05:29Z  in response to MatthiasNicola

      And for your original statement, maybe try this:

      XQuery copy $new := document{ %s } modify do insert %s as last into $new//%s return $new

       

      By the way, I suspect that the the insert location "as last into $new//%s" works in your case only because the element that you insert into exists only once in the document. If the target element has multiple occurrences, i.e. if the XPath $new//%s  produces more than one (or zero) nodes, then the operation fails with SQL16085N.

      - Matthias

  • aitkenhead
    aitkenhead
    5 Posts
    ACCEPTED ANSWER

    Re: Double-slash at root element confusion

    ‏2014-06-07T12:01:56Z  in response to aitkenhead

    OK, your suggestions had an interesitng outcome. Your code snippet works, when I type

    XQuery copy $new := document{ <a><b/></a> } modify do insert <c/> as last into $new//a return $new

    I do indeed get the desired result

    <a><b/><c/><a/>

    So that was great tip, thank you for that. That gets my development up and going again. I realize that I must guarantee the child element identified by double-dash is unique. No problem. The game I am playing is to take the elements of DB2 SQLCA and SQLDA structures and paste them into an XML document. 

    However, I do note that if I simply type at DB2 => command prompt

    Xquery document {<a><b/></a>//a}

    The cardinality of the result is 1, but what I see is just blank.

     

     

    • aitkenhead
      aitkenhead
      5 Posts
      ACCEPTED ANSWER

      Re: Double-slash at root element confusion

      ‏2014-06-07T12:07:31Z  in response to aitkenhead

      FInally, if I type at db2=> command prompt

      Xquery document{<a><b/></a>)//a

      then I get the desired result 

      <a><b/></a>

      • MatthiasNicola
        MatthiasNicola
        321 Posts
        ACCEPTED ANSWER

        Re: Double-slash at root element confusion

        ‏2014-06-08T11:20:08Z  in response to aitkenhead

        >> if I simply type at DB2 => command prompt Xquery document {<a><b/></a>//a} The cardinality of the result is 1, but what I see is just blank.

        Yes. This is expected. A single empty XML document is returned. The reason is that DB2 first evaluates the expression <a><b/></a>//a , which returns an empty sequence of nodes, and then an XML document node is wrapped around it. 

         

        >>Finally, if I type at db2=> command prompt Xquery document{<a><b/></a>}//a then I get the desired result <a><b/></a>

        Yes, this is want you want. You construct a document from <a><b/></a> and then the XPath //a is applied to that document. Hence, the XPath //a should be outside of the curly brackets, which produces the desired result.

         

        • aitkenhead
          aitkenhead
          5 Posts
          ACCEPTED ANSWER

          Re: Double-slash at root element confusion

          ‏2014-06-08T17:09:45Z  in response to MatthiasNicola

          Thank you for everything so far. It has put me back on my feet and I am coding again. I do have one last issue. Please have a look at he following. Keep in mind this is an iterative rewrite of the same XML document, and each time I execute the XQuery. This works as desired, but notice what I had to do in my C-language sprintf statement, using the strchr() function (will explain after code snippet) 

          EXE SQL BEGIN DECLARE SECTION;
          SQL TYPE IS XML AS CLOB(10K) xmlbuf;
          EXEC SQL DECLARE S1  STATEMENT;
          EXEC SQL DECLARE C1 CURSOR FOR S1;
          EXEC SQL END DECLARE SECTION;
          struct statement_
          { short length;
            char data [4096];
          } myStatement;
          EXEC SQL END DECLARE SECTION;
          for (i = 1; ; i++)
          { if (i == 0) myStatement = "XQuery return document {<root/>}";
            else if (child_tagname [i] == NULL) break; /* signal for done with loop */
            else myStatement = sprintf (myStatement,
            "XQuery copy $new := document{%s} modify do insert %s into $new//%s return $new",
            strchr (xmlbuf.data, '>') + 1, child_tagname [i], parent_tagname [i]);
            EXEC SQL PREPARE S1 from :myStatement;
            EXEC SQL OPEN C1;
            EXEC SQL FETCH C1 into :xmlout;
            EXEC SQL CLOSE C1;
            xmlout.data [xmlout.length] = 0;
          }

          The thing is, each iteration, the XQuery statement returns the XML document  into xmlbuf including the preamble <?xml version="1.0" encoding="UTF-8" ?>, and then when I re-feed the same XML document for the next loop, DB2 does not know what to do about the preamble. So, I need to strip it out the pre-amble before each iteration, which is why I invoke strchr (strchr gives me a pointer to the end of the preamble and then I go one byte past that).

          But this alludes to a bigger question. I think I would not have this trouble with the preamble, and the strategy would be more polished, if I were not doing this "sprintf"  stuff to make the Xquery statement...  using INPUT parameters instead if possible,  as if this were an ordinary DB2 cursor. In other words, I wish my cursor opens looked like this:

          EXEC SQL OPEN C1 USING :xmlbuf, :parent_tagname_string, :child_tagname_string;

           Is there a way to pass input parameters to my Xquery statement?

          Updated on 2014-06-08T17:15:24Z at 2014-06-08T17:15:24Z by aitkenhead
          • MatthiasNicola
            MatthiasNicola
            321 Posts
            ACCEPTED ANSWER

            Re: Double-slash at root element confusion

            ‏2014-06-09T08:53:51Z  in response to aitkenhead

            >>  Is there a way to pass input parameters to my XQuery statement?

            Yes, you can embed the XQuery in a SQL statement and than use SQL parameters or host variables similar to how you normally would. Your XQuery would be embedded in the SQL function XMLQUERY or XMLTABLE or XMLEXISTS.

            Below is an example that I found. Not a perfect match for your specific case, but maybe enough to convey the general idea. The embedded XQuery (here just a simple XPath) is shown in blue below. Note the use of the PASSING clause to pass column names (e.g. "info") or host variables into the XQuery by assigning them to XQuery variables (such as $i, $t, $c).

             

            EXEC SQL INCLUDE SQLCA;
            EXEC SQL BEGIN DECLARE SECTION;
              SQL TYPE IS XML AS BLOB(1K) myphone;
              CHAR city[20];
              CHAR phonetype[10];
            EXEC SQL END DECLARE SECTION;


            strcpy (city, "Berlin");
            strcpy (phonetype, "work");


            /* Declare a cursor for a SQL/XML query */
            EXEC SQL DECLARE cur1 CURSOR FOR

              SELECT XMLQUERY('$i/customerinfo/phone[@type= $t]' PASSING
                       info as "i", CAST(:phonetype AS INTEGER)as "t")
              FROM customer
              WHERE XMLEXISTS('$i/customerinfo[addr/city = $c]' PASSING
                        info as "i", CAST(:city AS VARCHAR(15))as "c");


            /* Open the cursor and fetch all rows */
            EXEC SQL OPEN cur1;
            while( sqlca.sqlcode == SQL_RC_OK )
            {
              EXEC SQL FETCH cur1 INTO :myphone;
              /* Consume and process the fetched phone elements here*/
            }
            EXEC SQL CLOSE cur1;

            - - - - -

            Also, if you wrap the SQL function XMLSERIALIZE around your XML result to return it for example as a BLOB, then there will be no XML declaration, i.e. no preamble about the XML version and encoding:

            SELECT XMLSERIALIZE( XMLQUERY (.... )   as CLOB) FROM...
             

            In this case you must fetch the result into a variable of type BLOB (or whatever type you specified in the XMLSERIALIZE). I don't think you can fetch it into an "XML AS LOB" variable anymore if you do this.

            - Matthias