Skip to content
Advertisement

Delete node when child have a certain value is found

I intend to remove a node whenever I find the unit_qty tag with the value equal to 0.0000. For example considering that the message below is called MESSAGE_DATA:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ReceiptDesc xmlns="http //www.w3.org/2000/svg">
    <appt_nbr>0</appt_nbr>
    <Receipt>
        <dc_dest_id>ST</dc_dest_id>
        <po_nbr>1232</po_nbr>
        <document_type>T</document_type>
        <asn_nbr>0033</asn_nbr>
        <ReceiptDtl>
            <item_id>100233127</item_id>
            <unit_qty>0.0000</unit_qty>
            <user_id>EXTERNAL</user_id>
            <shipped_qty>6.0000</shipped_qty>
        </ReceiptDtl>
        <from_loc>WH</from_loc>
        <from_loc_type>W</from_loc_type>
    </Receipt>
        <Receipt>
        <dc_dest_id>ST</dc_dest_id>
        <po_nbr>1233</po_nbr>
        <document_type>T</document_type>
        <asn_nbr>0033</asn_nbr>
        <ReceiptDtl>
            <item_id>355532244</item_id>
            <unit_qty>2.0000</unit_qty>
            <user_id>EXTERNAL</user_id>
            <shipped_qty>2.0000</shipped_qty>
        </ReceiptDtl>
        <from_loc>WH</from_loc>
        <from_loc_type>W</from_loc_type>
    </Receipt>
    <Receipt>
        <dc_dest_id>ST</dc_dest_id>
        <po_nbr>1234</po_nbr>
        <document_type>T</document_type>
        <asn_nbr>0033</asn_nbr>
        <ReceiptDtl>
            <item_id>54480</item_id>
            <unit_qty>0.0000</unit_qty>
            <user_id>EXTERNAL</user_id>
            <shipped_qty>6.0000</shipped_qty>
        </ReceiptDtl>
        <from_loc>WH</from_loc>
        <from_loc_type>W</from_loc_type>
    </Receipt>
    <Receipt>
        <dc_dest_id>ST</dc_dest_id>
        <po_nbr>1235</po_nbr>
        <document_type>T</document_type>
        <asn_nbr>0033</asn_nbr>
        <ReceiptDtl>
            <item_id>38760</item_id>
            <unit_qty>8.0000</unit_qty>
            <user_id>EXTERNAL</user_id>
            <shipped_qty>8.0000</shipped_qty>
        </ReceiptDtl>
        <from_loc>WH</from_loc>
        <from_loc_type>W</from_loc_type>
    </Receipt>
</ReceiptDesc>

the purpose of message_data is to return the following:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ReceiptDesc xmlns="http //www.w3.org/2000/svg">
    <appt_nbr>0</appt_nbr>
    <Receipt>
        <dc_dest_id>ST</dc_dest_id>
        <po_nbr>1233</po_nbr>
        <document_type>T</document_type>
        <asn_nbr>0033</asn_nbr>
        <ReceiptDtl>
            <item_id>355532244</item_id>
            <unit_qty>2.0000</unit_qty>
            <user_id>EXTERNAL</user_id>
            <shipped_qty>2.0000</shipped_qty>
        </ReceiptDtl>
        <from_loc>WH</from_loc>
        <from_loc_type>W</from_loc_type>
    </Receipt>
    <Receipt>
        <dc_dest_id>ST</dc_dest_id>
        <po_nbr>1235</po_nbr>
        <document_type>T</document_type>
        <asn_nbr>0033</asn_nbr>
        <ReceiptDtl>
            <item_id>38760</item_id>
            <unit_qty>8.0000</unit_qty>
            <user_id>EXTERNAL</user_id>
            <shipped_qty>8.0000</shipped_qty>
        </ReceiptDtl>
        <from_loc>WH</from_loc>
        <from_loc_type>W</from_loc_type>
    </Receipt>
</ReceiptDesc>

I’ve already tried the following options:

select  DELETEXML(xmltype(MESSAGE_DATA),'//Receipt/ReceiptDtl/unit_qty[text()="0.0000"]')from dual;

with XML_TABLE as 
(
   select XMLTYPE(MESSAGE_DATA) as XML_COLUMN from dual
)
SELECT XMLQuery(
    'copy $NEWXML := $XML modify (
      delete nodes $NEWXML/ReceiptDesc/Receipt/ReceiptDtl[unit_qty[text()=$NAME]]
     )
     return $NEWXML'
     passing XML_COLUMN as "XML",
             '0.0000' as "NAME"
     returning CONTENT
   )
from XML_TABLE;

I don’t know if I’m looking up the tag wrong but I’ve tried it with several formats and none of them work. for example this:

//Receipt[ReceiptDtl/unit_qty="0.0000"]

I appreciate all the help!

Advertisement

Answer

  1. You haven’t specified namespace, but your xml contains it, so oracle tried to find wrong elements. To search in all namespaces you can use *: as a namespace;
  2. If you want to delete whole node Receipt you should specify exactly Receipt and put all other filters into [], so, in short, we can put filter by child nodes into the filter. for example: $NEWXML//*:Receipt[./*:ReceiptDtl/*:unit_qty/text() eq $NAME] means that we want to delete all Receipt which contains at least one child node ReceiptDtl/unit_qty with a value = $NAME;
select
  x.XML_COLUMN as original
  ,XMLQuery(
    'copy $NEWXML := $XML
       modify (
         delete nodes $NEWXML/*:ReceiptDesc/*:Receipt[./*:ReceiptDtl/*:unit_qty/text() eq $NAME]
       )
     return $NEWXML'
     passing XML_COLUMN as "XML",
             '0.0000' as "NAME"
     returning CONTENT
   ) xml_fixed   
from XML_TABLE x;

Full example with test data: DBFiddle

with XML_TABLE as (
select xmltype(
q'[<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ReceiptDesc xmlns="http //www.w3.org/2000/svg">
    <appt_nbr>0</appt_nbr>
    <Receipt>
        <dc_dest_id>ST</dc_dest_id>
        <po_nbr>1232</po_nbr>
        <document_type>T</document_type>
        <asn_nbr>0033</asn_nbr>
        <ReceiptDtl>
            <item_id>100233127</item_id>
            <unit_qty>0.0000</unit_qty>
            <user_id>EXTERNAL</user_id>
            <shipped_qty>6.0000</shipped_qty>
        </ReceiptDtl>
        <from_loc>WH</from_loc>
        <from_loc_type>W</from_loc_type>
    </Receipt>
    <Receipt>
        <dc_dest_id>ST</dc_dest_id>
        <po_nbr>1233</po_nbr>
        <document_type>T</document_type>
        <asn_nbr>0033</asn_nbr>
        <ReceiptDtl>
            <item_id>355532244</item_id>
            <unit_qty>2.0000</unit_qty>
            <user_id>EXTERNAL</user_id>
            <shipped_qty>2.0000</shipped_qty>
        </ReceiptDtl>
        <from_loc>WH</from_loc>
        <from_loc_type>W</from_loc_type>
    </Receipt>
    <Receipt>
        <dc_dest_id>ST</dc_dest_id>
        <po_nbr>1234</po_nbr>
        <document_type>T</document_type>
        <asn_nbr>0033</asn_nbr>
        <ReceiptDtl>
            <item_id>54480</item_id>
            <unit_qty>0.0000</unit_qty>
            <user_id>EXTERNAL</user_id>
            <shipped_qty>6.0000</shipped_qty>
        </ReceiptDtl>
        <from_loc>WH</from_loc>
        <from_loc_type>W</from_loc_type>
    </Receipt>
    <Receipt>
        <dc_dest_id>ST</dc_dest_id>
        <po_nbr>1235</po_nbr>
        <document_type>T</document_type>
        <asn_nbr>0033</asn_nbr>
        <ReceiptDtl>
            <item_id>38760</item_id>
            <unit_qty>8.0000</unit_qty>
            <user_id>EXTERNAL</user_id>
            <shipped_qty>8.0000</shipped_qty>
        </ReceiptDtl>
        <from_loc>WH</from_loc>
        <from_loc_type>W</from_loc_type>
    </Receipt>
</ReceiptDesc>
]') XML_COLUMN
from dual
)
select
  x.XML_COLUMN as original
  ,XMLQuery(
      'copy $NEWXML := $XML
         modify (
           delete nodes $NEWXML/*:ReceiptDesc/*:Receipt[./*:ReceiptDtl/*:unit_qty/text() = $NAME]
         )
       return $NEWXML'
       passing XML_COLUMN as "XML",
               '0.0000' as "NAME"
       returning CONTENT
     ) 
   xml_fixed   
from XML_TABLE x;
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement