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 |
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 |
|
Michel Cadot
Messages: 68658 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:16Michel Cadot wrote on Mon, 21 June 2010 09:16As for your other topics:
Michel Cadot wrote on Mon, 21 June 2010 08:09From your previous topic:
Michel Cadot wrote on Thu, 17 June 2010 12:40Michel 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:15am 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 |
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 |
|
Michel Cadot
Messages: 68658 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:16Michel Cadot wrote on Mon, 21 June 2010 09:16As for your other topics:
Michel Cadot wrote on Mon, 21 June 2010 08:09From your previous topic:
Michel Cadot wrote on Thu, 17 June 2010 12:40Michel 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:15am 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.
|
|
|
|
Goto Forum:
Current Time: Sat Jun 15 16:38:56 CDT 2024
|