Hi
I am trying to extract data from XML type variable in PLSQL with below script, but it is not giving any data.
Any one please correct me where I am doing wrong.
DECLARE
l_xml VARCHAR2(32767);
BEGIN
l_xml := '<?xml version="1.0" encoding="WINDOWS-1256"?>
<DataTable xmlns="http://www.telcen.it/ws">
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="Table" msdata:Locale="">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="Table">
<xs:complexType>
<xs:sequence>
<xs:element name="GROUP1" type="xs:string" minOccurs="0"/>
<xs:element name="TOT_COST" type="xs:double" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<NewDataSet xmlns="">
<Table diffgr:id="Table1" msdata:rowOrder="0">
<GROUP1>000 </GROUP1>
<TOT_COST>0</TOT_COST>
</Table>
<Table diffgr:id="Table2" msdata:rowOrder="1">
<GROUP1>001229010002 </GROUP1>
<TOT_COST>0</TOT_COST>
</Table>
</NewDataSet>
</diffgr:diffgram>
</DataTable>';
FOR cur_rec IN (
SELECT a.mydata, xt.*
FROM (
-- Pull out just the CDATA value.
SELECT XMLTYPE(l_xml) AS mydata
FROM dual
) a,
-- Specify the path that marks a new row, remembering to use the correct namespace.
XMLTABLE(XMLNAMESPACES(default 'urn:schemas-microsoft-com:xml-msdata:NewDataSet'), '/NewDataSet/Table'
PASSING XMLTYPE(l_xml)
COLUMNS
column0 VARCHAR2(20) PATH 'GROUP1',
column1 NUMBER(4) PATH 'TOT_COST'
) xt)
LOOP
DBMS_OUTPUT.put_line('column0=' || cur_rec.column0 || ' column1=' || cur_rec.column1);
END LOOP;
END;
/
Thank you.
Regards,
Ibrahim Sayyed.