I have tried to read Oracle XMLDB Developer Guide and my first reaction was that parsing XML is very complicated in Oracle… And lately I got a task to rewrite some PL\/SQL code to use a different SOAP service and the existing code had a few hundred rows just to parse XML (with XML DOM API)! Actually, starting from 10.2, this job is much easier…<\/p>\n
Here I will go over some methods for working with XML, that are usable directly from SQL. In this first part, reading and extracting data from XML.<\/p>\n
\n<?xml version=\"1.0\" encoding=\"utf-8\"?>\n\n Some Company name<\/name>\n \n - \n
Ilmar<\/first_name>\n Kerm<\/last_name>\n Estonia<\/country>\n \n - \n
Development<\/name>\n <\/item>\n - \n
DBA<\/name>\n <\/item>\n <\/departments>\n <\/item>\n - \n
Ilmar2<\/first_name>\n Kerm2<\/last_name>\n Estonia2<\/country>\n \n - \n
Development<\/name>\n <\/item>\n - \n
DBA<\/name>\n <\/item>\n <\/departments>\n <\/item>\n <\/employees>\n<\/company>\n<\/pre>\n Load the XML data to a table, to special XMLTYPE data type.<\/p>\n
\ncreate table xml (\n x xmltype\n);\n\ninsert into xml values (\nxmltype.createxml('<?xml version=\"1.0\" encoding=\"utf-8\"?>\n\n Some Company name<\/name>\n \n...\n <\/employees>\n<\/company>'));\n\nCOMMIT;\n<\/pre>\n Extracting a single value<\/h2>\n
extractValue<\/b> can be used to extract a single value from XML using XPath expression<\/a>.<\/p>\n
\nSQL> SELECT extractValue(x, '\/company\/name') FROM xml;\n\nEXTRACTVALUE(X,'\/COMPANY\/NAME')\n-------------------------------\nSome Company name\n<\/pre>\nextractValue only works with single values, otherwise exception will be raised.<\/p>\n
\nSQL> SELECT extractValue(x, '\/company\/employees\/item') FROM xml;\nSELECT extractValue(x, '\/company\/employees\/item') FROM xml\n*\nERROR at line 1:\nORA-19025: EXTRACTVALUE returns value of only one node\n<\/pre>\nQuery XML as relational object<\/h2>\n
Oracle Database, since 10g, has a very easy way to map XML to a relational object and query it with SQL, so no PL\/SQL code is needed for parsing XML – the XMLTABLE function.<\/p>\n
\nSQL> SELECT emp.*\n FROM xml,\n XMLTABLE (\n '\/company\/employees\/item'\n PASSING x\n COLUMNS id NUMBER PATH '@id',\n first_name VARCHAR2 (10 CHAR) PATH 'first_name',\n last_name VARCHAR2 (10 CHAR) PATH 'last_name',\n country VARCHAR2 (10 CHAR) PATH 'country',\n active_department VARCHAR2 (20 CHAR) PATH 'departments\/item[@active=\"true\"]\/name'\n ) emp;\n\n ID FIRST_NAME LAST_NAME COUNTRY ACTIVE_DEPARTMENT\n---------- ---------- ---------- ---------- --------------------\n 1 Ilmar Kerm Estonia DBA\n 2 Ilmar2 Kerm2 Estonia2 Development\n<\/pre>\nXmlTable takes the following arguments:<\/p>\n
\n
\n ‘\/company\/employees\/item’<\/td>\n XPath expression of the “row”<\/td>\n<\/tr>\n \n PASSING x<\/td>\n Source of XML data – XMLTYPE data type<\/td>\n<\/tr>\n \n COLUMNS
id NUMBER PATH ‘@id’,
\nfirst_name VARCHAR2 (10 CHAR) PATH ‘first_name’,
\nlast_name VARCHAR2 (10 CHAR) PATH ‘last_name’,
\ncountry VARCHAR2 (10 CHAR) PATH ‘country’,
\nactive_department VARCHAR2 (20 CHAR) PATH ‘departments\/item[@active=”true”]\/name’\n<\/td>\nColumn definitions with the corresponding Oracle data type and XPath expression<\/td>\n<\/tr>\n<\/table>\n After XML is readable as a relational table, all the power of Oracle SQL can be used for querying.<\/p>\n","protected":false},"excerpt":{"rendered":"
I have tried to read Oracle XMLDB Developer Guide and my first reaction was that parsing XML is very complicated in Oracle… And lately I got a task to rewrite some PL\/SQL code to use a different SOAP service and the existing code had a few hundred rows just to parse XML (with XML DOM […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-56","post","type-post","status-publish","format-standard","hentry","category-blog-entry"],"_links":{"self":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/56","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/comments?post=56"}],"version-history":[{"count":0,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/56\/revisions"}],"wp:attachment":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/media?parent=56"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/categories?post=56"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/tags?post=56"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}