{"id":2651,"date":"2019-02-14T21:42:49","date_gmt":"2019-02-14T20:42:49","guid":{"rendered":"http:\/\/van-maanen.com\/?p=2651"},"modified":"2019-02-14T21:42:49","modified_gmt":"2019-02-14T20:42:49","slug":"working-with-xml-in-oracle","status":"publish","type":"post","link":"http:\/\/archief.van-maanen.com\/?p=2651","title":{"rendered":"Working with XML in Oracle"},"content":{"rendered":"\n

Oracle has nice features to export data as an XML file. Let us discuss a few of them.<\/p>\n\n\n\n

The first one is to use SQL Developer. We could already export a table in SQL developer as an XML file. <\/p>\n\n\n\n

The second one is to use the DBMS_XMLGEN package. This allows to use queries in an export. Let me show some code to demonstrate this.<\/p>\n\n\n\n

SELECT DBMS_XMLGEN.GETXML(
\n  'SELECT EMPLOYEE_ID
\n        , LAST_NAME
\n        , DEPARTMENT_ID
\n     FROM employees
\n    WHERE DEPARTMENT_ID < 101')
\n  \"XML OUTPUT\"
\n  FROM dual;<\/pre>\n\n\n\n
This creates output that can be inserted into an XML file. This can be read by a XML reader, such as Altova XMLSpy:<\/p>\n\n\n\n
\"\"
XML<\/figcaption><\/figure>\n\n\n\n
A third possibility is to work with a stored procedure. The code can be found here.<\/a><\/p>\n\n\n\n
One may notice that this stored procedure also uses the DBMS_XMLGEN.GETXML package. <\/p>\n\n\n\n
A fourth possibility is to use this statement:<\/p>\n\n\n\n
select xmlelement( \"Emp\", xmlforest( first_name, last_name, salary ) ) from hr.employees;<\/pre>\n\n\n\n
To get this displayed properly in sqlplus, we need to issue three commands:<\/p>\n\n\n\n
set serveroutput on
set pages 50000
set long 100000000<\/pre>\n\n\n\n
This leads to a nice readable result:<\/p>\n\n\n\n
\"\"
nice readable result<\/figcaption><\/figure>\n\n\n\n
Then possibility number 5. It is interesting to see that one has many possibilities to write a table or a query as XML. I read this possibility:<\/p>\n\n\n\n
select dbms_xmlgen.getxmltype('select * from employees') from dual;<\/pre>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
Oracle has nice features to export data as an XML file. Let us discuss a few of them. The first one is to use SQL Developer. We could already export a table in SQL developer as an XML file. The second one is to use the DBMS_XMLGEN package. This allows to use queries in an […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-2651","post","type-post","status-publish","format-standard","hentry","category-allgemein"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/2651","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2651"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/2651\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2651"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2651"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2651"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}