CODEWITHSUNDEEP
xmlreplaceattributesdb2
SARAVANAN CHINNU
SARAVANAN CHINNU

Reputation: 59

replacing the one child node value which is occurred multiple places with in the xml

Need to replace the values 'OBLIGEE ATTORNEY' and 'saravanan' of child node <var..> by the value 'EDWIN CHAND'. THANKS IN ADVANCE Here is the query:

 SELECT     XMLQUERY ( 'copy $new := $d1 
   modify (do replace value of $new/root//var[@name=$element_name] with $d)
    return $new'
                      PASSING xmlparse(document '<root>
<first>                                                                               
   <var name="user_input_attn_obligee_desc">OBLIGEE ATTORNEY</var> 
</first> 
<second>
 <var name="user_input_attn_obligee_desc">saravanan</var> 
</second>   
<user_input_attn_obligor_desc>OBLIGOR ATTORNEY</user_input_attn_obligor_desc>                          
</root>') AS "d1",
                              'EDWIN CHAND' AS "d",
                               'user_input_attn_obligee_desc'AS "element_name") FROM SYSIBM.SYSDUMMY1;

Needed output:

'<root>
<first>                                                                               
   <var name="user_input_attn_obligee_desc">EDWIN CHAND</var> 
</first> 
<second>
 <var name="user_input_attn_obligee_desc">EDWIN CHAND</var> 
</second>   
<user_input_attn_obligor_desc>OBLIGOR ATTORNEY</user_input_attn_obligor_desc>                          
</root>'

But it throws the error like below (if xml have only one <var..> node then it works fine). ERROR [10703] [IBM][DB2/LINUXX8664] SQL16085N The target node of an XQuery "replace value of" expression is not valid. Error QName=err: "XUTY0008".

Upvotes: 0

Views: 229

Answers (1)

Mark Barinstein
Mark Barinstein

Reputation: 12314

SQL16085N, QName = err:XUTY0008

err:XUTY0008

The value of expression-type is "replace" or "replace value of" and the target node of a replace expression does not qualify any node, qualifies a sequence of two or more nodes, or qualifies a document node. It must qualify exactly one node that is not a document node.

You must iterate through the nodes, if you have multiple ones in a document.
Use the following instead:

SELECT 
XMLQUERY 
('
transform
copy $new := $d1 
modify 
for $xnode in $new/root//var[@name=$element_name] 
return do replace value of $xnode with $d
return $new
'
PASSING xmlparse
(document
'
<root>
  <first>                                                                               
    <var name="user_input_attn_obligee_desc">OBLIGEE ATTORNEY</var> 
  </first> 
  <second>
    <var name="user_input_attn_obligee_desc">saravanan</var> 
  </second>   
  <user_input_attn_obligor_desc>OBLIGOR ATTORNEY</user_input_attn_obligor_desc>                          
</root>
'
) AS "d1"
, 'EDWIN CHAND' AS "d"
, 'user_input_attn_obligee_desc' AS "element_name"
) 
FROM SYSIBM.SYSDUMMY1;

Upvotes: 1

Related Questions