Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Concat+string-joing and xmltable

DENISKA80May 23 2023

hi there

I have this query

with t as(select XMLTYPE('
<root>
<x>
 <t>t1</t>
 <c>
  <y>1</y>
  <z>2</z>
 </c>
 <c>
  <y>111</y>
  <z>212</z>
 </c>
</x>
<x>
 <t>t2</t>
 <c>
  <y>112</y>
  <z>2111</z>
 </c>
</x>
</root>') cont1 from dual)
select
x,
cast(res as varchar2(1000)) FROM 
(select cont1 xm from t) a,
Xmltable('root/x' Passing a.xm  Columns x varchar(10) path 't',
                     res  Clob  path 'string-join((c/y,c/z),";")');
                     

and the result ot it is

1 t1 1;111;2;212
2 t2 112;2111

But I want to get smth like this

1 t1 Number 1 is equal to 111; Number 2 is equal to 212
2 t2 Number 112 is equal to 2111

“Number” and “is equal to” is just text i have to add to the inner result

thanx in advance

This post has been answered by User_3ABCE on May 23 2023
Jump to Answer
Comments
Post Details
Added on May 23 2023
13 comments
1,023 views