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
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...
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?
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
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!
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
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
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.
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
@@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.
@@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
Yes. The function for that is called JSON_TABLE. There's an example of it towards the end of this video. ruclips.net/video/YcuSOB85zAI/видео.html Or you can check it out in this article. oracle-base.com/articles/12c/json-support-in-oracle-database-12cr1#json_table Cheers Tim...
+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?
+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
+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
+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 :)
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
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
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.
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/
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.
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.
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.
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
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.
@@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
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 ...
Thanks Tim for the clear explanation, good to see the CUI screen!
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
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...
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?
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
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!
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
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
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.
Is there any way to directly parse and query the xml document without adding it into the table?
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
@@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.
@@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
@@oracle-base thank you..will definitely look into it.
Am getting an error as "identifier 'SYS.DBMS_XQUERYINT' must be declared" ?
If you Google your error, the first link back explains why you are getting this!
OK sir..thanks..
Does similar can be achieved from JSON to rows in Oracle?
Yes. The function for that is called JSON_TABLE. There's an example of it towards the end of this video.
ruclips.net/video/YcuSOB85zAI/видео.html
Or you can check it out in this article.
oracle-base.com/articles/12c/json-support-in-oracle-database-12cr1#json_table
Cheers
Tim...
@@oracle-base but it works for 12c or greater I guess
@@a00001111a1 JSON_TABLE was introduced in 12.1.0.2.
That was awesome. Thank you.
Good video - thanks. How can you use XMLTABLE when the data you need looks like xml, but it's inside a CDATA section?
+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?
+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
+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
+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 :)
Hi sir I need to process XML tag values and xpath dynamically how do we do this pls tel me
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
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
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.
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/
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.
Nice video....thanks....just one concern, do we have any alternate than @ being used in SQL...in some applications @ is defined as different symbol
You can always use XMLTABLE inside a view, so the internal syntax is not visible to your application.
Nice cameo from Kris Rice in the trailer.
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;
The query displays no rows...
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.
Thank You, Now works fine
good but very high level. more details would be helpful.
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.
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
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.
@@TheTimHall already tried, did't work, but le me see again, maybe i missed something ... thanks for reply ... good day :)
@@meandmydodge try with just an individual column first.
@@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
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 ...
Nice cameo from Kris Rice in the trailer.