2 Replies Latest reply: Feb 4, 2013 4:33 AM by odie_63 RSS

    loading xml data in oracle table without usingbfilename or external directo

    va*447258*15
      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,