Replace Particular XML tag with NULL value in Oracle SQL

Question

I have columns as VALUE in DUMMY table with type XMLTYPE. It contains:

  <?xml version="1.0"?>
  <ROWSET>
  <Value>
    <Data>802
     </Data>
  </Value>
  <Value>
    <Data>902
      </Data>
    </Value>
  </ROWSET>

I need to replace it with NULL for 802 value tag. The output should be :

  <?xml version="1.0"?>
   <ROWSET>
  <Value>
    <Data>902
     </Data>
  </Value>
  </ROWSET>

802 value tag should be removed with NULL.

I tried UpdateXML():

  update Dummy set VALUE=updatexml(VALUE,'ROWSET/Value/Data/text()','');

But it will update only 802 value with null.

2nd Approach: update Dummy set Value=updatexml(Value,'ROWSET','');

But it will delete everything inside ROWSET tag.Then,It will contain only :

<?xml version="1.0"?>
<ROWSET/>

I tried Replace() too.

     update Dummy set emps=replace('
      <Value><Data>802
      </Data></Value>',null);

Then it will remove other values from VALUE column and remain only the mentioned tag in replace().

After this replace(), It contains :

  <Value><Data>802
  </Data></Value>

Please suggest me on this.

score 2 · Accepted Answer · answered Jun 01 '17 at 08:56

You need

deleteXml() instead of updateXml(), and
a correct XPath for what's to be deleted.

Let's test our XPath first

with input$ as (
    select --+ no_merge
        xmltype(q'{<?xml version="1.0"?>
      <ROWSET>
      <Value>
        <Data>802
         </Data>
      </Value>
      <Value>
        <Data>902
          </Data>
        </Value>
      </ROWSET>}') as xx
    from dual
)
select
    xmlserialize(document X.xx indent) as original,
    xmlserialize(document
        deletexml(X.xx, '/ROWSET/Value[normalize-space(Data)="802"]')
        indent
    ) as with_802_removed
from input$ X;

... yields...

ORIGINAL               WITH_802_REMOVED
---------------------- ----------------------
<?xml version="1.0"?>  <?xml version="1.0"?>
<ROWSET>               <ROWSET>
  <Value>                <Value>
    <Data>802              <Data>902
         </Data>                 </Data>
  </Value>               </Value>
  <Value>              </ROWSET>
    <Data>902                                                                    
          </Data>                                                                
  </Value>                                                                       
</ROWSET>

Note: The xmlserialize() function is used here only for pretty-printing.

Now your update

update Dummy X
set X.value = deletexml(X.value, '/ROWSET/Value[normalize-space(Data)="802"]');

Note for Oracle 12c: There should be a more elegant solution to this using XQuery, but I was not able to get a full grasp on the XQuery language yet, hence I present you the deleteXml() solution only.

Replace Particular XML tag with NULL value in Oracle SQL

1 Answers1