Hi All,
I have an XML document, which I need to parse and take the data from respective tags and insert it into another table.
here is the XML I am having.
<convertTo xsi:schemaLocation="https://xecdapi.xe.com/schema/v1/convertTo.xsd">
<terms>http://www.xe.com/privacy.php</terms>
<privacy>http://www.xe.com/legal/dfs.php</privacy>
<to>USD</to>
<amount>1.0</amount>
<timestamp>2015-10-25T23:00:00Z</timestamp>
<from>
<rate>
<currency>EUR</currency>
<mid>0.9075541422</mid>
</rate>
<rate>
<currency>INR</currency>
<mid>65.0313451105</mid>
</rate>
<rate>
<currency>CAD</currency>
<mid>1.3167560135</mid>
</rate>
<rate>
<currency>GBP</currency>
<mid>0.6528693249</mid>
</rate>
</from>
</convertTo>
Here is the code I am using to parse the values
DECLARE
x XMLType := XMLType(
'<convertTo xsi:schemaLocation="https://xecdapi.xe.com/schema/v1/convertTo.xsd">
<terms>http://www.xe.com/privacy.php</terms>
<privacy>http://www.xe.com/legal/dfs.php</privacy>
<to>USD</to>
<amount>1.0</amount>
<timestamp>2015-10-25T23:00:00Z</timestamp>
<from>
<rate>
<currency>EUR</currency>
<mid>0.9075541422</mid>
</rate>
<rate>
<currency>INR</currency>
<mid>65.0313451105</mid>
</rate>
<rate>
<currency>CAD</currency>
<mid>1.3167560135</mid>
</rate>
<rate>
<currency>GBP</currency>
<mid>0.6528693249</mid>
</rate>
</from>
</convertTo>'
);
BEGIN
FOR r IN
(
SELECT
ExtractValue(Value(p),'/rate/currency/text()') AS name
--,ExtractValue(Value(p),'/row/Address/State/text()') as state
--,ExtractValue(Value(p),'/row/Address/City/text()') as city
FROM
TABLE(XMLSequence(Extract(x,'convertTo/from/rate'))) p
)
LOOP
-- do whatever you want with r.name, r.state, r.city
dbms_output.put_line ('Name'||r.name);
END LOOP;
END;
I am getting the below error message,
Error report:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00234: namespace prefix "xsi" is not declared
Error at line 1
ORA-06512: at "SYS.XMLTYPE", line 310
ORA-06512: at line 2
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.
Any help on how to resolve this would be really helpful.
Appreciate your time and help.
Thanks,
Ragul