This discussion is archived
2 Replies Latest reply: Feb 4, 2013 2:33 AM by odie_63 RSS

loading xml data in oracle table without usingbfilename or external directo

va*447258*15 Newbie
Currently Being Moderated
Hi All,

I would like to know is there any way to upload xml data in oracle without using creating directory inorder to access xml file:

I have used following command but it gives me an empty table as output: Any comments or improvement will be highly appretiated:
create table temp_xml_agent as
 select extract(x,'/agents/text()').getStringVal() x
from(
    select
    xmltype (
        '<?xml version="1.0" encoding="utf-8" ?> 
 <agents count="1382">
<agent>
<name>Nancy Palmer</name> 
<email>npalmer@apr.com</email> 
<agentid>MLSL:00525350</agentid> 
<officeid>58</officeid> 
<website>http://www.nancypalmer.com</website> 
<photo>https://sites.e-agents.com/Uploads/68/41/6841/Agents/agent_8418_NANCY_PALMER_COLOR_HEAD_SHOT_HIGH_QUALITY_2011.jpg</photo> 
<phone_direct>6504344313</phone_direct> 
<phone_cell>6504920200</phone_cell> 
<mod_time>2012-08-31T05:15:06.933</mod_time> 
</agent>
<agent>
<name>Genella Williamson</name> 
<email>genella@apr.com</email> 
<agentid>MLSL:00755754</agentid> 
<officeid>58</officeid> 
<website>http://www.apr.com/genella</website> 
<photo>https://sites.e-agents.com/Uploads/68/41/6841/Agents/agent_8426_genella.jpg</photo> 
<phone_direct>6504344319</phone_direct> 
<phone_cell>6507870839</phone_cell> 
<mod_time>2010-10-30T15:15:07.603</mod_time> 
</agent>
<agent>
<name>Diana Langley</name> 
<email>dlangley@apr.com</email> 
<agentid>MLSL:01256202,SFAR:805608</agentid> 
<officeid>50</officeid> 
<website>http://www.apr.com/DLangley</website> 
<photo>https://sites.e-agents.com/Uploads/68/41/6841/Agents/agent_7848_dlangley.jpg</photo> 
<phone_direct /> 
<phone_cell /> 
<mod_time>2011-06-06T05:15:06.587</mod_time> 
</agent>
</agents>') as x        
    from dual
)
Thanks in advance,

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points