XMLTABLE : Convert XML into Rows and Columns using SQL

[@shiningstar8823]

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

[@TheOldMaritimer]

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!

[@oracle-base]

The linked article shows how to deal with nested XML. oracle-base.com/articles/misc/xmltable-convert-xml-data-into-rows-and-columns-using-sql#nested-xml Once you've got the data parsed, just insert it. As for it happening regularly, just use a job. oracle-base.com/articles/10g/scheduler-10g#simple

[@raaghavirajendran1515]

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

[@oracle-base]

I think you probably need to do this in multiple XMLTABLE calls. The first to get the base data that is present for all rows. The next to get the additional address data. You can then joint these two sets together using an outer join.

[@michaelcieslik]

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?

[@oracle-base]

I've added an example of nested attribute-based XML to the second half of this section. oracle-base.com/articles/misc/xmltable-convert-xml-data-into-rows-and-columns-using-sql#nested-xml

[@ZaidAlig]

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

[@oracle-base]

You can always use XMLTABLE inside a view, so the internal syntax is not visible to your application.

[@jeeves251]

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

[@oracle-base]

+jeeves251 You would probably have to extract the data and present it as an XMLTYPE, so XMLTABLE can use it. I guess how you do this depends on your requirement. You could create a pipelined table function to extract the CDATA field and present it as an XMLTYPE, the query from that using the TABLE operator, and apply the XMLTABLE to that maybe?

[@jeeves251]

+ORACLE-BASE.com Hey thank you for the quick response! Ok I'll research that asap! Basically I'm trying to parse the soap response from the OBIEE executeSQLQuery web service. I don't know why Oracle delivers the payload in a CDATA section. What's the point of delivering XML if the bits you need are delivered as text? This is what the response looks like: 1000East Region 2000West Region 1500Central Region ]]> true

[@oracle-base]

+jeeves251 I've used your XML in an example here. oracle-base.com/articles/misc/xmltable-convert-xml-data-into-rows-and-columns-using-sql#xml-data-in-variables

[@jeeves251]

+ORACLE-BASE.com Super helpful Tim; Thank you so much. That xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" namespace bit was really throwing me for a loop but now that I see how you handled it I understand a little better. Thanks again for sharing your expertise and for the great video and articles :)

[@VictorKonishchev]

That was awesome. Thank you.

[@romashery5291]

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

[@oracle-base]

The linked articles in the description give more details. It's kind-of hard to do a video on XML or JSON without it getting really boring, so instead I try and give a taste. If someone is interested, there is always more information in the linked articles.

[@pawe708]

0:48 whats the 'x' ? im trying to getClobVal() but it doesn't work im doing: select test8(username).movies(table name).getClobVal() from filmy test8 nothing show up

[@oracle-base]

In the examples, "x" is the alias for the table. That's not special to this functionality. It's just something you do in SQL to make tables easier to reference. Perhaps you will find it easier to follow the article? oracle-base.com/articles/misc/xmltable-convert-xml-data-into-rows-and-columns-using-sql Cheers Tim...

[@KentGraziano]

Nice cameo from Kris Rice in the trailer.

[@kavirajnegi]

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

[@oracle-base]

Yes. There is an example in the linked post. oracle-base.com/articles/misc/xmltable-convert-xml-data-into-rows-and-columns-using-sql#xml-data-in-variables

[@kavirajnegi]

@@oracle-base thanks for replying. I think i was not able to tell you what exactly i am try to achieve. I have a XML file stored in the server. I wish to fetch some information from that XML and using that info i wish to update a table in the Oracle database. It would be really helpful If can share some article regarding this type of problem. Thanks in advance.

[@oracle-base]

@@kavirajnegi Well, that linked article shows you how to get the data out of XML stored in a variable. Now all you need to do is load a variable form a file on the file system. There is an example of how to do that here. oracle-base.com/dba/script?category=miscellaneous&file=file_to_blob.sql

[@kavirajnegi]

@@oracle-base thank you..will definitely look into it.

[@followMahi]

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

[@oracle-base]

Yes. The function for that is called JSON_TABLE. There's an example of it towards the end of this video. kzbin.info/www/bejne/j5TYhIJ4bZqtd6s Or you can check it out in this article. oracle-base.com/articles/12c/json-support-in-oracle-database-12cr1#json_table Cheers Tim...

[@a00001111a1]

@@oracle-base but it works for 12c or greater I guess

[@oracle-base]

@@a00001111a1 JSON_TABLE was introduced in 12.1.0.2.

[@siddhartharao8490]

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

[@oracle-base]

If you Google your error, the first link back explains why you are getting this!

[@siddhartharao8490]

OK sir..thanks..

[@bbujjin3899]

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

[@oracle-base]

If you don't know the structure of the XML you have to walk through it using the functionality described here. oracle-base.com/articles/9i/parse-xml-documents-9i

[@bbujjin3899]

ORACLE-BASE.com Hi sir Thanks for your reply Am struck here I need to get all XML tag values from XML document and stored into "ca_iso_typ_tab" collection. this collection contains XPATH and VALUE columns. I need to store XML tag values into VALUE column dynamically please kindly let me know

[@oracle-base]

The article linked above gives you the basics. You will be using subprograms of DBMS_XMLDOM. Specifically the GETCHILDNODES to get all the childten of the top-most node. Then for each node returned call GETCHILDNODES again. So this is recursive, which will ultimately allow you to visit every node in the XML document. You can then build a collection with the information you get back.

[@bbujjin3899]

ORACLE-BASE.com Thank you so much sir Using DBMS_xmldom subprograms I build one function that returns node value, node type with dynamically it's working fine .But I need xpath also could you please kindly let me know Ex: Xpath ------------- Root/id/id2/name/

[@TheTimHall]

I don't believe DBMS_xmldom has any functionality to do this. There is no equivalent to the "getpathFor()" functionality of Javascript. As result you will have to track the path yourself.

[@saleemafzali9884]

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;

[@saleemafzali9884]

The query displays no rows...

[@oracle-base]

You've not copied my code. You're using uppercase in the search path "PATH 'EMPNO'", rather using lowercase, "PATH 'empno'". This search path is case sensitive.

[@saleemafzali9884]

Thank You, Now works fine

[@meandmydodge]

Hi Tim, thanks for your solution. I appreciate if you or someone can help to bring below complex xml file in oracle table. scsanctions.un.org/resources/xml/en/consolidated.xml tried but could't .. so found 3rd party tool and just running the show ... but its a long exercise. Need to define process to directly upload data from XML to Oracle Table. Best Regards

[@TheTimHall]

It's the same as the example in my video. You're xml is made up of lots of individuals. Project the columns for one individual and you are done. Size of the document doesn't make it harder.

[@meandmydodge]

@@TheTimHall already tried, did't work, but le me see again, maybe i missed something ... thanks for reply ... good day :)

[@TheTimHall]

@@meandmydodge try with just an individual column first.

[@meandmydodge]

@@TheTimHall yes i will try for 2 to 3 individual records first with your method ... if success ? will apply on all ... thanks for response ... will let you know the results too ... :) Regards

[@meandmydodge]

see i tried your method step by step ... it works exactly as you did in video ... now the point is you populated data from scott.emp table to xml_tab ... whereas i want to bring data from xml file to oracle table (the link is there in above comments) ... how to do that ? i accept i am new in this ...

[@KentGraziano]

Nice cameo from Kris Rice in the trailer.