Home » Developer & Programmer » JDeveloper, Java & XML » XML extract in PL/SQL (11.2.0.1.0 - Production, jdeveloper 11g R2 11.1.2.1.0, Win7 Prof. 64-bit, Weblogic server)
XML extract in PL/SQL [message #543313] Tue, 14 February 2012 01:52 Go to next message
wtfn00b
Messages: 115
Registered: October 2011
Location: Latvia
Senior Member
Good day, I have tried to use xml extract in PL/SQL.

My In data:
 <claimants>
    <claimant>
      <status_id>1</status_id>
      <status>Īpašnieks</status>
      <natural_person>
        <person_id>27075312958</person_id>
        <first_name>Ints</first_name>
        <surname>*</surname>
        <other_names/>
      </natural_person>
      <address>Dienvidu iela 7 k-2 - 89, Salaspils, Salaspils nov.</address>
      <shares>1/1</shares>
      <contacts>
        <phone>22113344</phone>
        <emails>
          <email>janis@test.com</email>
        </emails>
      </contacts>
    </claimant>
    <claimant>
      <status_id>3</status_id>
      <status>Trešā ieinteresētā puse</status>
      <legal_person>
        <unified_id>11111111111</unified_id>
        <full_name>A/S ManaBanka</full_name>
      </legal_person>
      <address>Balasta dambis 1B, Rīga</address>
      <shares></shares>
      <contacts>
        <phone>22331144</phone>
        <emails>
          <email>test1@test.com</email>
        </emails>
      </contacts>
    </claimant>
  </claimants>


There is 2 e-mail addreses.
I'm trying to get theim out of xml:
if xmlclaim.extract('//claimants/claimant/contacts/emails/email['||to_char(i)||']/child::text()') is not null then
            emails:=xmlclaim.extract('//claimants/claimant/contacts/emails/email['||to_char(i)||']/child::text()').getstringval();


But I get first and second like this: janis@test.comtest1>@test.com

I want to take theim one by one.
And than send mail to each e-mail address.

Please help solve my problem.

Best regards,
wtfn00b.
Re: XML extract in PL/SQL [message #543316 is a reply to message #543313] Tue, 14 February 2012 01:56 Go to previous messageGo to next message
wtfn00b
Messages: 115
Registered: October 2011
Location: Latvia
Senior Member
I'm very sorry but somehow posted 2 posts.
Can some admin please remove one.
Thank you.

Best regards,
wtfn00b.
Re: XML extract in PL/SQL [message #543317 is a reply to message #543316] Tue, 14 February 2012 01:59 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Removed your duplicate message.
http://www.orafaq.com/forum/mv/msg/177626/537228/136607/#msg_537228

please Post what you have tried.

Sriram
Re: XML extract in PL/SQL [message #543318 is a reply to message #543317] Tue, 14 February 2012 02:06 Go to previous messageGo to next message
wtfn00b
Messages: 115
Registered: October 2011
Location: Latvia
Senior Member
I posted what I have tried but all email addresses goes in one value.
And I don't get it why ?

Best regards,
wtfn00b.
Re: XML extract in PL/SQL [message #543321 is a reply to message #543318] Tue, 14 February 2012 02:35 Go to previous messageGo to next message
wtfn00b
Messages: 115
Registered: October 2011
Location: Latvia
Senior Member
How it's possible that `loop` is going but
in first time takes all values and puts in one out value Sad ?


Best regards,
wtfn00b.
Re: XML extract in PL/SQL [message #543340 is a reply to message #543321] Tue, 14 February 2012 04:29 Go to previous messageGo to next message
wtfn00b
Messages: 115
Registered: October 2011
Location: Latvia
Senior Member
I found this url - http://www.w3schools.com/xpath/xpath_syntax.asp
`/bookstore/book[1]` - not helping me Sad

Best regards,
wtfn00b.
Re: XML extract in PL/SQL [message #543344 is a reply to message #543313] Tue, 14 February 2012 06:15 Go to previous messageGo to next message
transfer
Messages: 53
Registered: August 2007
Location: transfer
Member
with data as (select xmltype(
'<claimants>
    <claimant>
      <status_id>1</status_id>
      <status>Īpašnieks</status>
      <natural_person>
        <person_id>27075312958</person_id>
        <first_name>Ints</first_name>
        <surname>*</surname>
        <other_names/>
      </natural_person>
      <address>Dienvidu iela 7 k-2 - 89, Salaspils, Salaspils nov.</address>
      <shares>1/1</shares>
      <contacts>
        <phone>22113344</phone>
        <emails>
          <email>janis@test.com</email>
        </emails>
      </contacts>
    </claimant>
    <claimant>
      <status_id>3</status_id>
      <status>Trešā ieinteresētā puse</status>
      <legal_person>
        <unified_id>11111111111</unified_id>
        <full_name>A/S ManaBanka</full_name>
      </legal_person>
      <address>Balasta dambis 1B, Rīga</address>
      <shares></shares>
      <contacts>
        <phone>22331144</phone>
        <emails>
          <email>test1@test.com</email>
        </emails>
      </contacts>
    </claimant>
  </claimants>'
, null, 1, 1) xmlclaim from dual
) select "status_id", "status", email, person_id
from data, XMLTABLE(
  '$X//claimant' PASSING xmlclaim AS X 
  columns 
  "status_id" number,
  "status",
  EMAIL varchar2(63) PATH 'contacts/emails/email',
  PERSON_ID number PATH '//person_id',
  unified_id number path '//unified_id'
);

status_id     status                   EMAIL        PERSON_ID 
------------  ------------------------ ------------ -----------
1             Īpašnieks                janis@test.c 27075312958
3             Trešā ieinteresētā p     test1@test.c 
Re: XML extract in PL/SQL [message #543345 is a reply to message #543344] Tue, 14 February 2012 06:19 Go to previous messageGo to next message
wtfn00b
Messages: 115
Registered: October 2011
Location: Latvia
Senior Member
Fix my problem with this:

if xmlclaim.extract('(//emails/email)['||to_char(i)||']/child::text()') is not null then
   emails:=xmlclaim.extract('(//emails/email)['||to_char(i)||']/child::text()').getstringval();


With round bracets only taking one by one value.

Best regards,
wtfn00b
Re: XML extract in PL/SQL [message #543346 is a reply to message #543345] Tue, 14 February 2012 06:19 Go to previous message
wtfn00b
Messages: 115
Registered: October 2011
Location: Latvia
Senior Member
Thanks sashton for good SQL.

Best regards,
wtfn00b.
Previous Topic: Extract data from XML (merged)
Next Topic: xml extract -> xml string-join
Goto Forum:
  


Current Time: Mon Jun 01 21:39:45 CDT 2020