Hi All,
In Oracle 11g i have the below:
create table xx_directors (id number, dir_name varchar2(250), gender varchar2(250), DateOfBirth varchar2(250),ResignationDate varchar2(250));
create table xx_positions(position_id number, direcotr_id number, position varchar2(250),appoint_date varchar2(250));
Let us consider the below XML given:
<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
<s:Body>
<RetrieveCompanyOnlineReportResponse xmlns="http://www.creditsafe.com/globaldata/operations">
<RetrieveCompanyOnlineReportResult xmlns:q1="http://www.creditsafe.com/globaldata/datatypes/reports">
<q1:Reports>
<q1:Report xsi:type="q1:LtdCompanyFullReport" CompanyId="SE011/0/5562546746" OrderNumber="2286128" Language="EN" ReportCurrency="SEK" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<q1:Directors>
<q1:CurrentDirectors>
<q1:Director>
<q1:Name>Anders Johan Bovaller</q1:Name>
<q1:Gender>1</q1:Gender>
<q1:DateOfBirth>1965-09-04T00:00:00Z</q1:DateOfBirth>
<q1:Position AppointmentDate="2009-09-15T00:00:00Z">Actual Member of Board</q1:Position>
<q1:Position AppointmentDate="2009-09-15T00:00:00Z">Chairman of Board</q1:Position>
</q1:Director>
<q1:Director>
<q1:Name>Allison, Simon Thomas</q1:Name>
<q1:Gender>0</q1:Gender>
<q1:DateOfBirth>1974-07-30T00:00:00Z</q1:DateOfBirth>
<q1:Position AppointmentDate="2014-06-12T00:00:00Z">Actual Member of Board</q1:Position>
<q1:Position AppointmentDate="2014-06-12T00:00:00Z">Foreign Resident in the EEA</q1:Position>
</q1:Director>
<q1:Director>
<q1:Name>Mateus Antunes, Vitor Manuel</q1:Name>
<q1:Gender>0</q1:Gender>
<q1:DateOfBirth>1973-12-10T00:00:00Z</q1:DateOfBirth>
<q1:Position AppointmentDate="2011-02-15T00:00:00Z">Actual Member of Board</q1:Position>
<q1:Position AppointmentDate="2011-02-15T00:00:00Z">Foreign Resident in the EEA</q1:Position>
</q1:Director>
</q1:CurrentDirectors>
</q1:Directors>
</q1:Report>
</q1:Reports>
</RetrieveCompanyOnlineReportResult>
</RetrieveCompanyOnlineReportResponse>
</s:Body>
</s:Envelope>
I want to insert data into above 2 tables and associate the positions to the director name it belongs. I tried to parse it with one query but it returns error( i inserted the xml into custom table as clob) :
select
*
FROM XXCM_XML_DOCUMENTS t
, xmltable(
XMLNamespaces('http://schemas.xmlsoap.org/soap/envelope/' AS "s",
'http://www.creditsafe.com/globaldata/datatypes/reports' AS "q1",
'http://www.creditsafe.com/globaldata/datatypes' AS "tn", -- Added
DEFAULT 'http://www.creditsafe.com/globaldata/operations')
, 'for $i in /s:Envelope/s:Body/RetrieveCompanyOnlineReportResponse/RetrieveCompanyOnlineReportResult/q1:Reports/q1:Report
, $j in $i/q1:Directors/q1:CurrentDirectors/q1:Director
return element r {
element CompanyId {data($i/@CompanyId)
}
, $j
}'
PASSING xmltype(t.xmldoc)
COLUMNS
director_name VARCHAR2(250) PATH 'q1:Director/q1:Name',
director_Gender VARCHAR2(250) PATH 'q1:Director/q1:Gender',
director_DateOfBirth VARCHAR2(250) PATH 'q1:Director/q1:DateOfBirth',
director_Position VARCHAR2(250) PATH 'q1:Director/q1:Position',
director_AppointmentDate VARCHAR2(250) PATH 'q1:Director/q1:Position/@AppointmentDate',
CompanyId VARCHAR2(250) PATH './CompanyId'
) x
where t.file_id = 266
Error is :
ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
19279. 00000 - "XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence"
*Cause: The XQuery sequence passed in had more than one item.
*Action: Correct the XQuery expression to return a single item sequence.
Can anyone please tell me how to loop the data so i can check for more than 1 Position tag against a Director ?
Regards,
Alex