tag:blogger.com,1999:blog-1378961241059327992.post5867361750783554189..comments2023-04-05T09:35:55.180+02:00Comments on KiBeHa: INSERT ALL master/detail data from XMLKim Berg Hansenhttp://www.blogger.com/profile/06491635470794828550noreply@blogger.comBlogger9125tag:blogger.com,1999:blog-1378961241059327992.post-52872507739464254712019-04-04T15:40:17.496+02:002019-04-04T15:40:17.496+02:00Okay, when you have no IDs in the XML, then you ca...Okay, when you have no IDs in the XML, then you cannot just split into two insert statements.<br /><br />Depending on your data you have two choices that I can think of.<br /><br />One way is a PL/SQL for-loop on a query that uses the single XMLTable to retrieve all master attributes + the additionalnumbers and functions as XMLType columns. Inside the for-loop you insert a single master (part) RETURNING it's ID. Then two insert statements using XMLtable on the two XMLType columns.<br /><br />The other is doing everything in a single INSERT ALL and accepting the complexity of the cartesian join. I have created an example of this on LiveSQL you can look at: https://livesql.oracle.com/apex/livesql/s/h684g2dl0wsv9c3b9l83mt7wo<br /><br />Which method is preferable depends on your data, how many rows, how large the cartesian joins between additionalnumbers and functions can become, etc. You would have to try it out and see which works the best for you ;-)<br /><br />Cheerio<br />/KimKim Berg Hansenhttps://www.blogger.com/profile/06491635470794828550noreply@blogger.comtag:blogger.com,1999:blog-1378961241059327992.post-24052253271743255792019-04-04T14:19:08.258+02:002019-04-04T14:19:08.258+02:00Hi.
Yes, i have ID column in all tables. But XML ...Hi.<br /><br />Yes, i have ID column in all tables. But XML nodes have none.<br />If i insert master and first detail node, how can i then insert second detail in new insert statement when i don't know to which master it belongs. <br />XML master node can have only minor difference or none. Such is the structure of my XML.<br /><br />Example:<br />Parts> <br />....Name confidential="true ">acetate/Name> <br />....NumberExists confidential="false">true/NumberExists> <br />....FullNumber confidential="false">111/FullNumber> <br />....AdditionalNumbers> <br />........Number confidential="false">123/Number> <br />........Number confidential="false">234/Number> <br />........Number confidential="false">134534/Number> <br />..../AdditionalNumbers> <br />....Quantity confidential="false">.22/Quantity> <br />....Functions> <br />........Function confidential="true ">13/Function> <br />..../Functions> <br />....WhetherClassification confidential="true ">false/WhetherClassification> <br />....Details> <br />........DataAvailable confidential="false">2/DataAvailable> <br />........Emission confidential="false">true/Emission> <br />....Details> <br />/Parts> <br /><br /><br />BR,<br />Dip<br /><br />Diplinghttps://www.blogger.com/profile/08507121781551682703noreply@blogger.comtag:blogger.com,1999:blog-1378961241059327992.post-37706728857486711762019-04-04T11:17:04.794+02:002019-04-04T11:17:04.794+02:00Hi, Dip
Let me split that in two:
- Detail is mi...Hi, Dip<br /><br />Let me split that in two:<br /><br />- Detail is missing:<br /><br />For example in the last insert statement in the blogpost, line 32 could be changed to "left outer join xmltable(" and line 39 changed to ") xmllines on 1=1". And then the CASE structure in line 17-20 should be adapted to handle NULL values in lineseq when there are no orderlines.<br /><br />- Multiple details:<br /><br />If an order contained for example both orderlines and payments, you could add another xmltype column to the first xmltable call containing the payments, and then left outer join a third xmltable call passing this column. But the problem would then be that within each order, the orderlines and payments would actually be cartesian joined - you would need a way (maybe analytic row_number) to make sure each orderline and each payment would be inserted only once.<br /><br />That could easily become cumbersome, and depending on the number of detail rows it could also perform sub-optimally, so the benefit of a single insert compared to looping over the orders might be less (or even worse).<br /><br />If you have IDs in the data (no need for sequences), I'd probably do 2 inserts - one like shown in the post, one extra for all rows from the second detail nodes.<br /><br /><br />Cheerio<br />/KimKim Berg Hansenhttps://www.blogger.com/profile/06491635470794828550noreply@blogger.comtag:blogger.com,1999:blog-1378961241059327992.post-15284069146121282342019-04-04T10:18:52.828+02:002019-04-04T10:18:52.828+02:00Hello.
Great post.
But how to do it, if you have ...Hello.<br /><br />Great post.<br />But how to do it, if you have two detail nodes and sometimes one can be missing.<br /><br />BR,<br />DipDiplinghttps://www.blogger.com/profile/08507121781551682703noreply@blogger.comtag:blogger.com,1999:blog-1378961241059327992.post-3473216957998898662015-06-30T08:40:52.624+02:002015-06-30T08:40:52.624+02:00Hi, Rachit
I'm sorry, but posting XML to Blog...Hi, Rachit<br /><br />I'm sorry, but posting XML to Blogger removes all the XML tags, so I cannot see how your XML is structured :-(<br /><br />Either if you have the ability to upload the XML to some file sharing site and post a link?<br />Or alternatively paste your XML into this HTML encoder:<br />http://www.way2blogging.org/blogger-tools/html-entities-encoder-decoder<br />Paste your XML there, press "encode" button.<br />That will give you something that should look like this:<br /><br />&lt;xmltag&gt;<br />The value<br />&lt;/xmltag&gt;<br /><br />Then copy that HTML encoded result and paste it into a post here on the blog.<br />And then it should look like this:<br /><br /><xmltag><br />The value<br /></xmltag><br /><br />Kim Berg Hansenhttps://www.blogger.com/profile/06491635470794828550noreply@blogger.comtag:blogger.com,1999:blog-1378961241059327992.post-56314981777848647662015-06-30T07:20:15.300+02:002015-06-30T07:20:15.300+02:00Here is my code:-
/* Formatted on 6/25/2015 10:16:...Here is my code:-<br />/* Formatted on 6/25/2015 10:16:13 AM (QP5 v5.240.12305.39446) */<br />DECLARE<br /> v_xml XMLTYPE;<br /> ECODE NUMBER;<br /> EMESG VARCHAR2(2000);<br /><br />BEGIN<br /> SELECT xmltype (xml_type) INTO v_xml FROM XML_TEMP;<br /><br />FOR R IN ( <br />select xmlorders.ID<br /> , xmlorders.NAME<br /> , xmlorders.CUSTOMERBUILDINGID<br />from xmltable(<br /> '/BUILDINGS/BUILDING'<br /> passing v_xml<br /> columns ID integer path 'ID'<br /> , NAME varchar2(100) path 'NAME'<br /> , CUSTOMERBUILDINGID integer path 'CUSTOMERBUILDINGID'<br /> ) xmlorders )<br /> LOOP<br /> DBMS_OUTPUT.PUT_LINE('-->' || R.ID||'-->' || R.NAME||'-->' || R.CUSTOMERBUILDINGID);<br /> END LOOP; <br /> <br /> <br /> FOR buildingaddress IN ( <br />select xmlorders.ID<br /> , xmlbuildingaddress.STREETADDRESS1<br /> , xmlbuildingaddress.CITY<br /> , xmlbuildingaddress.STATE<br /> , xmlbuildingaddress.ZIP<br />from xmltable(<br /> '/BUILDINGS/BUILDING'<br /> passing v_xml<br /> columns ID integer path 'ID'<br /> , BUILDING xmltype path 'BUILDING'<br /> ) xmlorders<br /> ,xmltable(<br /> '/BUILDING/BUILDINGADDRESS'<br /> passing xmlorders.BUILDING<br /> columns STREETADDRESS1 VARCHAR2(100) path 'STREETADDRESS1', <br /> CITY VARCHAR2(50) path 'CITY',<br /> STATE VARCHAR2(100) path 'STATE',<br /> ZIP VARCHAR2(100) path 'ZIP'<br /> ) xmlbuildingaddress<br /> )<br /> LOOP<br /> DBMS_OUTPUT.PUT_LINE('-->' || buildingaddress.ID||'-->' || buildingaddress.STREETADDRESS1||'-->' || buildingaddress.CITY||'-->' || buildingaddress.STATE);<br /> END LOOP; <br /> <br /> <br />FOR propertymanager IN ( <br />select xmlorders.ID<br /> , xmlpropertymanager.COMPANYID<br /> , xmlpropertymanager.NAME<br /> , xmlpropertymanager.PHONE<br /> , xmlpropertymanager.WEBSITE<br />from xmltable(<br /> '/BUILDINGS/BUILDING'<br /> passing v_xml<br /> columns ID integer path '@ID'<br /> , BUILDING xmltype path 'BUILDING'<br /> ) xmlorders<br /> ,xmltable(<br /> '/BUILDING/PROPERTYMANAGER'<br /> passing xmlorders.BUILDING<br /> columns COMPANYID VARCHAR2(100) path 'COMPANYID', <br /> NAME VARCHAR2(50) path 'NAME',<br /> PHONE VARCHAR2(100) path 'PHONE',<br /> WEBSITE VARCHAR2(100) path 'WEBSITE'<br /> ) xmlpropertymanager )<br /> LOOP<br /> DBMS_OUTPUT.PUT_LINE('-->' || propertymanager.ID||'-->' || propertymanager.COMPANYID||'-->' || propertymanager.NAME||'-->' || propertymanager.PHONE);<br /> END LOOP; <br /> <br /><br />EXCEPTION<br /><br /> WHEN NO_DATA_NEEDED THEN<br /> RAISE;<br /> <br /> <br /> WHEN OTHERS THEN<br /> ECODE := SQLCODE;<br /> EMESG := SQLERRM;<br /> DBMS_OUTPUT.PUT_LINE(TO_CHAR(ECODE) || '-' || EMESG); <br /> dbms_output.put_line('===>>> CLEAN UP (NO_DATA_NEEDED)'); <br /> <br /> <br />END;RACHIThttps://www.blogger.com/profile/04924714417418459844noreply@blogger.comtag:blogger.com,1999:blog-1378961241059327992.post-54764617351799211602015-06-30T07:19:51.627+02:002015-06-30T07:19:51.627+02:00Hi, Its a very useful Post. I am trying to accompl...Hi, Its a very useful Post. I am trying to accomplish the same thing. But my XML tags are little Different. Here is my XML sample:-<br /><br /> <br /> 7425<br /> BANK OF AMERICA CENTER<br /> 42803075<br /> <br /> 700 LOUISIANA ST<br /> HOUSTON<br /> TEXAS<br /> 77002<br /> <br /> <br /> 1557141<br /> M-M PROPERTIES<br /> 7137286020<br /> HTTP://WWW.MMPROP.COM<br /> <br /> <br /> <br /> 37230<br /> ONE ALLEN CENTER<br /> 13330559<br /> <br /> 500 DALLAS ST<br /> HOUSTON<br /> TEXAS<br /> 77002<br /> <br /> <br /> 1557141<br /> M-M PROPERTIES<br /> 7137286020<br /> HTTP://WWW.MMPROP.COM<br /> <br /> <br /> <br />I am able to get the BUILDINGS and BUILDINGADDRESS. But I am not able to get the PROPERTYMANAGER tag. <br /><br />Thanks,<br />Rachit Jauhari.<br />RACHIThttps://www.blogger.com/profile/04924714417418459844noreply@blogger.comtag:blogger.com,1999:blog-1378961241059327992.post-22321878469054149062014-05-19T22:03:54.822+02:002014-05-19T22:03:54.822+02:00Hi, Chithambaram
Well, pasting XML into a Blogger...Hi, Chithambaram<br /><br />Well, pasting XML into a Blogger comment is not very useful, as you can see for yourself - all the XML tags are missing ;-) And your coding is also missing - presumably the comment has become too large for Blogger. Sorry about that - Blogger is not really optimal for "support" type comments :-(<br /><br />As to "performance problem" - that is a wide question. What kind of performance problem?<br /><br />If I can I'll try to help if there's a bit more detail. Rather than pasting into Blogger comment, can you upload XML and code to some file sharing site? Then you can post the link in a comment along with a description of the performance problem.<br /><br />(I can't guarantee the speed of the reply, though, if it's really really urgent you might have to pay some consultant somewhere for fast action ;-)<br /><br />Regards<br />KimKim Berg Hansenhttps://www.blogger.com/profile/06491635470794828550noreply@blogger.comtag:blogger.com,1999:blog-1378961241059327992.post-11208447808763610782014-05-19T08:06:21.369+02:002014-05-19T08:06:21.369+02:00HI Kim,
Very Nice and most useful post...I am do...HI Kim,<br /><br /> Very Nice and most useful post...I am doing the same work now...But am facing performance problem on Oracle 10g..<br /><br />Could you please help me to resolve my problem..Its urgent...<br />MY XML is"<br /> <br /> <br />- <br />- <br />- <br />- <br /> PHIL <br /> POLLAK <br /> 1591 Miller Rd <br /> Lilburn <br /> GA <br /> 30047 <br /> Sod Sales Direct <br /> null@cybersource.com <br /> US <br /> 6785757676 <br /> <br />- <br />- <br /> 1339 <br /> 11 <br /> 2016 <br /> Visa <br /> <br /> <br />- <br />- <br /> P <br /> 1 <br /> 2015.81 <br /> 0.00 <br /> default <br /> <br /> <br />- <br />- <br /> 0 <br /> DCARDREFUSED <br /> DECLINED <br /> <br />- <br /> <br /> <br /> <br /> <br />- <br /> 3982617706980176056193 <br /> fdiglobal <br /> 2015.81 <br /> USD <br /> 0.00 <br /> Z <br /> Z <br /> 51 <br /> 2015.81 <br /> USD <br /> 474165 <br /> <br />- <br /> 145 4/23/14 <br /> <br /> <br />- <br />- <br /> PHIL <br /> POLLAK <br /> 1591 Miller Rd <br /> Lilburn <br /> GA <br /> 30047 <br /> Polak Enterprises Inc. <br /> null@cybersource.com <br /> US <br /> 6785757676 <br /> <br />- <br />- <br /> 1339 <br /> 11 <br /> 2016 <br /> Visa <br /> <br /> <br />- <br />- <br /> P <br /> 1 <br /> 2015.81 <br /> 0.00 <br /> default <br /> <br /> <br />- <br />- <br /> 0 <br /> DCARDREFUSED <br /> DECLINED <br /> <br />- <br /> <br /> <br /> <br /> <br />- <br /> 3982619956700176056193 <br /> fdiglobal <br /> 2015.81 <br /> USD <br /> 0.00 <br /> Z <br /> Z <br /> 51 <br /> 2015.81 <br /> USD <br /> 474165 <br /> <br />- <br /> 145 4/23/14 <br /> <br /> <br />- <br />- <br /> PHIL <br /> POLAK <br /> 1591 Miller Rd <br /> Lilburn <br /> GA <br /> 30047 <br /> Polak Enterprises Inc. <br /> null@cybersource.com <br /> US <br /> 6785757676 <br /> <br />- <br />- <br /> 1339 <br /> 11 <br /> 2016 <br /> Visa <br /> <br /> <br />- <br />- <br /> P <br /> 1 <br /> 2015.81 <br /> 0.00 <br /> default <br /> <br /> <br />- <br />- <br /> 1 <br /> SOK <br /> Request was processed successfully. <br /> <br />- <br /> 1 <br /> SOK <br /> Request was processed successfully. <br /> <br /> <br />- <br /> 3982625069050176056193 <br /> fdiglobal <br /> 2015.81 <br /> USD <br /> 0.00 <br /> O <br /> 517408 <br /> Z <br /> Z <br /> M <br /> 00 <br /> 2015.81 <br /> USD <br /> 474165 <br /> <br />- <br /> 145 4/23/14 <br /> <br /> <br /> <br /> "<br />and my coding...<br />Anonymoushttps://www.blogger.com/profile/04749999012239292417noreply@blogger.com