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  |
 |
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 #543402 is a reply to message #543378] |
Tue, 14 February 2012 10:45  |
 |
Barbara Boehmer
Messages: 9088
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 |
|
Barbara Boehmer
Messages: 9088
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 |
|
Barbara Boehmer
Messages: 9088
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.
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 08:47:50 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
