DS. DS. - 1 month ago 18
SQL Question

Combining "Transform" and FLWOR expressions in Oracle XMLQuery

In the code below i'm trying to insert the "name" element from oldXml2 into oldXml AND update the "test" attribute value in oldXml to "newTest", the resulting combined output is newXml.
The code successfully changes the attribute value but, when I add in the:

let $newName := $b/users/user/name


clause to get the name element from oldXml2 it stops working.
Does anyone have a solution to this problem?

I'm using Oracle 11g, which uses xquery 1.0.

WITH myXml AS (select 1 id, xmltype(
'<users>
<user test="oldvalue">
<userid>id1</userid>
<name>dave</name>
</user>
</users>
'
) oldXml
from dual),
myXml2 AS (select 1 id,xmltype(
'<users>
<user>
<userid>id2</userid>
<name>steve</name>
</user>
</users>
'
) oldXml2
from dual)
SELECT oldXml,oldXml2,
XMLQuery(' copy $c := $a
(: let $newName := $b/users/user/name :) (: If you add this clause it doesnt work:)
modify ( rename node $c/users/user/@test as "newTest")
return $c'
PASSING a.oldXml as "a" ,b.oldXml2 as "b" RETURNING CONTENT) newXml
FROM myXml a
JOIN myXml2 b
ON a.id = b.id;


The required output in newXml is:

<users>
<user test="newTest">
<userid>id1</userid>
<name>dave</name>
</user>
<user>
<userid>id2</userid>
<name>steve</name>
</user>
</users>

Answer

You need to fo the let first and give it its own return clause, within which you do the copy/modify:

SELECT oldXml, oldXml2,
  XMLQuery('
    let $newName := $b/users/user/name
    return 
      copy $c := $a
      modify (rename node $c/users/user/@test as "newTest")
      return $c'
  PASSING a.oldXml as "a", b.oldXml2 as "b" RETURNING CONTENT) newXml
FROM myXml a
JOIN myXml2 b
ON a.id = b.id;

You can then refer to $newName within the copy/modify part, but it isn't clear how you want to use it; or why you want that variable instead of referring to $b/users/user/name directly, unless your real code will be in a for loop.


You don't need a let to do what you want, you can insert the $b/users/user node directly; with an XMLSerialize just to format the output:

SELECT oldXml, oldXml2,
  XMLSerialize(CONTENT XMLQuery('
    copy $c := $a
    modify (rename node $c/users/user/@test as "newTest",
      insert nodes $b/users/user after $c/users/user)
    return $c'
  PASSING a.oldXml as "a", b.oldXml2 as "b" RETURNING CONTENT)
  AS VARCHAR2(4000) INDENT SIZE=2) newXml
FROM myXml a
JOIN myXml2 b
ON a.id = b.id;

OLDXML                         OLDXML2                        NEWXML                        
------------------------------ ------------------------------ ------------------------------
<users>                        <users>                        <users>                       
  <user test="oldvalue">         <user>                         <user newTest="oldvalue">   
   <userid>id1</userid>           <userid>id2</userid>            <userid>id1</userid>      
   <name>dave</name>              <name>steve</name>              <name>dave</name>         
  </user>                        </user>                        </user>                     
</users>                       </users>                         <user>                      
                                                                  <userid>id2</userid>      
                                                                  <name>steve</name>        
                                                                </user>                     
                                                              </users>                      

You can use let if you add a return, as in the first part of the answer, but not sure it gains you anything in this example - maybe it's more useful in your real scenario - making the Xquery:

let $newUser := $b/users/user
return
  copy $c := $a
  modify (rename node $c/users/user/@test as "newTest",
    insert nodes $newUser after $c/users/user)
  return $c
Comments