I have a table with XMLTYPE column.
Loaded the XML files using SQL Loader.
I can query the table using the below query when there is no xsi:schemaLocation and xmlns:xsi attributes in root tag.
SELECT extractValue(value(ctba),'/Staff/@Base') crew_base,
extractValue(value(ctbb),'/Payment') Payment
FROM xml_Files,
TABLE(xmlsequence(extract(XMLDATA,'/Payroll/Staff'))) ctba,
TABLE(xmlsequence(extract(XMLDATA,'/Payroll/Staff/Payment'))) ctbb
But when the xml file root tag contains xmlNamespace attributes this query does not return any rows.
My XML File looks like below:
=======================
<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
- <Payroll xsi:schemaLocation="http://www.myurl.com/Schemas/PayML Payroll.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.myurl.com/Schemas/PayML">
- <Staff Base="CTX" Name="SIYA MUAN" Expat="false" Rank="CA" StaffNumber="C9033">
<Payment InDate="2008-04-02+05:30" Id="807-808/1" EffDate="2008-04-30+00:00" ProcDate="2008-08-31+00:00">8:11</Payment>
<Payment InDate="2008-04-05+05:30" Id="517-608/1" EffDate="2008-04-30+00:00" ProcDate="2008-08-31+00:00">5:00</Payment>
</Staff>
</Payroll>
Any help is appreciated.