| Converting XML Data to Rows. [message #660340] |
Wed, 15 February 2017 03:27  |
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  |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
|
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #660436 is a reply to message #660364] |
Thu, 16 February 2017 06:45 |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
