CODEWITHSUNDEEP
sqlreplacedb2xquery
Stella
Stella

Reputation: 11

Xquery Replace Statement for multiple nodes

This column (named docum_xml) in a DB2 Table has a xml structure like this:

    <member>
      <client1>
         <phone>510-000-0001</phone>
      </client1>
      <client2></client2>
      <client3>
         <phone>510-000-0002</phone>
      </client3>
      ...
    </member>

I want to do a xmlquery to get rid of all the potential dashes from tag. The Query I wrote was:

UPDATE TABLENAME
SET DOCUM_XML = XMLQUERY('
 TRANSFORM
 COPY $new := $documxml
 MODIFY(
 DO REPLACE VALUE OF $new//phone WITH fn:replace($documxml//phone,''-'',''''))
 RETURN $new' PASSING DOCUM_XML AS "documxml");

But it gave me error saying: SQL16003N An expression of data type "( item(), item()+ )" cannot be used when the data type "item()" is expected in the context.

I realized it errored out because I was trying to update multiple nodes, so I thought a for loop might be needed here. But I'm stuck in constructing the for loop and return the correct data back to my DB2 column. Can someone please help me out?

Thanks! Stella

Upvotes: 1

Views: 1547

Answers (1)

prker
prker

Reputation: 504

Try using this in the modify:

for $phone in $new//phone
return 
    replace value of node $phone
    with fn:replace($phone,''-'','''')

Upvotes: 1

Related Questions