Home » SQL & PL/SQL » SQL & PL/SQL » XMLQUERY: ORA-01427: single-row subquery returns more than one row (oracle 12g)
XMLQUERY: ORA-01427: single-row subquery returns more than one row [message #678881] Sun, 19 January 2020 23:22 Go to next message
shobhakashyapgmailcom
Messages: 58
Registered: June 2010
Location: India
Member

Hi,

am trying to pull the records from XML to oracle table. below is the query used for. result set is having multiple values in saperate nodes which is giving error. it is suppose to print one below another. kindly help.


XML file has been attached in the message.

INSERT INTO GOM_e2br3_imp_queue VALUES (
1000,
'IIMPPV020FER2019-V1',
xmltype(bfilename('MY_DIR','IDMP STD Sample.xml'),nls_charset_id('AL32UTF8') ),
SYSDATE
);


SELECT

B1.H_3_1a_XML,


(select H_3_1a
from
gom_e2br3_imp_queue imp,
XMLTABLE ( xmlnamespaces('urn:hl7-org:v3' as "xa"), '/xa:observationEvent' passing b1.H_3_1a_XML
columns
H_3_1a Varchar2(4000) path 'xa:value/@codeSystemVersion')
)
"H_3_1a" ,


(select H_3_1b
from
gom_e2br3_imp_queue imp,
XMLTABLE ( xmlnamespaces('urn:hl7-org:v3' as "xa"), '/xa:observationEvent' passing b1.H_3_1b_XML
columns
H_3_1b Varchar2(4000) path 'xa:value/@code')
)
"H_3_1b"



FROM
GOM_e2br3_imp_queue imp,
XMLTABLE ( xmlnamespaces('urn:hl7-org:v3' as "xa"), '/xa:MCCI_IN200100UV01' PASSING imp.xml_data
COLUMNS
H_3_1a_XML XMLTYPE PATH 'xa:PORR_IN049016UV/xa:controlActProcess/xa:subject/xa:investigationEvent/xa:component/xa:adverseEventAssessment/xa:component1/xa:obs ervationEvent[xa:author/xa:assignedEntity/xa:code/@code=1][xa:code/@code=15]', ---[xa:author/xa:assignedEntity/xa:code/@displayName="sender"]
H_3_1b_XML XMLTYPE PATH 'xa:PORR_IN049016UV/xa:controlActProcess/xa:subject/xa:investigationEvent/xa:component/xa:adverseEventAssessment/xa:component1/xa:obs ervationEvent[xa:author/xa:assignedEntity/xa:code/@code=1][xa:code/@code=15]'


) b1;

Re: XMLQUERY: ORA-01427: single-row subquery returns more than one row [message #678883 is a reply to message #678881] Mon, 20 January 2020 00:24 Go to previous messageGo to next message
Michel Cadot
Messages: 66851
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

From one of your previous topics:

Michel Cadot wrote on Wed, 23 June 2010 09:16
Michel Cadot wrote on Mon, 21 June 2010 09:16
As for your other topics:

Michel Cadot wrote on Mon, 21 June 2010 08:09
From your previous topic:

Michel Cadot wrote on Thu, 17 June 2010 12:40
Michel Cadot wrote on Thu, 17 June 2010 12:23

Post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Before Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Quote:
please anybody help me to solve
Please help us to help you to solve.
shobhakashyapgmailcom wrote on Mon, 21 June 2010 10:15
am sorry delna,Ved and michel if i was wrong anywhere..plz forgive me..i do respect everyone in forum..
Really? It seems you ignore the rules despite the many admonitions you got.

Regards
Michel


Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, make sure that lines of code do not exceed 80 characters.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

[Updated on: Mon, 20 January 2020 00:24]

Report message to a moderator

Re: XMLQUERY: ORA-01427: single-row subquery returns more than one row [message #678884 is a reply to message #678881] Mon, 20 January 2020 01:22 Go to previous messageGo to next message
shobhakashyapgmailcom
Messages: 58
Registered: June 2010
Location: India
Member

shobhakashyapgmailcom wrote on Mon, 20 January 2020 10:52

Hi,

am trying to pull the records from XML to oracle table. below is the query used for. result set is having multiple values in saperate nodes which is giving error. it is suppose to print one below another. kindly help.


XML file has been attached in the message.
Create directory

SQL> CREATE DIRECTORY my_dir1 AS 'D:/abc';

Directory created.

Please place the file attached in the message naming it as .xml( as it is not allowing me to upload XML format,extention has been changed to .txt)

SQL> INSERT INTO GOM_e2br3_imp_queue VALUES (
1000,
'IIMPPV020FER2019-V1',
xmltype(bfilename('MY_DIR1','IDMP STD Sample.xml'),nls_charset_id('AL32UTF8') ),
SYSDATE
);


SQL> SELECT
2 b1.h_3_1a_xml,
3 (
4 SELECT
5 h_3_1a
6 FROM
7 gom_e2br3_imp_queue imp,
8 XMLTABLE ( XMLNAMESPACES ( 'urn:hl7-org:v3' AS "xa" ), '/xa:observationEvent' PASSING b1.h_3_1a_xml
9 COLUMNS h_3_1a VARCHAR2(4000) PATH 'xa:value/@codeSystemVersion'
10 )
11 ) "H_3_1a",
12 (
13 SELECT
14 h_3_1b
15 FROM
16 gom_e2br3_imp_queue imp,
17 XMLTABLE ( XMLNAMESPACES ( 'urn:hl7-org:v3' AS "xa" ), '/xa:observationEvent' PASSING b1.h_3_1b_xml
18 COLUMNS h_3_1b VARCHAR2(4000) PATH 'xa:value/@code'
19 )
20 ) "H_3_1b"
21 FROM
22 gom_e2br3_imp_queue imp,
23 XMLTABLE ( XMLNAMESPACES ( 'urn:hl7-org:v3' AS "xa" ), '/xa:MCCI_IN200100UV01' PASSING imp.xml_data
24 COLUMNS
25 h_3_1a_xml XMLTYPE PATH 'xa:PORR_IN049016UV/xa:controlActProcess/xa:subject/xa:investigationEvent/xa:component/xa:adverseEventAssessment/xa:component1/xa:obs ervationEvent[xa:author/xa:assignedEntity/xa:code/@code=1][xa:code/@code=15]',
26 h_3_1b_xml XMLTYPE PATH 'xa:PORR_IN049016UV/xa:controlActProcess/xa:subject/xa:investigationEvent/xa:component/xa:adverseEventAssessment/xa:component1/xa:obs ervationEvent[xa:author/xa:assignedEntity/xa:code/@code=1][xa:code/@code=15]'
27 ) b1;
ERROR:
ORA-01427: single-row subquery returns more than one row



no rows selected

Re: XMLQUERY: ORA-01427: single-row subquery returns more than one row [message #678885 is a reply to message #678884] Mon, 20 January 2020 01:47 Go to previous messageGo to next message
Michel Cadot
Messages: 66851
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 20 January 2020 07:24

From one of your previous topics:

Michel Cadot wrote on Wed, 23 June 2010 09:16
Michel Cadot wrote on Mon, 21 June 2010 09:16
As for your other topics:

Michel Cadot wrote on Mon, 21 June 2010 08:09
From your previous topic:

Michel Cadot wrote on Thu, 17 June 2010 12:40
Michel Cadot wrote on Thu, 17 June 2010 12:23

Post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Before Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Quote:
please anybody help me to solve
Please help us to help you to solve.
shobhakashyapgmailcom wrote on Mon, 21 June 2010 10:15
am sorry delna,Ved and michel if i was wrong anywhere..plz forgive me..i do respect everyone in forum..
Really? It seems you ignore the rules despite the many admonitions you got.

Regards
Michel

Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, make sure that lines of code do not exceed 80 characters.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: XMLQUERY: ORA-01427: single-row subquery returns more than one row [message #678934 is a reply to message #678885] Wed, 22 January 2020 23:38 Go to previous message
shobhakashyapgmailcom
Messages: 58
Registered: June 2010
Location: India
Member

Hi,

I have updated my post . do you still feel the required information is missing. Kindly Respond.

Thanks
Previous Topic: Query on Oracle JVM
Next Topic: Looping through dblinks with a cursor
Goto Forum:
  


Current Time: Wed Feb 19 12:47:17 CST 2020