{"id":11296,"date":"2016-02-19T09:59:37","date_gmt":"2016-02-19T14:59:37","guid":{"rendered":"http:\/\/jianmingli.com\/wp\/?p=11296"},"modified":"2016-02-19T09:59:37","modified_gmt":"2016-02-19T14:59:37","slug":"generate-xml-from-oracle-database","status":"publish","type":"post","link":"https:\/\/jianmingli.com\/wp\/?p=11296","title":{"rendered":"Generate XML from Oracle Database"},"content":{"rendered":"
\n

Contents<\/h2>\n
    \n\t
  1. \n\t\tOverview<\/a>\n\t<\/li>\n\t
  2. \n\t\tGenerate XML using SQL Functions<\/a>\n\t<\/li>\n\t
  3. \n\t\tGenerate XML using DBMS_XMLGEN<\/a>\n\t\t
      \n\t\t\t
    1. \n\t\t\t\tdbms-xmlgen Functions and Procedures<\/a>\n\t\t\t<\/li>\n\t\t\t
    2. \n\t\t\t\tExamples<\/a>\n\t\t\t\t
        \n\t\t\t\t\t
      1. \n\t\t\t\t\t\tExample One<\/a>\n\t\t\t\t\t<\/li>\n\t\t\t\t<\/ol>\n<\/ol>\n\t\t\t
      2. \n\t\t\t\tReferences<\/a>\n\t\t\t<\/li>\n<\/ol>\n<\/ol>\n<\/div>\n
         <\/div>\n

        Overview<\/h2><\/span>\n

        Generate XML using SQL Functions<\/h2><\/span>\n

        * XMLELEMENT and XMLATTRIBUTES
        \n* XMLFOREST
        \n* XMLCONCAT
        \n* XMLAGG
        \n* XMLPI
        \n* XMLCOMMENT
        \n* XMLSERIALIZE
        \n* XMLPARSE<\/p>\n

        Generate XML using DBMS_XMLGEN<\/h2><\/span>\n

        * DBMS_XMLGEN creates XML documents (as a CLOB or XMLType) from SQL query results
        \n* It provides a fetch<\/em> interface
        \n– max rows to retrieve
        \n– max rows to skip
        \n* It provides options for changing tag names for ROW, ROWSET, etc.<\/p>\n

        dbms-xmlgen Functions and Procedures<\/h3><\/span>\n

        * Context handle
        \n– used by all functions<\/p>\n

        \r\nSUBTYPE ctxHandle IS NUMBER\r\n<\/pre>\n
        * Obtain a context handle<\/p>\n
        \r\n-- Context handle from query string\r\nnewContext(\r\n  queryString IN VARCHAR2)\r\n\r\n-- Context handle from cursor variable\r\nnewContext(\r\n  queryString IN SYS_REFCURSOR)\r\n  RETURN ctxHandle;\r\n\r\n-- ?\r\nnewContextFromHierarchy(\r\n  queryString IN VARCHAR2)\r\n  RETURN ctxHandle;\r\n<\/pre>\n
        \r\n  -- Create context handle\r\n  qryCtx := dbms_xmlgen.newContext(\r\n    'select * from hr.employees');\r\n<\/pre>\n
        * Sets the name of the element separating all the rows. The default name is ROW<\/em>.<\/p>\n
        \r\nsetRowTag(ctx IN ctxHandle,\r\n          rowTag IN VARCHAR2);\r\n\r\ndbms_xmlgen.setRowTag(qryCtx, 'EMPLOYEE');\r\n<\/pre>\n
        * Sets the name of the document root element. The default name is ROWSET<\/em><\/p>\n
        \r\nsetRowSetTag(ctx IN ctxHandle, \r\n             rowSetTag IN VARCHAR2);\r\n\r\n  -- Set the rowset tab\r\n  dbms_xmlgen.setRowSetTag(qryCtx, 'EMPLOYEES');\r\n<\/pre>\n
        * Retrieves XML<\/p>\n
        \r\n-- Get xml doc from input context handle and append to input clob:\r\ngetXML(ctx IN ctxHandle, \r\n       clobval IN OUT NCOPY clob, \r\n       dtdOrSchema IN number:= NONE);\r\n\r\n-- Get xml doc from input context handle and return as clob:\r\ngetXML(ctx IN ctxHandle, \r\n       dtdOrSchema IN number:= NONE) \r\n  RETURN clob;\r\n\r\n-- Get xml doc from input context handle and return as XMLType:\r\ngetXMLType(\r\n  ctx IN ctxHandle, \r\n  dtdOrSchema IN number:= NONE)\r\n  RETURN XMLType;\r\n\r\n-- Get xml from input query string and return as clob;\r\ngetXML(\r\n  sqlQuery IN VARCHAR2, \r\n  dtdOrSchema IN NUMBER := NONE)\r\n  RETURN CLOB;\r\n\r\n-- Get xml from input query string and return as XMLType;\r\ngetXMLType(\r\n  sqlQuery IN VARCHAR2, \r\n  dtdOrSchema IN NUMBER := NONE)   \r\n  RETURN XMLType;\r\n<\/pre>\n
        * Get number of rows processed<\/p>\n
        \r\ngetNumRowsProcessed(ctx IN ctxHandle) \r\n  RETURN number;\r\n\r\n-- e.g.\r\nexit when dbms_xmlgen.getnumrowsprocessed(qryCtx) = 0;\r\n<\/pre>\n
        * Sets the maximum number of rows to fetch from the SQL query result for every invocation of the getXML call.
        \n– It is an error to call this function on a context handle created by function newContextFromHierarchy<\/em>.<\/p>\n
        \r\nsetMaxRows(ctx IN ctxHandle, \r\n           maxRows IN NUMBER);\r\n\r\n-- e.g.\r\ndbms_xmlgen.setmaxrows(qryCtx, 2);\r\n<\/pre>\n
        * Skips a given number of rows before generating the XML output for every call to getXML.
        \n– It is an error to call this function on a context handle created by function newContextFromHierarchy<\/em>.<\/p>\n
        \r\nsetSkipRows(ctx IN ctxHandle, \r\n            skipRows IN NUMBER);\r\n<\/pre>\n
        * Determines whether or not special characters in the XML data must be converted into their escaped XML equivalent.
        \n– defaults to yes<\/p>\n
        \r\nsetConvertSpecialChars(\r\n  ctx IN ctxHandle, \r\n  conv IN BOOLEAN);\r\n<\/pre>\n
        * Sets the name of the collection elements.
        \n– The default name for collection elements it the type name itself.
        \n– You can override that to use the name of the column with the _ITEM<\/em> tag appended to it using this function.<\/p>\n
        \r\nuseItemTagsForColl(ctx IN ctxHandle);\r\n<\/pre>\n
        * Restarts the query and generate the XML from the first row again.<\/p>\n
        \r\nrestartQuery(ctx IN ctxHandle);\r\n<\/pre>\n
        * Close context handle<\/p>\n
        \r\ncloseContext(ctx IN ctxHandle);\r\n<\/pre>\n
        * Encodes or decodes the XML data string argument.
        \n– Encoding refers to replacing entity references such as < to their escaped equivalent, such as <.\n- Decoding refers to the reverse conversion.\n\n\n
        \r\nconvert(\r\n  xmlData IN varchar2, \r\n  flag IN NUMBER := ENTITY_ENCODE)    \r\n  RETURN VARCHAR2;\r\n\r\nconvert(\r\n  xmlData IN CLOB, \r\n  flag IN NUMBER := ENTITY_ENCODE) \r\n  RETURN CLOB;\r\n<\/pre>\n
        * NULL handling
        \nThe setNullHandling flag values are:
        \n    DROP_NULLS CONSTANT NUMBER := 0;
        \n    This is the default setting and leaves out the tag for NULL elements.<\/p>\n
            NULL_ATTR CONSTANT NUMBER := 1;
        \n    This sets xsi:nil = “true”.<\/p>\n
            EMPTY_TAG CONSTANT NUMBER := 2;
        \n    This sets, for example, .<\/p>\n
        \r\nsetNullHandling(ctx IN ctxHandle, \r\n                flag IN NUMBER);\r\n\r\n-- shortcut for setNullHandling(ctx, NULL_ATTR)\r\nuseNullAttributeIndicator(\r\n  ctx IN ctxHandle, \r\n  attrind IN BOOLEAN := TRUE);\r\n<\/pre>\n
        * Sets bind value for the bind variable appearing in the query string associated with the context handle.
        \n– The query string with bind variables cannot be executed until all of the bind variables are set values using setBindValue.<\/p>\n
        \r\nsetBindValue(\r\n  ctx IN ctxHandle, \r\n  bindVariableName IN VARCHAR2, \r\n  bindValue IN VARCHAR2);\r\n<\/pre>\n
        * Clears all the bind values for all the bind variables appearing in the query string associated with the context handle. Afterwards, all of the bind variables must rebind new values using setBindValue.<\/p>\n
        \r\nclearBindValue(ctx IN ctxHandle);\r\n<\/pre>\n
        Examples<\/h3><\/span>\n
        Example One<\/h4><\/span>\n
        * Create a temp table to hold XML result:<\/p>\n
        \r\ncreate table temp_clob_tab (result clob);\r\n<\/pre>\n
        * Use dbms_xmlgen:<\/p>\n
        \r\ndeclare\r\n  -- Define a context handle\r\n  qryCtx dbms_xmlgen.ctxHandle;\r\n  -- Result XML\r\n  xml clob;\r\nbegin\r\n  -- Create context handle\r\n  qryCtx := dbms_xmlgen.newContext(\r\n    'select * from hr.employees');\r\n  \r\n  -- Set the row tab\r\n  dbms_xmlgen.setRowTag(qryCtx, 'EMPLOYEE');\r\n  \r\n  dbms_xmlgen.setmaxrows(qryCtx, 2);\r\n  \r\n  loop\r\n    -- Get result xml\r\n    xml := dbms_xmlgen.getXML(qryCtx);\r\n    \r\n    exit when dbms_xmlgen.getnumrowsprocessed(qryCtx) = 0;\r\n  \r\n    -- Insert result xml into temp table\r\n    insert into temp_clob_tab values(xml);\r\n  end loop;\r\n  \r\n  -- Close context\r\n  dbms_xmlgen.closeContext(qryCtx);\r\nend;\r\n\/\r\n\r\n<\/pre>\n
        References<\/h2><\/span>\n
        * Generating XML Data from the Database<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
        Overview Generate XML using SQL Functions * XMLELEMENT and XMLATTRIBUTES * XMLFOREST * XMLCONCAT * XMLAGG * XMLPI * XMLCOMMENT * XMLSERIALIZE * XMLPARSE Generate XML using DBMS_XMLGEN * DBMS_XMLGEN creates XML documents (as a CLOB or XMLType) from SQL … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[35],"tags":[567,674],"class_list":["post-11296","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-oracle","tag-xml"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p8cRUO-2Wc","_links":{"self":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/11296","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=11296"}],"version-history":[{"count":2,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/11296\/revisions"}],"predecessor-version":[{"id":11313,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/11296\/revisions\/11313"}],"wp:attachment":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=11296"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=11296"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=11296"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}