Home » Developer & Programmer » JDeveloper, Java & XML » xml extract -> xml string-join (11.2.0.1.0 - Production, jdeveloper 11g R2 11.1.2.1.0, Win7 Prof. 64-bit, Weblogic server)
xml extract -> xml string-join [message #543358] Tue, 14 February 2012 06:47 Go to next message
wtfn00b
Messages: 115
Registered: October 2011
Location: Latvia
Senior Member
Good day, I want to ask how can I do this:

XML:
<properties>
    <!-- 2. Nekustamie īpašumi -->
    <property>
      <addresses>
        <address>
          <var_id>101886605</var_id>
          <text>Ernestīnes iela 16, Rīga</text>
        </address>
      </addresses>
      <lb_name>Rīgas pilsētas zemesgrāmata</lb_name>
      <folio_nr>16462</folio_nr>
      <folio_id>10000164620000</folio_id>
    </property>
    <property>
      <addresses>
        <address>
          <var_id>112728727</var_id>
          <text>Ernestīnes iela 16 - 1, Rīga</text>
        </address>
      </addresses>
      <lb_name>Rīgas pilsētas zemesgrāmata</lb_name>
      <folio_nr>16462 1</folio_nr>
      <folio_id>10000164620001</folio_id>
    </property>
    <!-- Kad nepieciešams, pievieno vairākus īpašumus -->
  </properties>


Using code:
if xmlclaim.extract('(//address/text)['||to_char(n)||']/child::text()') is not null then
                address:=xmlclaim.extract('(//address/text)['||to_char(n)||']/child::text()').getstringval();


Want to get both values like this:
Ernestīnes iela 16, Rīga, Ernestīnes iela 16 - 1, Rīga


But I only can get like this:
Ernestīnes iela 16, RīgaErnestīnes iela 16 - 1, Rīga
or
Ernestīnes iela 16, Rīga
or
Ernestīnes iela 16 - 1, Rīga


Can you tell me how to get the correct result ?

Best regards,
wtfn00b.
Re: xml extract -> xml string-join [message #543378 is a reply to message #543358] Tue, 14 February 2012 08:04 Go to previous messageGo to next message
Michel Cadot
Messages: 67138
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can only get result from your data.

Tell us WITH WORDS what you want.

Regards
Michel
Re: xml extract -> xml string-join [message #543402 is a reply to message #543378] Tue, 14 February 2012 10:45 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
Your partial IF statement suggests that you are doing this in an inefficient, slow manner, looping through a cursor, when you should be using just a SQL select statement instead. Please see the demonstration below, showing two different syntaxes. The first uses the older TABLE and XMLSEQUENCE and the second uses the newer XMLTABLE. I have used the 11g LISTAGG with both, but you could use any string aggregation method you like.

SCOTT@orcl_11gR2> WITH
  2    data AS
  3  	 (SELECT XMLTYPE ('<properties>
  4  	 <!-- 2. Nekustamie īpašumi -->
  5  	 <property>
  6  	   <addresses>
  7  	     <address>
  8  	       <var_id>101886605</var_id>
  9  	       <text>Ernestīnes iela 16, Rīga</text>
 10  	     </address>
 11  	   </addresses>
 12  	   <lb_name>Rīgas pilsētas zemesgrāmata</lb_name>
 13  	   <folio_nr>16462</folio_nr>
 14  	   <folio_id>10000164620000</folio_id>
 15  	 </property>
 16  	 <property>
 17  	   <addresses>
 18  	     <address>
 19  	       <var_id>112728727</var_id>
 20  	       <text>Ernestīnes iela 16 - 1, Rīga</text>
 21  	     </address>
 22  	   </addresses>
 23  	   <lb_name>Rīgas pilsētas zemesgrāmata</lb_name>
 24  	   <folio_nr>16462 1</folio_nr>
 25  	   <folio_id>10000164620001</folio_id>
 26  	 </property>
 27  	 <!-- Kad nepieciešams, pievieno vairākus īpašumus -->
 28    </properties>') xmlclaim
 29  	 FROM	DUAL)
 30  SELECT LISTAGG (EXTRACTVALUE (VALUE (x), '//address/text'), ', ')
 31  	      WITHIN GROUP (ORDER BY ROWNUM) addresses
 32  FROM   data,
 33  	    TABLE (XMLSEQUENCE (EXTRACT (data.xmlclaim, '//properties/property'))) x
 34  /

ADDRESSES
--------------------------------------------------------------------------------
Ernestīnes iela 16, Rīga, Ernestīnes iela 16 - 1, Rīga

1 row selected.


SCOTT@orcl_11gR2> WITH
  2    data AS
  3  	 (SELECT XMLTYPE ('<properties>
  4  	 <!-- 2. Nekustamie īpašumi -->
  5  	 <property>
  6  	   <addresses>
  7  	     <address>
  8  	       <var_id>101886605</var_id>
  9  	       <text>Ernestīnes iela 16, Rīga</text>
 10  	     </address>
 11  	   </addresses>
 12  	   <lb_name>Rīgas pilsētas zemesgrāmata</lb_name>
 13  	   <folio_nr>16462</folio_nr>
 14  	   <folio_id>10000164620000</folio_id>
 15  	 </property>
 16  	 <property>
 17  	   <addresses>
 18  	     <address>
 19  	       <var_id>112728727</var_id>
 20  	       <text>Ernestīnes iela 16 - 1, Rīga</text>
 21  	     </address>
 22  	   </addresses>
 23  	   <lb_name>Rīgas pilsētas zemesgrāmata</lb_name>
 24  	   <folio_nr>16462 1</folio_nr>
 25  	   <folio_id>10000164620001</folio_id>
 26  	 </property>
 27  	 <!-- Kad nepieciešams, pievieno vairākus īpašumus -->
 28    </properties>') xmlclaim
 29  	 FROM	DUAL)
 30  SELECT LISTAGG (x.address_text, ', ') WITHIN GROUP (ORDER BY ROWNUM) addresses
 31  FROM   data,
 32  	    XMLTABLE
 33  	      ('//properties/property' PASSING data.xmlclaim
 34  	       COLUMNS address_text PATH '//address/text') x
 35  /

ADDRESSES
--------------------------------------------------------------------------------
Ernestīnes iela 16, Rīga, Ernestīnes iela 16 - 1, Rīga

1 row selected.

Re: xml extract -> xml string-join [message #543406 is a reply to message #543402] Tue, 14 February 2012 11:10 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
If your xmlclaim is in a PL/SQL variable, not a table, then you could use one of the two methods below, similar to the two demonstrated previously.

SCOTT@orcl_11gR2> DECLARE
  2    xmlclaim  XMLTYPE;
  3    address	 VARCHAR2 (100);
  4  BEGIN
  5    xmlclaim := XMLTYPE ('<properties>
  6  	 <!-- 2. Nekustamie īpašumi -->
  7  	 <property>
  8  	   <addresses>
  9  	     <address>
 10  	       <var_id>101886605</var_id>
 11  	       <text>Ernestīnes iela 16, Rīga</text>
 12  	     </address>
 13  	   </addresses>
 14  	   <lb_name>Rīgas pilsētas zemesgrāmata</lb_name>
 15  	   <folio_nr>16462</folio_nr>
 16  	   <folio_id>10000164620000</folio_id>
 17  	 </property>
 18  	 <property>
 19  	   <addresses>
 20  	     <address>
 21  	       <var_id>112728727</var_id>
 22  	       <text>Ernestīnes iela 16 - 1, Rīga</text>
 23  	     </address>
 24  	   </addresses>
 25  	   <lb_name>Rīgas pilsētas zemesgrāmata</lb_name>
 26  	   <folio_nr>16462 1</folio_nr>
 27  	   <folio_id>10000164620001</folio_id>
 28  	 </property>
 29  	 <!-- Kad nepieciešams, pievieno vairākus īpašumus -->
 30    </properties>');
 31    SELECT LISTAGG (EXTRACTVALUE (VALUE (x), '//address/text'), ', ')
 32  		WITHIN GROUP (ORDER BY ROWNUM) addresses
 33    INTO   address
 34    FROM   TABLE (XMLSEQUENCE (EXTRACT (xmlclaim, '//properties/property'))) x;
 35    DBMS_OUTPUT.PUT_LINE (address);
 36  END;
 37  /
Ernestīnes iela 16, Rīga, Ernestīnes iela 16 - 1, Rīga

PL/SQL procedure successfully completed.


SCOTT@orcl_11gR2> DECLARE
  2    xmlclaim  XMLTYPE;
  3    address	 VARCHAR2 (100);
  4  BEGIN
  5    xmlclaim := XMLTYPE ('<properties>
  6  	 <!-- 2. Nekustamie īpašumi -->
  7  	 <property>
  8  	   <addresses>
  9  	     <address>
 10  	       <var_id>101886605</var_id>
 11  	       <text>Ernestīnes iela 16, Rīga</text>
 12  	     </address>
 13  	   </addresses>
 14  	   <lb_name>Rīgas pilsētas zemesgrāmata</lb_name>
 15  	   <folio_nr>16462</folio_nr>
 16  	   <folio_id>10000164620000</folio_id>
 17  	 </property>
 18  	 <property>
 19  	   <addresses>
 20  	     <address>
 21  	       <var_id>112728727</var_id>
 22  	       <text>Ernestīnes iela 16 - 1, Rīga</text>
 23  	     </address>
 24  	   </addresses>
 25  	   <lb_name>Rīgas pilsētas zemesgrāmata</lb_name>
 26  	   <folio_nr>16462 1</folio_nr>
 27  	   <folio_id>10000164620001</folio_id>
 28  	 </property>
 29  	 <!-- Kad nepieciešams, pievieno vairākus īpašumus -->
 30    </properties>');
 31    SELECT LISTAGG (x.address_text, ', ') WITHIN GROUP (ORDER BY ROWNUM) addresses
 32    INTO   address
 33    FROM   XMLTABLE
 34  		('//properties/property' PASSING xmlclaim
 35  		 COLUMNS address_text PATH '//address/text') x;
 36    DBMS_OUTPUT.PUT_LINE (address);
 37  END;
 38  /
Ernestīnes iela 16, Rīga, Ernestīnes iela 16 - 1, Rīga

PL/SQL procedure successfully completed.

Re: xml extract -> xml string-join [message #543433 is a reply to message #543406] Tue, 14 February 2012 12:43 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
I suspect that what you were trying to do was something like below. Either selecting the count or guessing at the maximum value and checking if the value is null and concatenating within a loop is likely slower than just using a select statement as in the previous examples. I have provided this for comparison only.

SCOTT@orcl_11gR2> DECLARE
  2    xmlclaim  XMLTYPE;
  3    address	 VARCHAR2 (100);
  4    v_count	 NUMBER;
  5  BEGIN
  6    xmlclaim := XMLTYPE ('<properties>
  7  	 <!-- 2. Nekustamie īpašumi -->
  8  	 <property>
  9  	   <addresses>
 10  	     <address>
 11  	       <var_id>101886605</var_id>
 12  	       <text>Ernestīnes iela 16, Rīga</text>
 13  	     </address>
 14  	   </addresses>
 15  	   <lb_name>Rīgas pilsētas zemesgrāmata</lb_name>
 16  	   <folio_nr>16462</folio_nr>
 17  	   <folio_id>10000164620000</folio_id>
 18  	 </property>
 19  	 <property>
 20  	   <addresses>
 21  	     <address>
 22  	       <var_id>112728727</var_id>
 23  	       <text>Ernestīnes iela 16 - 1, Rīga</text>
 24  	     </address>
 25  	   </addresses>
 26  	   <lb_name>Rīgas pilsētas zemesgrāmata</lb_name>
 27  	   <folio_nr>16462 1</folio_nr>
 28  	   <folio_id>10000164620001</folio_id>
 29  	 </property>
 30  	 <!-- Kad nepieciešams, pievieno vairākus īpašumus -->
 31    </properties>');
 32    SELECT TO_NUMBER (x.COLUMN_VALUE)
 33    INTO   v_count
 34    FROM   XMLTABLE ('count(//property/addresses/address/text)' PASSING xmlclaim) x;
 35    FOR n in 1 .. v_count
 36    LOOP
 37  	 address := address || ', ' ||
 38  	   xmlclaim.extract('(//address/text)['||to_char(n)||']/text()').getstringval();
 39    END LOOP;
 40    address := ltrim (address, ', ');
 41    DBMS_OUTPUT.PUT_LINE (address);
 42  END;
 43  /
Ernestīnes iela 16, Rīga, Ernestīnes iela 16 - 1, Rīga

PL/SQL procedure successfully completed.

Re: xml extract -> xml string-join [message #543584 is a reply to message #543433] Wed, 15 February 2012 08:01 Go to previous message
wtfn00b
Messages: 115
Registered: October 2011
Location: Latvia
Senior Member
Wow, thanks a lot, very helpful.

Best regards,
wtfn00b.
Previous Topic: XML extract in PL/SQL
Next Topic: Java Stored Procedure - if expression
Goto Forum:
  


Current Time: Sat May 30 11:24:55 CDT 2020