Home » Developer & Programmer » JDeveloper, Java & XML » Converting XML Data to Rows. (11g R2)
Converting XML Data to Rows. [message #660340] Wed, 15 February 2017 03:27 Go to next message
Manoj.Gupta.91
Messages: 239
Registered: March 2008
Location: Delhi
Senior Member
Hi All,

I need to extract XML data and then insert it into table rows. I'm using EXTRACTVALUE to achieve this but it looks I'm doing some mistake. Please help me to do this.

Sample XML
<?xml version="1.0" encoding="UTF-8"?>
<ns0:RespGetList xmlns:ns0="http://abc.org/xyz/scheme/">
   <Head ver="1.0" ts="2016-08-23T19:55:00" orgId="ORGID" msgId="1234" />
   <Txn id="1234" refUrl="" timestamp="2016-08-23T19:55:00" orgTxnId="" refId="" type="FETCHDATA" note="">
      <Resp result="SUCCESS" respCode="000" timestamp="2016-08-23T19:56:18" totReqCnt="3" sucessReqCnt="0" totalTagsInResponse="1737" totalTagsInMsg="0" msgNum="3" totalMsg="18">
         <Exception result="SUCCESS" desc="DESC1" priority="1" lastupdatedTime="2016-04-29T18:53:40" excCode="20" errorCode="0" totalTag="4">
            <Card ourId="123456" />
            <Card ourId="223456" />
            <Card ourId="323456" />
            <Card ourId="423456" />
         </Exception>
         <Exception result="SUCCESS" desc="DESC2" priority="2" lastupdatedTime="2016-04-29T18:53:44" excCode="30" errorCode="0" totalTag="3">
            <Card ourId="11111" />
            <Card ourId="22222" />
            <Card ourId="33333" />
         </Exception>
         <Exception result="SUCCESS" desc="DESC3" priority="3" lastupdatedTime="2016-04-29T18:53:43" excCode="40" errorCode="0" totalTag="3">
            <Card ourId="44444" />
            <Card ourId="55555" />
            <Card ourId="66666" />
         </Exception>
      </Resp>
   </Txn>
</ns0:RespGetExceptionList>

Required Output
desc           ourId     timestamp               myCode
-------------------------------------------------------
DESC1          123456    2016-08-23T19:56:18     20 
DESC1          223456    2016-08-23T19:56:18     20
DESC1          323456    2016-08-23T19:56:18     20
DESC1          423456    2016-08-23T19:56:18     20
DESC2          11111     2016-04-29T18:53:44     30
DESC2          22222     2016-04-29T18:53:44     30
DESC2          33333     2016-04-29T18:53:44     30
DESC3          44444     2016-04-29T18:53:43     40
DESC3          55555     2016-04-29T18:53:43     40
DESC3          66666     2016-04-29T18:53:43     40

Thanks & Regards
Manoj
Re: Converting XML Data to Rows. [message #660345 is a reply to message #660340] Wed, 15 February 2017 04:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Note that your XML is not correct (end tag does not match open tag).
After fixing it:
SQL> with data as (
  2    select xmltype('<?xml version="1.0" encoding="UTF-8"?>
  3  <ns0:RespGetList xmlns:ns0="http://abc.org/xyz/scheme/">
  4     <Head ver="1.0" ts="2016-08-23T19:55:00" orgId="ORGID" msgId="1234" />
  5     <Txn id="1234" refUrl="" timestamp="2016-08-23T19:55:00" orgTxnId="" refId="" type="FETCHDATA" note="">
  6        <Resp result="SUCCESS" respCode="000" timestamp="2016-08-23T19:56:18" totReqCnt="3" sucessReqCnt="0" totalTagsInResponse="1737" totalTagsInMsg="0" msgNum="3" totalMsg="18">
  7           <Exception result="SUCCESS" desc="DESC1" priority="1" lastupdatedTime="2016-04-29T18:53:40" excCode="20" errorCode="0" totalTag="4">
  8              <Card ourId="123456" />
  9              <Card ourId="223456" />
 10              <Card ourId="323456" />
 11              <Card ourId="423456" />
 12           </Exception>
 13           <Exception result="SUCCESS" desc="DESC2" priority="2" lastupdatedTime="2016-04-29T18:53:44" excCode="30" errorCode="0" totalTag="3">
 14              <Card ourId="11111" />
 15              <Card ourId="22222" />
 16              <Card ourId="33333" />
 17           </Exception>
 18           <Exception result="SUCCESS" desc="DESC3" priority="3" lastupdatedTime="2016-04-29T18:53:43" excCode="40" errorCode="0" totalTag="3">
 19              <Card ourId="44444" />
 20              <Card ourId="55555" />
 21              <Card ourId="66666" />
 22           </Exception>
 23        </Resp>
 24     </Txn>
 25  </ns0:RespGetList>') val
 26  from dual )
 27  select extractvalue(value(x), '/Exception/@desc') "desc",
 28         extractvalue(value(y), '/Card[position()='||line||']/@ourId') "ourId",
 29         extractvalue(value(x), '/Exception/@lastupdatedTime') "timestamp",
 30         extractvalue(value(x), '/Exception/@excCode') "myCode"
 31  from data,
 32       table(xmlsequence(extract(val,'//Exception'))) x,
 33       table(xmlsequence(extract(value(x), '//Card'))) y,
 34       (select level line from dual connect by level <= 5) lines
 35  where extractvalue(value(y), '/Card[position()='||line||']/@ourId') is not null
 36  order by extractvalue(value(x), '/Exception/@priority'), line
 37  /
desc  ourId  timestamp           myCode
----- ------ ------------------- ------
DESC1 123456 2016-04-29T18:53:40 20
DESC1 223456 2016-04-29T18:53:40 20
DESC1 323456 2016-04-29T18:53:40 20
DESC1 423456 2016-04-29T18:53:40 20
DESC2 11111  2016-04-29T18:53:44 30
DESC2 33333  2016-04-29T18:53:44 30
DESC2 22222  2016-04-29T18:53:44 30
DESC3 55555  2016-04-29T18:53:43 40
DESC3 44444  2016-04-29T18:53:43 40
DESC3 66666  2016-04-29T18:53:43 40
Re: Converting XML Data to Rows. [message #660347 is a reply to message #660345] Wed, 15 February 2017 04:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Or using XMLTABLE (which is the recommended way):
SQL> with data as (
  2    select xmltype('<?xml version="1.0" encoding="UTF-8"?>
  3  <ns0:RespGetList xmlns:ns0="http://abc.org/xyz/scheme/">
  4     <Head ver="1.0" ts="2016-08-23T19:55:00" orgId="ORGID" msgId="1234" />
  5     <Txn id="1234" refUrl="" timestamp="2016-08-23T19:55:00" orgTxnId="" refId="" type="FETCHDATA" note="">
  6        <Resp result="SUCCESS" respCode="000" timestamp="2016-08-23T19:56:18" totReqCnt="3" sucessReqCnt="0" totalTagsInResponse="1737" totalTagsInMsg="0" msgNum="3" totalMsg="18">
  7           <Exception result="SUCCESS" desc="DESC1" priority="1" lastupdatedTime="2016-04-29T18:53:40" excCode="20" errorCode="0" totalTag="4">
  8              <Card ourId="123456" />
  9              <Card ourId="223456" />
 10              <Card ourId="323456" />
 11              <Card ourId="423456" />
 12           </Exception>
 13           <Exception result="SUCCESS" desc="DESC2" priority="2" lastupdatedTime="2016-04-29T18:53:44" excCode="30" errorCode="0" totalTag="3">
 14              <Card ourId="11111" />
 15              <Card ourId="22222" />
 16              <Card ourId="33333" />
 17           </Exception>
 18           <Exception result="SUCCESS" desc="DESC3" priority="3" lastupdatedTime="2016-04-29T18:53:43" excCode="40" errorCode="0" totalTag="3">
 19              <Card ourId="44444" />
 20              <Card ourId="55555" />
 21              <Card ourId="66666" />
 22           </Exception>
 23        </Resp>
 24     </Txn>
 25  </ns0:RespGetList>') val
 26  from dual )
 27  select "desc", "ourId", "timestamp", "myCode"
 28  from data,
 29       xmltable('//Exception' passing val
 30                columns
 31                  exception   xmltype      path '/Exception',
 32                  "desc"      varchar2(6)  path '/Exception/@desc',
 33                  "timestamp" varchar2(19) path '/Exception/@lastupdatedTime',
 34                  "myCode"    number       path '/Exception/@excCode',
 35                  priority    number       path '/Exception/@priority'),
 36       xmltable('//Card' passing exception
 37                columns
 38                  position for ordinality,
 39                  "ourId" varchar2(6) path '/Card/@ourId')
 40  order by priority, position
 41  /
desc   ourId  timestamp               myCode
------ ------ ------------------- ----------
DESC1  123456 2016-04-29T18:53:40         20
DESC1  223456 2016-04-29T18:53:40         20
DESC1  323456 2016-04-29T18:53:40         20
DESC1  423456 2016-04-29T18:53:40         20
DESC2  11111  2016-04-29T18:53:44         30
DESC2  22222  2016-04-29T18:53:44         30
DESC2  33333  2016-04-29T18:53:44         30
DESC3  44444  2016-04-29T18:53:43         40
DESC3  55555  2016-04-29T18:53:43         40
DESC3  66666  2016-04-29T18:53:43         40

[Updated on: Wed, 15 February 2017 04:42]

Report message to a moderator

Re: Converting XML Data to Rows. [message #660364 is a reply to message #660347] Wed, 15 February 2017 06:27 Go to previous messageGo to next message
Manoj.Gupta.91
Messages: 239
Registered: March 2008
Location: Delhi
Senior Member
Hi Michel,

Thank you so much for your help and support. It is working perfectly fine.

Regards
Manoj
Re: Converting XML Data to Rows. [message #660436 is a reply to message #660364] Thu, 16 February 2017 06:45 Go to previous messageGo to next message
Manoj.Gupta.91
Messages: 239
Registered: March 2008
Location: Delhi
Senior Member
Hi All,

Just to add one experience I used both of these options with 50,000 records in XMXL.

Option 1 using EXTRACTVALUE took about 6 minutes. Whereas option 2 using XMLTABLE took less than a second (1 second).

From performance point of view XMLTABLE is much more faster.

Thanks & Regards
Manoj
Re: Converting XML Data to Rows. [message #660439 is a reply to message #660436] Thu, 16 February 2017 07:36 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks for the feedback.

Previous Topic: Generate XML for table definiton
Next Topic: XML file generation
Goto Forum:
  


Current Time: Thu Mar 28 13:39:31 CDT 2024