Dear All,
Can Anyone help me to retrieve data from an XML format
The XML data is as
-
<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope
xmlns:soap="http://www.w3.org/2003/05/soap-envelope"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<GetStdStatusResponse
xmlns="http://tempuri.org/">
<GetStdStatusResult>
<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:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="Table1">
<xs:complexType>
<xs:sequence>
<xs:element name="CIVL_ID" type="xs:string" minOccurs="0" />
<xs:element name="STUDENT_NAME" type="xs:string" minOccurs="0" />
<xs:element name="STATUS_CATEGORY_DESC" type="xs:string" minOccurs="0" />
<xs:element name="PASSED_UNITS" type="xs:string" minOccurs="0" />
<xs:element name="GPA" type="xs:string" minOccurs="0" />
<xs:element name="MAJOR_NAME" type="xs:string" minOccurs="0" />
<xs:element name="STATUS_DATE" type="xs:dateTime" 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="">
<Table1 diffgr:id="Table11" msdata:rowOrder="0" diffgr:hasChanges="inserted">
<CIVL_ID>289031901311</CIVL_ID>
<STUDENT_NAME>?¹?¨?¯?§U?U?U? ?§?U??¯ U?U??³U? ?¯?§U??¯</STUDENT_NAME>
<STATUS_CATEGORY_DESC>??U??± U?U?U??¯</STATUS_CATEGORY_DESC>
<PASSED_UNITS>0</PASSED_UNITS>
<MAJOR_NAME>??U?U?U?U?U??¬U??§ U?U?U??§U?U?U?U??§ U?U?U? U???±U?U?</MAJOR_NAME>
<STATUS_DATE>2011-10-27T17:25:10+03:00</STATUS_DATE>
</Table1>
</NewDataSet>
</diffgr:diffgram>
</GetStdStatusResult>
</GetStdStatusResponse>
</soap:Body>
</soap:Envelope>
-----------------------------------------
And I am trying to get the data as
FOR r IN
(SELECT *
FROM xmltable( xmlnamespaces(DEFAULT 'http://tempuri.org/'), '/GetStdStatusResult' passing l_request.response
columns CIVL_ID VARCHAR2(100) path 'NewDataSet/Table1/CIVL_ID',
STUDENT_NAME VARCHAR2(100) path 'NewDataSet/Table1/STUDENT_NAME',
STATUS_CATEGORY_DESC VARCHAR2(100) path 'NewDataSet/Table1/STATUS_CATEGORY_DESC' ,
PASSED_UNITS VARCHAR2(100) path 'NewDataSet/Table1/PASSED_UNITS' ,
MAJOR_NAME VARCHAR2(100) path 'NewDataSet/Table1/MAJOR_NAME'
)
)
LOOP
l_paaet_info.CIVL_ID :=r.CIVL_ID;
l_paaet_info.STUDENT_NAME :=r.STUDENT_NAME;
l_paaet_info.STATUS_CATEGORY_DESC :=r.STATUS_CATEGORY_DESC;
l_paaet_info.PASSED_UNITS :=r.PASSED_UNITS;
l_paaet_info.MAJOR_NAME :=r.MAJOR_NAME;
DBMS_OUTPUT.PUT_LINE('The CIVL_ID is '||l_paaet_info.CIVL_ID);
DBMS_OUTPUT.PUT_LINE('The STUDENT_NAME is '||l_paaet_info.STUDENT_NAME);
DBMS_OUTPUT.PUT_LINE('The STATUS_CATEGORY_DESC is '||l_paaet_info.STATUS_CATEGORY_DESC);
DBMS_OUTPUT.PUT_LINE('The PASSED_UNITS is '||l_paaet_info.PASSED_UNITS);
DBMS_OUTPUT.PUT_LINE('The MAJOR_NAME is '||l_paaet_info.MAJOR_NAME);
DBMS_OUTPUT.PUT_LINE('The STATUS_DATE is '||l_paaet_info.STATUS_DATE);
END LOOP;
But due to some XML structure problem I am getting only nulls
Regards
Dennis John