I have some XML (example given)
<emails>
<email email_id="[email protected]"/>
<email email_id="[email protected]"/>
...
<email email_id="[email protected]"/>
<emails>
I want to get the email_id from each email node and put it into a string. e.g "[email protected], [email protected], [email protected]..."
I am guessing I need to use xmltable in some form but can't work out how to get the values to use other than writing them in a select statement.
SELECT
LISTAGG(emailId, '; ')
WITHIN GROUP(ORDER BY emailId) "EmailList"
FROM
(
select emailId
FROM XMLTABLE('/emails/email'
PASSING
xmltype('
<emails>
<email email_id="[email protected]"/>
<email email_id="[email protected]"/>
<email email_id="[email protected]"/>
</emails>
')
COLUMNS
emailId varchar2(200) PATH './@email_id'
) xmlt
)
;
You can use XMLTYPE options for this
SQL> set serverout on
SQL> declare
2 x xmltype := xmltype(
3 '<emails>
4 <email email_id="[email protected]"/>
5 <email email_id="[email protected]"/>
6 <email email_id="[email protected]"/>
7 </emails>');
8
9 email_list sys.odcivarchar2list := sys.odcivarchar2list();
10
11 e varchar2(100);
12 begin
13 for i in 1 .. 1000
14 loop
15 if x.existSNode('/emails/email['||i||']') > 0 then
16 e:= x.extract('/emails/email['||i||']/@email_id').getstringVal();
17 dbms_output.put_line(e);
18 email_list.extend;
19 email_list(i) := e;
20 else
21 exit;
22 end if;
23 end loop;
24 end;
25 /
[email protected]
[email protected]
[email protected]
PL/SQL procedure successfully completed.
