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)