XMLTABLE : Convert XML into Rows and Columns using SQL

Hi Tim,

Could you do a video on how to ingest a regularly delivered XML file (with several nested children and data) to an Oracle table?

Thanks so much!

That was awesome. Thank you.

Hi Tim,
Could you please explain how do we fetch asymmetric elements in xml into rows and columns. For example, in my xml, I have 4 employee data in the xml. They have temporary and permanent address. Employee 4 doesn't have have address details. Instead displaying employee 4 address as null, employee 4 record is completely removed from the table. Could you please explain how to handle this

This is a very simple XML example with attributes only on the leaves of the xml tree. But how do you use xmltable, if xml elements reside on each level of the xml hierarchy?

good but very high level. more details would be helpful.

Is there any way to directly parse and query the xml document without adding it into the table?

Does similar can be achieved from JSON to rows in Oracle?

Hello dear,

I have created below queries; everything works fine except the select query that need your support please...


create table whh_test_1

(
test_id number(10),
xml_txt blob
);

the data is inserted from external drives as below and works fine;

declare
l_bfile bfile;
l_blob blob;
BEGIN

INSERT INTO whh_test_1(test_id) VALUES(1);
COMMIT;
update whh_test_1 set xml_txt=empty_blob() where test_id=1 RETURN xml_txt into l_blob;
l_bfile:=bfilename('DIR_XML','emp.xml');
dbms_lob.open(l_bfile,dbms_lob.file_readonly);
dbms_lob.loadfromfile(l_blob,l_bfile,dbms_lob.getlength(l_bfile));
dbms_lob.close(l_bfile);
commit;
END;

but below select query gives error:

SELECT xt.*
FROM whh_test_1 x,
XMLTABLE('/employees/emp'
PASSING x.xml_txt
COLUMNS
empno VARCHAR2(4) PATH 'empno',
ename VARCHAR2(10) PATH 'ename',
job VARCHAR2(19) PATH 'job',
mgr VARCHAR2(19) PATH 'mgr',
hire_date VARCHAR2(11) PATH 'hire_date',
sal VARCHAR2(11) PATH 'sal',
comm VARCHAR2(11) PATH 'comm'
) xt;

Thanks in Advance

Everything works fine except below query could you please give feedback. Thanks :-)
select xt.* from xml_tab x,
xmltable('/employees/employee'
passing x.xml_data
columns
"EMPNO" VARCHAR2(4) PATH 'EMPNO',
"ENAME" VARCHAR2(16) PATH 'ENMAE',
"JOB" VARCHAR2(16) PATH 'JOB',
"HIREDATE" VARCHAR2(16) PATH 'HIREDATE'
) xt;

Hi sir I need to process XML tag values and xpath dynamically how do we do this pls tel me

Thanks Tim for the clear explanation, good to see the CUI screen!

Am getting an error as "identifier 'SYS.DBMS_XQUERYINT' must be declared" ?

Nice video....thanks....just one concern, do we have any alternate than @ being used in SQL...in some applications @ is defined as different symbol

Good video - thanks. How can you use XMLTABLE when the data you need looks like xml, but it's inside a CDATA section?

Nice cameo from Kris Rice in the trailer.

Nice cameo from Kris Rice in the trailer.