oracle - Best way to output 100,000 records from PL/SQL program into XML file -

-

i'm new xml have been been given task output test set of 100,000 records xml file, bigger file in future. within each record there subsets of child data needs output file (see structure of code below).

i'm trying find best solution dbms_xmldom looking @ forums outdated tool know there xmlelement think works in sql , not pl/sql.

is there else works in pl/sql other dbms_xmldom or should stick i'm doing. tool has output each record output file.

the current version of database 11g release 1.

any advice great in advance.

  begin    r_prod_rec in c_prod_rec   loop     output process master record xml file      r_prod_child1_rec in c_prod_child1_rec(r_prod_rec.id)     loop          output process child record table1 xml file     end loop      r_prod_child2_rec in c_prod_child2_rec(r_prod_rec.id)     loop          output process child record table2 xml file     end loop    end loop    end

oracle setup:

create table parents ( id, attr1, attr2 ) select level, 'a.' || level, 'b.' || level   dual connect level <= 5;  create table children ( id, attr1, parent_id ) select level, 'c.' || level, case when level <= 3 1                                   when level <= 6 2                                   when level <= 8 4                                                   else 5                              end dual connect level <= 9;

query:

select xmlelement(          "root",          xmlagg(            xmlelement(              "parent",              xmlattributes(                p.id,                p.attr1 "a",                p.attr2 "b"              ),              c.child_xml            )          )        ).getclobval() xml   parents p        left outer join (          select parent_id,                 xmlagg(                   xmlelement(                     "child",                     xmlattributes( id ),                     attr1                   )                 ) child_xml            children          group parent_id        ) c        on ( p.id = c.parent_id );

output:

xml ------------------------------------------------------------------------------------------- <root><parent id="1" a="a.1" b="b.1"><child id="1">c.1</child><child id="2">c.2</child> <child id="3">c.3</child></parent><parent id="2" a="a.2" b="b.2"><child id="4">c.4</child> <child id="5">c.5</child><child id="6">c.6</child></parent><parent id="3" a="a.3" b="b.3"> </parent><parent id="4" a="a.4" b="b.4"><child id="7">c.7</child><child id="8">c.8</child> </parent><parent id="5" a="a.5" b="b.5"><child id="9">c.9</child></parent></root>

if want use in pl/sql do:

declare   p_xml clob; begin   select ...     p_xml     ...;    -- use xml value. end; /

Comments

Post a Comment

Popular posts from this blog

java - Run spring boot application error: Cannot instantiate interface org.springframework.context.ApplicationListener -

-
i have spring project , try make use spring boot , run @ embadded tomcat following : https://spring.io/guides/gs/rest-service/ this application //@configuration //@enableaspectjautoproxy @springbootapplication @componentscan(basepackages = "gux.prome") public class application { public static void main(string[] args) { springapplication.run(application.class, args); } } if use maven command: mvn spring-boot:run , project starts fine, need debug , run main method @ intelij, exception occurs: exception in thread "main" java.lang.illegalargumentexception: cannot instantiate interface org.springframework.context.applicationlistener : org.springframework.boot.logging.classpathloggingapplicationlistener @ org.springframework.boot.springapplication.createspringfactoriesinstances(springapplication.java:414) @ org.springframework.boot.springapplication.getspringfactoriesinstances(springapplication.java:394) @ org.springframework.bo...
Read more

reactjs - React router and this.props.children - how to pass state to this.props.children -

-
i'm using react-router first time , don't know how think in yet. here's how i'm loading components in nested routes. entry point .js reactdom.render( <router history={hashhistory} > <route path="/" component={app}> <route path="models" component={content}> </route> </router>, document.getelementbyid('app') ); app.js render: function() { return ( <div> <header /> {this.props.children} </div> ); } so child of app content component sent in. i'm using flux , app.js has state , listens changes, don't know how pass state down this.props.children. before using react-router app.js defines children explicitly, passing state natural don't see how now. using couple of react helper methods can add state, props , whatever else this.props.children render: function() { var children = react.ch...
Read more

Excel VBA "Microsoft Windows Common Controls 6.0 (SP6)" Location Changes -

-
i have excel workbook used generate quotations customers. there main page user inputs data, , there buttons generate various pdfs , send them customer. however, 1 user consistently getting error preventing generating , sending pdfs. i beleive "microsoft windows common controls 6.0 (sp6)". it's installed on users' computers. on mine, location "c:\windows\syswow64\mscomctl.ocx" on others "c:\windows\system32\mscomctl.ocx" the user experiencing issues on 32-bit machine, "mscomctl.ocx" file should in system32. have manually put in there, , pointed common controls reference in vba it. issue seems keep reverting looking in syswow64. since folder doesn't exist, can't find it, , can't produce pdfs. i thought perhaps issue user running older version of office (2007) there users on 2013 , 2016. thought perhaps mixture of 64-bit , 32-bit users. user gets sent workbook other users (as quotation process goes further, gets passed ...
Read more