multiple xml body insert into oracle table-CodePudding

Requiremet - have multiple xml root body in a file and would like to load every root body into a separate row in XMLtype oracle table

for example.

<play><player>EZEE090</player><screentime>12.23min</screentime><endtime>11PM</endtime></play>
<play><player>ESONY010</player><screentime>12.76min</screentime><endtime>10PM</endtime></play>
<play><player>ESAB020</player><screentime>11.55min</screentime><endtime>08PM</endtime></play>
<play><player>ENDT050</player><screentime>18.11min</screentime><endtime>06PM</endtime></play>

I have tried using sql loader but its not liking because my xml file doesn't have opening and closing tag. Even If I add it's inserting into just one row

Please advise method to load the file into number of rows based on number of xml body in the file.

CodePudding user response：

Wrap your XML in a root tag and then use XMLTABLE in an INSERT statement:

INSERT INTO table_name (xml)
SELECT play
FROM   XMLTABLE(
         '/root/play'
         PASSING XMLTYPE(
           '<root>'
           || '<play><player>EZEE090</player><screentime>12.23min</screentime><endtime>11PM</endtime></play>
<play><player>ESONY010</player><screentime>12.76min</screentime><endtime>10PM</endtime></play>
<play><player>ESAB020</player><screentime>11.55min</screentime><endtime>08PM</endtime></play>
<play><player>ENDT050</player><screentime>18.11min</screentime><endtime>06PM</endtime></play>'
           || '</root>'
         )
         COLUMNS
           play XMLTYPE PATH '.'
       )

db<>fiddle here

CodePudding user response：

thanks MT0 , appreciate this works. I am looking to load 3 GB of XML file into multiple rows in XML Type table.. is there anyway to insert individual rows in a table using sqlloader or any other means ?