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

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
    ACCEPTED ANSWER

    Re: Need help with XQuery

    ‏2011-04-26T14:09:46Z  in response to GerryKaplan
    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
      ACCEPTED ANSWER

      Re: Need help with XQuery

      ‏2011-04-26T16:11:28Z  in response to GerryKaplan
      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
        321 Posts
        ACCEPTED ANSWER

        Re: Need help with XQuery

        ‏2011-04-26T16:56:41Z  in response to GerryKaplan
        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/