Topic
  • 6 replies
  • Latest Post - ‏2011-04-27T04:52:32Z by MatthiasNicola
GerryKaplan
GerryKaplan
7 Posts

Pinned topic Need help with XQuery

‏2011-04-26T14:04:03Z |
I'm trying to create an update XQuery where one of the parameters is a piece of XML. Before the update occurs, that piece of XML needs to be modified. For example, the parameter parm1 may contain "<foo>bar</foo>" but I need to update it to include an attribute, such as "<foo id="1">bar</foo>" before inserting it into the main xml document. Here is what I've come up with, but I'm unable to get the modified parameter to work. It always just contains the original value.

In the sample below, I've switched over to using a "let" instead of a passed parm to try and troubleshoot this, but ultimately, the "parm1" would be passed in as a parameter from Java.

The basic logic is, first figure out what the max @id attribute value is, then increment it. Use that value for the new element's id.

UPDATE imweb.reports SET reportxml =
XMLQUERY('declare default element namespace "http://foobar.com/abc";
let $ids := $xml/imp-document/imp/@id
let $parm1 := <hello>this</hello>
let $nextId := if (count($ids) = 0) then (0) else (max($ids) + 1)
return
transform
copy
$newxml := $xml,
$newParm := $newParm
modify (
do insert attribute id {$nextId} into $newParm,
do insert $xp into $newxml/imp-document
)
return $newxml'
PASSING reportxml as "xml") WHERE id = 34

Any ideas?
Updated on 2011-04-27T04:52:32Z at 2011-04-27T04:52:32Z by MatthiasNicola
  • GerryKaplan
    GerryKaplan
    7 Posts

    Re: Need help with XQuery

    ‏2011-04-26T14:09:46Z  
    I made a mistake in one of the lines when I edited this for the forum. The line $newParm := $newParm should have been $newParm = $parm1.

    UPDATE imweb.reports SET reportxml =
    XMLQUERY('declare default element namespace "http://foobar.com/abc";
    let $ids := $xml/imp-document/imp/@id
    let $parm1 := <hello>this</hello>
    let $nextId := if (count($ids) = 0) then (0) else (max($ids) + 1)
    return
    transform
    copy
    $newxml := $xml,
    $newParm := $parm1
    modify (
    do insert attribute id {$nextId} into $newParm,
    do insert $xp into $newxml/imp-document
    )
    return $newxml'
    PASSING reportxml as "xml") WHERE id = 34
  • GerryKaplan
    GerryKaplan
    7 Posts

    Re: Need help with XQuery

    ‏2011-04-26T16:11:28Z  
    I made a mistake in one of the lines when I edited this for the forum. The line $newParm := $newParm should have been $newParm = $parm1.

    UPDATE imweb.reports SET reportxml =
    XMLQUERY('declare default element namespace "http://foobar.com/abc";
    let $ids := $xml/imp-document/imp/@id
    let $parm1 := <hello>this</hello>
    let $nextId := if (count($ids) = 0) then (0) else (max($ids) + 1)
    return
    transform
    copy
    $newxml := $xml,
    $newParm := $parm1
    modify (
    do insert attribute id {$nextId} into $newParm,
    do insert $xp into $newxml/imp-document
    )
    return $newxml'
    PASSING reportxml as "xml") WHERE id = 34
    I must have been truly braindead (possibly a result of looking at this too long). The final insert should read: do insert $newParm into $newxml/imp-document

    UPDATE imweb.reports SET reportxml =
    XMLQUERY('declare default element namespace "http://foobar.com/abc";
    let $ids := $xml/imp-document/imp/@id
    let $parm1 := <hello>this</hello>
    let $nextId := if (count($ids) = 0) then (0) else (max($ids) + 1)
    return
    transform
    copy
    $newxml := $xml,
    $newParm := $parm1
    modify (
    do insert attribute id {$nextId} into $newParm,
    do insert $newParm into $newxml/imp-document
    )
    return $newxml'
    PASSING reportxml as "xml") WHERE id = 34
  • MatthiasNicola
    MatthiasNicola
    322 Posts

    Re: Need help with XQuery

    ‏2011-04-26T16:56:41Z  
    I must have been truly braindead (possibly a result of looking at this too long). The final insert should read: do insert $newParm into $newxml/imp-document

    UPDATE imweb.reports SET reportxml =
    XMLQUERY('declare default element namespace "http://foobar.com/abc";
    let $ids := $xml/imp-document/imp/@id
    let $parm1 := <hello>this</hello>
    let $nextId := if (count($ids) = 0) then (0) else (max($ids) + 1)
    return
    transform
    copy
    $newxml := $xml,
    $newParm := $parm1
    modify (
    do insert attribute id {$nextId} into $newParm,
    do insert $newParm into $newxml/imp-document
    )
    return $newxml'
    PASSING reportxml as "xml") WHERE id = 34
    Hi Gerry,

    the issue with this "double update" is the following. If you perform multiple updates operations (such as 'do insert') in a single transform expression, then they are applied independently from each other and they do not affect each other. This is why your new piece of XML is inserted without the new id attribute.

    As another example of this issue, see the very last section "Combine multiple update operations" in this article:
    http://www.ibm.com/developerworks/data/library/techarticle/dm-0710nicola/

    The following might be a possible solution for your case, using to separate update expressions:

    
    CREATE TABLE imweb.reports(id INTEGER, reportxml XML)#   INSERT INTO imweb.reports VALUES(34, 
    '<imp-document xmlns="http://foobar.com/abc"> <imp id=
    "5"> <content>abc</content> </imp> <imp id=
    "6"> <content>xyz</content> </imp> </imp-document>
    ')#   UPDATE imweb.reports SET reportxml = XMLQUERY(
    'declare default element namespace "http://foobar.com/abc"; let $parm1 := document
    {<imp><hello>this</hello></imp>
    } let $nextId := 1 + max($REPORTXML/imp-document/imp/@id) let $parm1b := ( copy $newparm := $parm1 modify 
    
    do insert attribute id 
    {$nextId
    } into $newparm/imp 
    
    return $newparm )   let $new := ( copy $newxml := $REPORTXML modify 
    
    do insert $parm1b into $newxml/imp-document 
    
    return $newxml ) 
    
    return $new
    ')  WHERE id = 34   #
    


    Does this help?

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
  • GerryKaplan
    GerryKaplan
    7 Posts

    Re: Need help with XQuery

    ‏2011-04-26T23:18:51Z  
    Hi Gerry,

    the issue with this "double update" is the following. If you perform multiple updates operations (such as 'do insert') in a single transform expression, then they are applied independently from each other and they do not affect each other. This is why your new piece of XML is inserted without the new id attribute.

    As another example of this issue, see the very last section "Combine multiple update operations" in this article:
    http://www.ibm.com/developerworks/data/library/techarticle/dm-0710nicola/

    The following might be a possible solution for your case, using to separate update expressions:

    <pre class="jive-pre"> CREATE TABLE imweb.reports(id INTEGER, reportxml XML)# INSERT INTO imweb.reports VALUES(34, '<imp-document xmlns="http://foobar.com/abc"> <imp id= "5"> <content>abc</content> </imp> <imp id= "6"> <content>xyz</content> </imp> </imp-document> ')# UPDATE imweb.reports SET reportxml = XMLQUERY( 'declare default element namespace "http://foobar.com/abc"; let $parm1 := document {<imp><hello>this</hello></imp> } let $nextId := 1 + max($REPORTXML/imp-document/imp/@id) let $parm1b := ( copy $newparm := $parm1 modify do insert attribute id {$nextId } into $newparm/imp return $newparm ) let $new := ( copy $newxml := $REPORTXML modify do insert $parm1b into $newxml/imp-document return $newxml ) return $new ') WHERE id = 34 # </pre>

    Does this help?

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    I'll give it a try. It looks promising. Thanks for your help - your suggestion has also answered some other "curiosity" questions I've been wondering about with XQuery.
  • GerryKaplan
    GerryKaplan
    7 Posts

    Re: Need help with XQuery

    ‏2011-04-27T03:45:35Z  
    Hi Gerry,

    the issue with this "double update" is the following. If you perform multiple updates operations (such as 'do insert') in a single transform expression, then they are applied independently from each other and they do not affect each other. This is why your new piece of XML is inserted without the new id attribute.

    As another example of this issue, see the very last section "Combine multiple update operations" in this article:
    http://www.ibm.com/developerworks/data/library/techarticle/dm-0710nicola/

    The following might be a possible solution for your case, using to separate update expressions:

    <pre class="jive-pre"> CREATE TABLE imweb.reports(id INTEGER, reportxml XML)# INSERT INTO imweb.reports VALUES(34, '<imp-document xmlns="http://foobar.com/abc"> <imp id= "5"> <content>abc</content> </imp> <imp id= "6"> <content>xyz</content> </imp> </imp-document> ')# UPDATE imweb.reports SET reportxml = XMLQUERY( 'declare default element namespace "http://foobar.com/abc"; let $parm1 := document {<imp><hello>this</hello></imp> } let $nextId := 1 + max($REPORTXML/imp-document/imp/@id) let $parm1b := ( copy $newparm := $parm1 modify do insert attribute id {$nextId } into $newparm/imp return $newparm ) let $new := ( copy $newxml := $REPORTXML modify do insert $parm1b into $newxml/imp-document return $newxml ) return $new ') WHERE id = 34 # </pre>

    Does this help?

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    It works like a charm. Thanks so much. I notice that this doesn't use the transform statement now. I really like XQuery, but wonder how much it is being adopted. Do you know of any ways of using XQuery on in-memory documents without the need of a database behind it?
  • MatthiasNicola
    MatthiasNicola
    322 Posts

    Re: Need help with XQuery

    ‏2011-04-27T04:52:32Z  
    It works like a charm. Thanks so much. I notice that this doesn't use the transform statement now. I really like XQuery, but wonder how much it is being adopted. Do you know of any ways of using XQuery on in-memory documents without the need of a database behind it?
    Hi Gerry,

    I'm glad it works for you now.

    The statement that I provided is still using a transform expression, it's just that the keyword "transform" is optional and I omitted it for brevity.

    Note that I actually used two transform expressions, one to insert the ID attribute into your new XML piece, and another to insert that piece into the original document. Here I'm adding the "transform" keywords back in, so it becomes more obvious:

    
    UPDATE imweb.reports SET reportxml = XMLQUERY(
    'declare default element namespace "http://foobar.com/abc"; let $parm1 := document
    {<imp><hello>this</hello></imp>
    } let $nextId := 1 + max($REPORTXML/imp-document/imp/@id) let $parm1b := ( transform copy $newparm := $parm1 modify 
    
    do insert attribute id 
    {$nextId
    } into $newparm/imp 
    
    return $newparm ) let $new := ( transform copy $newxml := $REPORTXML modify 
    
    do insert $parm1b into $newxml/imp-document 
    
    return $newxml ) 
    
    return $new
    ')  WHERE id = 34   #
    

    >> I really like XQuery, but wonder how much it is being adopted.
    We see a lot of adoption of XQuery, often in conjunction with SQL (i.e. SQL/XML) but also XQuery by itself.
    >> Do you know of any ways of using XQuery on in-memory documents without the need of a database behind it?

    If you want to use XQuery on XML documents in your application layer without calling out to DB2 for the XQuery processing, this can be done with the XQuery support in WebSphere Application Server, specifically with the WebSphere XML Feature Pack. Here are some links where you can read about it:

    http://www-01.ibm.com/software/webservers/appserv/was/featurepacks/xml/
    http://www.ibm.com/developerworks/xml/library/x-xmlfeat1/index.html
    http://www.ibm.com/developerworks/library/x-xmlfeat2/index.html
    http://publib.boulder.ibm.com/infocenter/wasinfo/fep/topic/com.ibm.websphere.xmlfep.multiplatform.doc/info/ae/ae/welcome_fepxml.html
    http://www.ibm.com/developerworks/library/x-was7xmlfp/index.html

    Let me know if you're interested in exploring the WebSphere XQuery support in more detail and I can connect you to the right resources and people.

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/