CODEWITHSUNDEEP
sqlxmloracle-databasexpath
Nishant Modi
Nishant Modi

Reputation: 679

Unable to fetch data using extractvalue function

I have a table having a XML Type column in Oracle DB. I am trying to use the extractvalue and updatexml functions on that table.

XML 

<?xml version = '1.0' encoding = 'UTF-8'?>
<custominfo>
   <singlerecord  ZIP="51100"/>
   <multiplerecord type="ONE_TIME_CHARGES_LIST">
      <record DESCRIPTION="Device Payment" RATE="1000000" TAX="0"/>
      <record DESCRIPTION="Plan Payment" RATE="480000" TAX="0"/>
      <record DESCRIPTION="Deposit" RATE="1000000" TAX="0"/>
   </multiplerecord>
</custominfo>

Query

select EXTRACTVALUE(XMLCONTENT,'//record[@DESCRIPTION="Plan Payment"]') from TABLE_XML  X;

Can anyone suggest what I am doing wrong here?

After sorting out the extract I want to replace the DESCRIPTION from "Plan Payment" to "Pre Plan Payment".

Upvotes: 1

Views: 436

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

What you are doing is working - it's giving you the text value of the record node with that attribute value. But that node is empty:

<record DESCRIPTION="Plan Payment" RATE="480000" TAX="0"/>

has three attributes but the node itself has no content - it's an empty tag. So your query is returning null, which is correct. If you wanted to see that you'd found the node you could get an attribute value instead:

select EXTRACTVALUE(XMLCONTENT,'//record[@DESCRIPTION="Plan Payment"]/@DESCRIPTION')
from TABLE_XML  X;

However, the extractvalue() function has long been deprecated, so you shouldn't really be using it anyway.

You can use an XMLQuery to view the whole matching record node; as the node has no content there isn't much point in getting its content, as your current query does:

select XMLQuery('$x//record[@DESCRIPTION="Plan Payment"]'
    passing xmlcontent as "x"
    returning content
  ) as result
from table_xml;

RESULT                                                                          
--------------------------------------------------------------------------------
<record DESCRIPTION="Plan Payment" RATE="480000" TAX="0"/>

The updatexml() function is deprecated as well. You can use an XMLQuery to modify the attribute name too:

select XMLQuery('copy $i := $x modify
    (for $j in $i//record[@DESCRIPTION="Plan Payment"]/@DESCRIPTION
      return replace value of node $j with $r)
    return $i'
    passing xmlcontent as "x", 'Pre Plan Payment' as "r"
    returning content
  ) as result
from table_xml;

Using a CTE for your sample XML document, and wrapping it in an XMLSerialize call to retain the formatting for readability:

with table_xml (xmlcontent) as (
  select xmltype(q'[<?xml version = '1.0' encoding = 'UTF-8'?>
<custominfo>
   <singlerecord  ZIP="51100"/>
   <multiplerecord type="ONE_TIME_CHARGES_LIST">
      <record DESCRIPTION="Device Payment" RATE="1000000" TAX="0"/>
      <record DESCRIPTION="Plan Payment" RATE="480000" TAX="0"/>
      <record DESCRIPTION="Deposit" RATE="1000000" TAX="0"/>
   </multiplerecord>
</custominfo>]'
  ) from dual
)
select XMLSerialize(
    document
    XMLQuery('copy $i := $x modify
      (for $j in $i//record[@DESCRIPTION="Plan Payment"]/@DESCRIPTION
        return replace value of node $j with $r)
      return $i'
      passing xmlcontent as "x", 'Pre Plan Payment' as "r"
      returning content
    )
    indent
  ) as result
from table_xml;

RESULT                                                                          
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<custominfo>
  <singlerecord ZIP="51100"/>
  <multiplerecord type="ONE_TIME_CHARGES_LIST">
    <record DESCRIPTION="Device Payment" RATE="1000000" TAX="0"/>
    <record DESCRIPTION="Pre Plan Payment" RATE="480000" TAX="0"/>
    <record DESCRIPTION="Deposit" RATE="1000000" TAX="0"/>
  </multiplerecord>
</custominfo>

If you want to update the value in the table you can use the same query as part of an update statement, filtered on rows that match. Read more.

Upvotes: 3

Related Questions