XMLTABLE in oracle - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
11,335
got it... following worked good...
FROM ( SELECT p_region_id,
p_revision_id,
p_bg_revision_id,
p_facility_id,
p_scp_classification_id,
p_provider_group_id
FROM DUAL) p,
(SELECT b.bgid, b.ggid
FROM XMLTABLE(
'/ProviderGroupScopeOfPractice'
PASSING lv_xmldata
COLUMNS records XMLTYPE PATH 'Records') r,
XMLTABLE ( 'Records/Record'
PASSING r.records
COLUMNS bgid NUMBER (3) PATH '@bgid',
ggid NUMBER (5) PATH '@ggid'
)b
) bgDetails;
Author by
beetri
Updated on June 25, 2022Comments
-
beetri about 2 years
Declare lv_xmldata XMLType := XMLType(' <ProviderGroupScopeOfPractice> <Records> <Record bgid="1" ggid="39"/> <Record bgid="2" ggid="0"/> <Record bgid="3" ggid="0"/> <Record bgid="1" ggid="35"/> </Records> </ProviderGroupScopeOfPractice> '); v_provider_Group_Id Number(10); v_resource_Id Number(10); v_count Number (10); p_region_id number(5); p_revision_id number(5); p_bg_revision_id number(5); p_scp_classification_id number(3); p_facility_id VARCHAR2(3); p_provider_group_id Number(10); temp number; Begin p_region_id :=51; p_revision_id :=53; p_bg_revision_id :=1; p_scp_classification_id:= 2; p_facility_id :='STR'; p_provider_group_id := 211; INSERT INTO BG_GDLINE_SCOPE_GROUP_XREF ( REGION_ID, REVISION_ID, BG_REVISION_ID, FACILITY_ID, SCP_CLASSIFICATION_ID, PROVIDER_GROUP_ID, BG_CLASSIFICATION_ID, GUIDELINE_GROUP_ID, CREATE_USER_ID, CREATE_TS, UPDATE_USER_ID, UPDATE_TS ) SELECT p.p_region_id, p.p_revision_id, p.p_bg_revision_id, p.p_facility_id, p.p_scp_classification_id, p.p_provider_group_id, bgDetails.bgid, bgDetails.ggid, 'user1', Sysdate, 'user1', Sysdate FROM ( SELECT p_region_id, p_revision_id, p_bg_revision_id, p_facility_id, p_scp_classification_id, p_provider_group_id FROM DUAL) p, (SELECT b.bgid, b.ggid FROM XMLTABLE( '/ProviderGroupScopeOfPractice/Records' PASSING lv_xmldata columns bgid number(3) PATH 'Record/@bgid' , ggid number(5) PATH 'Record/@ggid' ) b ) bgDetails ; end;
I get error as:
Error report: ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence ORA-06512: at line 33 19279. 00000 - "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.
Please advice...
Thanks