Skip to Main Content
  • Questions

  • Issue while fetching XML file using XMLTABLE sql query

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ayub.

Asked: October 22, 2021 - 5:22 am UTC

Last updated: November 11, 2021 - 6:01 am UTC

Version: 19c

Viewed 1000+ times

You Asked

XML from client :
XML FILE1
<?xml version="1.0" encoding="UTF-8"?>
<easy-form-response>
<subject>
<supplier-information-contacts>
<supplier-information-contact>
<name-given>Kimberly</name-given>
<name-family>Crider</name-family>
<name-fullname>Kimberly Crider</name-fullname>
<email>criderkim@gmail.com</email>
</supplier-information-contact>
<supplier-information-contact>
<name-given>Kim</name-given>
<name-family>Crider</name-family>
<name-fullname>Kim Crider</name-fullname>
<email>criderkim@gmail.com</email>
</supplier-information-contact>
</supplier-information-contacts>
<diversities type="array" />
</subject>
</easy-form-response>

XML File2
<?xml version="1.0" encoding="UTF-8"?>
<easy-form-response>
<subject>
<supplier-information-contacts>
<supplier-information-contact>
<name-given>Kimberly</name-given>
<name-family>Crider</name-family>
<name-fullname>Kimberly Crider</name-fullname>
<email>criderkim@gmail.com</email>
</supplier-information-contact>
</supplier-information-contacts>
<diversities type="array" />
</subject>
</easy-form-response>


Error message:

ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence

I want sql query to fetch column 'name-given',name-family,name-fullname,email from the xml also query should work for both xml files.

Currently I am using below sql but this is failing for XML file 1

SELECT tab1.contact_email
FROM xxbcg_coupa_xml_gtt gtt,
XMLTABLE (
'/easy-form-responses/easy-form-response'
PASSING xmltype (gtt.xml_data)
COLUMNS contact_email VARCHAR2 (4000)
PATH '//subject/supplier-information-contacts/supplier-information-contact/email',
XMLTYPE
PATH '//subject/supplier-information-addresses/supplier-information-address')tab1
;

and Connor said...


SQL>
SQL> create table xxbcg_coupa_xml_gtt  (id int, xml_data clob);

Table created.

SQL>
SQL> begin
  2  insert into xxbcg_coupa_xml_gtt  values (1,
  3  '<?xml version="1.0" encoding="UTF-8"?>
  4  <easy-form-response>
  5  <subject>
  6  <supplier-information-contacts>
  7  <supplier-information-contact>
  8  <name-given>Kimberly</name-given>
  9  <name-family>Crider</name-family>
 10  <name-fullname>Kimberly Crider</name-fullname>
 11  <email>criderkim@gmail.com</email>
 12  </supplier-information-contact>
 13  <supplier-information-contact>
 14  <name-given>Kim</name-given>
 15  <name-family>Crider</name-family>
 16  <name-fullname>Kim Crider</name-fullname>
 17  <email>criderkim@gmail.com</email>
 18  </supplier-information-contact>
 19  </supplier-information-contacts>
 20  <diversities type="array" />
 21  </subject>
 22  </easy-form-response>');
 23
 24  insert into xxbcg_coupa_xml_gtt  values (2,
 25  '<?xml version="1.0" encoding="UTF-8"?>
 26  <easy-form-response>
 27  <subject>
 28  <supplier-information-contacts>
 29  <supplier-information-contact>
 30  <name-given>Kimberly</name-given>
 31  <name-family>Crider</name-family>
 32  <name-fullname>Kimberly Crider</name-fullname>
 33  <email>criderkim@gmail.com</email>
 34  </supplier-information-contact>
 35  </supplier-information-contacts>
 36  <diversities type="array" />
 37  </subject>
 38  </easy-form-response>');
 39
 40  end;
 41  /

PL/SQL procedure successfully completed.

SQL>
SQL> select gtt.id, tab2.*
  2  FROM xxbcg_coupa_xml_gtt gtt,
  3  XMLTABLE (
  4      '/easy-form-response'
  5      PASSING xmltype (gtt.xml_data)
  6      COLUMNS addr XMLTYPE PATH 'subject/supplier-information-contacts/supplier-information-contact'
  7      )  tab1
  8  CROSS JOIN XMLTable(
  9    '/supplier-information-contact'
 10    PASSING tab1.addr
 11    COLUMNS name varchar2(40) path 'name-given',
 12            email varchar2(40) path 'email'
 13  ) tab2;

        ID NAME                                     EMAIL
---------- ---------------------------------------- ----------------------------------------
         1 Kimberly                                 criderkim@gmail.com
         1 Kim                                      criderkim@gmail.com
         2 Kimberly                                 criderkim@gmail.com

3 rows selected.

SQL>



Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.