Smart way to extract XML tags in DB2

Question

I have this issue and I know workaround solution, but it's not pretty and maybe you can offer something smarter.. ?

I have a table with one column containing XML stuff that looks like this:

bunch of text with tags

<AdrLine> Some address text </AdrLine> 
<AdrLine> More text with address </AdrLine> 
<AdrLine> postal codes etc </AdrLine>

bunch of text with tags

I need only Address from here and my solution is - find "AdrLine" and "/AdrLine" (with INSTR) count the characters in between and substract from the whole string.

Maybe there is more elegant way to solve this ? Btw, we do not have Regex available for us (FL 501).

Answer 1

/*
WITH MYTAB (C) AS 
(
SELECT
'
<doc>
<bunch e="of text with tags"/>
<AdrLine> Some address text </AdrLine> 
<AdrLine> More text with address </AdrLine> 
<AdrLine> postal codes etc </AdrLine>
<bunch e="of text with tags"/>
</doc>
'
FROM SYSIBM.SYSDUMMY1
)
*/
SELECT X.*
FROM 
  MYTAB T
, XMLTABLE
(
  '$D/doc/AdrLine' PASSING XMLPARSE (DOCUMENT T.C) AS "D" 
  COLUMNS 
    LINE VARCHAR (100) PATH '.'
) X;

LINE
Some address text
More text with address
postal codes etc

The example above is for a case, when you have a string (LOB or VARCHAR) representation of an xml document in your table.
You must use T.C instead of XMLPARSE (DOCUMENT T.C), if your table column C is of XML data type.