HomeНаука и техникаRelated VideosMore From: ORACLE-BASE.com

XMLTABLE : Convert XML into Rows and Columns using SQL

100 ratings | 21043 views
This video gives a quick overview of the XMLTABLE functionality, which can be used to convert XML documents in to rows and columns in SQL. For more information see: https://oracle-base.com/articles/misc/xmltable-convert-xml-data-into-rows-and-columns-using-sql https://oracle-base.com/articles/misc/sqlxml-sqlx-generating-xml-content-using-sql Website: https://oracle-base.com Blog: https://oracle-base.com/blog Twitter: https://twitter.com/oraclebase Cameo by Mike Hichwa : Twitter : https://twitter.com/mikehichwa1 Cameo appearances are for fun, not an endorsement of the content of this video. All trademarks, product names and logos are the property of their respective owners.
Html code for embedding videos on your blog
Text Comments (27)
Atiq Mehrin (3 months ago)
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
Saleem Afzali (5 months ago)
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;
Saleem Afzali (5 months ago)
Thank You, Now works fine
ORACLE-BASE.com (5 months ago)
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.
Saleem Afzali (5 months ago)
The query displays no rows...
bb ujjin (9 months ago)
Hi sir I need to process XML tag values and xpath dynamically how do we do this pls tel me
bb ujjin (9 months ago)
Tim Hall How can I get xpath please help me sir
Tim Hall (9 months ago)
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.
bb ujjin (9 months ago)
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/
ORACLE-BASE.com (9 months ago)
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.
bb ujjin (9 months ago)
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
Shining star (1 year ago)
Thanks Tim for the clear explanation, good to see the CUI screen!
Paweł Milecki (2 years ago)
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.com (2 years ago)
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? https://oracle-base.com/articles/misc/xmltable-convert-xml-data-into-rows-and-columns-using-sql Cheers Tim...
siddhartha rao (2 years ago)
Am getting an error as "identifier 'SYS.DBMS_XQUERYINT' must be declared" ?
siddhartha rao (2 years ago)
OK sir..thanks..
ORACLE-BASE.com (2 years ago)
If you Google your error, the first link back explains why you are getting this!
Zaid Alig. (2 years ago)
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.com (2 years ago)
You can always use XMLTABLE inside a view, so the internal syntax is not visible to your application.
jeeves251 (2 years ago)
Good video - thanks. How can you use XMLTABLE when the data you need looks like xml, but it's inside a CDATA section?
jeeves251 (2 years ago)
+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 :)
ORACLE-BASE.com (2 years ago)
+jeeves251 I've used your XML in an example here. https://oracle-base.com/articles/misc/xmltable-convert-xml-data-into-rows-and-columns-using-sql#xml-data-in-variables
jeeves251 (2 years ago)
+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: <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:sawsoap="urn://oracle.bi.webservices/v6"> <soap:Body> <sawsoap:executeSQLQueryResult> <sawsoap:return xsi:type="sawsoap:QueryResults"> <sawsoap:rowset> <![CDATA[<rowset xmlns="urn:schemas-microsoft-com:xml-analysis:rowset"> <Row><Column0>1000</Column0><Column1>East Region</Column1></Row> <Row><Column0>2000</Column0><Column1>West Region</Column1></Row> <Row><Column0>1500</Column0><Column1>Central Region</Column1></Row> </rowset>]]> </sawsoap:rowset> <sawsoap:queryID/> <sawsoap:finished>true</sawsoap:finished> </sawsoap:return> </sawsoap:executeSQLQueryResult> </soap:Body> </soap:Envelope>
ORACLE-BASE.com (2 years ago)
+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?
Kent Graziano (2 years ago)
Nice cameo from Kris Rice in the trailer.
Kent Graziano (2 years ago)
Nice cameo from Kris Rice in the trailer.

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.