CODEWITHSUNDEEP
sqlxmloracle-databaseplsql
RC LFA
RC LFA

Reputation: 19

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!

Upvotes: 0

Views: 47

Answers (1)

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8665

  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;

Upvotes: 1

Related Questions