asked on 7/21/2008

Duplicate elements in XML extract

Hi

I need help related to XML extract for duplicate elements.
below is the sample XML file which has duplicate elements

when I try to extract using below statement. I am not able to extract single value at a time

SELECT
a.xml.extract('//PP/REGISTER_III/text()').getStringVal() AS "REFISTER_III",
a.xml.extract('//PP/REGISTER_SUBJECT/text()').getStringVal() AS "REGISTER_SUBJECT"
FROM xml_tab a

Can you please help me how can I extract the values based on occurrence.

Thanks

<?xml version="1.0" encoding="UTF-8" ?> 
 <PP>
  <NNN6>Yes</NNN6> 
  <NNN7>Yes</NNN7> 
  <CB71>Yes</CB71> 
  <NNN8>Yes</NNN8> 
  <NNN9>No</NNN9> 
  <CB91>N/A</CB91> 
  <REGISTER_SUBJECT>10401</REGISTER_SUBJECT> 
  <REGISTER_III>1</REGISTER_III> 
  <REGISTER_DATE2 /> 
  <INFOMED_ASSENT /> 
  <REGISTER_DATE1>2007-10-22</REGISTER_DATE1> 
  <REGISTER_DATE_SIGNED1>2007-12-10</REGISTER_DATE_SIGNED1> 
  <REGISTER_LIST_DATE_SIGNED2 /> 
  <Com3 /> 
  <REGISTER_SUBJECT>10402</REGISTER_SUBJECT> 
  <REGISTER_III>1</REGISTER_III> 
  <REGISTER_DATE2 /> 
  <INFOMED_ASSENT /> 
  <REGISTER_DATE1>2007-10-22</REGISTER_DATE1> 
  <REGISTER_DATE_SIGNED1>2008-04-10</REGISTER_DATE_SIGNED1> 
  <REGISTER_LIST_DATE_SIGNED2 /> 
  <REGISTER_SUBJECT>10403</REGISTER_SUBJECT> 
  <REGISTER_III>1</REGISTER_III> 
  <REGISTER_DATE2 /> 
  <INFOMED_ASSENT /> 
  <REGISTER_DATE1>2007-10-22</REGISTER_DATE1> 
  <REGISTER_DATE_SIGNED1>2008-04-14</REGISTER_DATE_SIGNED1> 
  <REGISTER_LIST_DATE_SIGNED2 /> 
 </PP>

Last Comment

7/22/2008

7/21/2008

I'm not sure I understand your question.

Your xpath queries "should" only return 1 value, the first one they find that matches your xpath.

what is the output you expect?

basirana

7/21/2008

ASKER

Hi

Expecting each occurrence in a single row.
Now the output what I get is all occurrence in Single row.

Thanks

Sean Stuber

7/21/2008

use xmlsequence to return each node as a row

SELECT EXTRACTVALUE(VALUE(x), '/REGISTER_III') refister_iii,
EXTRACTVALUE(VALUE(x), '/REGISTER_SUBJECT') register_subject
FROM xml_tab a,
TABLE(XMLSEQUENCE(EXTRACT(xml, '//PP/REGISTER_III | //PP/REGISTER_SUBJECT'))) x

basirana

7/21/2008

ASKER

The above sql statement will will return each value in one row.
But I need all this values in single row.
and there are repeated elements that will come under next row.

10401
1

2007-10-22
2007-12-10

--Format I am looking for is 
 
REGISTER_SUBJECT REGISTER_III REGISTER_DATE1
---------------- ------------ --------------
10401            1            2007-10-22
10402            1            2007-10-22
10403            1            2007-10-22

Open in new window

ASKER CERTIFIED SOLUTION

Sean Stuber

7/21/2008

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

basirana

7/22/2008

ASKER

Thank you