Parse text data to extract sub-string

[asandy1234]

We have a XML saved as CLOB datatype in a DB2 table. In the XML text we need to extract the substring between nodes <text> and </text>.
Since the number of occurence of this is not contant(multiple occurence), we have difficulty using SUBSTR function.
Any guidance is appreciated.

Thanks,

 

[MatthiasB]

Hi,

this is a month ago (didn't visit this for for months), yet maybe this still helps - if I understood the problem right, here you slice nested tags:

[tt]data txt;
drop i;
str = 'Out1<text>In1<text>In2<TeXt>Not1</TeXt></text>Post1</text>Out2';
output;
do while (str ne '');
i = index (str, '<text>');
if i ne 0 then do;
str = reverse (substr (str, i+length('<text>')));
i = index (str, reverse('</text>'));
if i ne 0 then do;
str = strip (reverse (substr (str, i+length('</text>'))));
output;
end; else
str = '';
end; else
str = '';
end;
run;[/tt]

What is on the table as a result:

[tt]Out1<text>In1<text>In2<TeXt>Not1</TeXt></text>Post1</text>Out2
In1<text>In2<TeXt>Not1</TeXt></text>Post1
In2<TeXt>Not1</TeXt>[/tt]

Cheers,
Matthias