Skip to Main Content
  • Questions

  • How to extract the tag value form the xml tag with colon

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Chris Saxon

Thanks for the question.

Asked: November 15, 2023 - 6:01 am UTC

Last updated: November 21, 2023 - 10:59 am UTC

Version: 19c

Viewed 1000+ times

You Asked

How to Extract the Tag value from xml which as tag name with colon.

 <link>https://www.rba.gov.au/statistics/frequency/exchange-rates.html#JPY</link>
    <description>AUD/JPY representative rate as at 4.00 pm Eastern Australian time on 14 Nov 2023</description>
    <dc:date>2023-11-14T17:52:00+11:00</dc:date>
    <dc:language>en</dc:language>
    <dcterms:audience>general</dcterms:audience>
    <cb:statistics rdf:parseType="Resource">
      <rdf:type rdf:resource="http://www.cbwiki.net/wiki/index.php/RSS-CB_1.2_RDF_Schema#Exchange-Rates"/>
      <cb:country>AU</cb:country>
      <cb:institutionAbbrev>RBA</cb:institutionAbbrev>
      <cb:exchangeRate rdf:parseType="Resource">
        <rdf:type rdf:resource="http://www.cbwiki.net/wiki/index.php/RSS-CB_1.2_RDF_Schema#Exchange-Rates"/>
        <cb:observation rdf:parseType="Resource">
          <rdf:type rdf:resource="http://www.cbwiki.net/wiki/index.php/RSS-CB_1.2_RDF_Schema#Exchange-Rates"/>
          <cb:value>96.64</cb:value>
          <cb:unit>AUD</cb:unit>
          <cb:decimals>2</cb:decimals>
        </cb:observation>
        <cb:baseCurrency>AUD</cb:baseCurrency>
        <cb:targetCurrency>JPY</cb:targetCurrency>
        <cb:rateType>4.00 pm foreign exchange rates</cb:rateType>
        <cb:observationPeriod rdf:parseType="Resource">
          <rdf:type rdf:resource="http://www.cbwiki.net/wiki/index.php/RSS-CB_1.2_RDF_Schema#Exchange-Rates"/>
          <cb:frequency>daily</cb:frequency>
          <cb:period>2023-11-14</cb:period>
        </cb:observationPeriod>
      </cb:exchangeRate>
    </cb:statistics>


Tried with by inserting the xml as clob object.
SELECT
   XMLTYPE(t.xclob).EXTRACT('/rdf:RDF/item/cb:statistics/cb:exchangeRate/cb:baseCurrency/text()').getStringVal()
FROM
   test_clob t;

Getting dORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00601: Invalid token in: ' '/rdf:RDF/item/cb:statistics/cb:exchangeRate/cb:baseCurrency/text()'
31011. 00000 -  "XML parsing failed"
*Cause:    XML parser returned an error while trying to parse the document.
*Action:   Check if the document to be parsed is valid.


Please suggest.

and Chris said...

I tried the suggested action:

Action: Check if the document to be parsed is valid.

And indeed, the XML shown is invalid!

- There's no root element
- Many of the elements have prefixes with undefined namespaces (e.g. the "dc" in dc:date).

If this is only part of the document you're working with, please provide a complete, valid document. Then we can help further.

If this is the complete XML document you've received, the first step is to work with them to get a valid document.

Rating

  (1 rating)

We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.