Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE t (foo VARCHAR2(100), xmlcol XMLTYPE);
INSERT INTO t(foo, xmlcol) VALUES
('bar', '<?xml version="1.0"?>
<MessageData>
  <TextblockParameters>
    <TextblockParameter>
      <tagName>BUSINESSPROCESSID</tagName>
      <value>RETAIL</value>
    </TextblockParameter>
    <TextblockParameter>
      <tagName>SEGMENT</tagName>
      <value>RESIDENTIAL</value>
    </TextblockParameter>
    <TextblockParameter>
      <tagName>TRANSACTIONTYPE</tagName>
      <value>PURCHASE</value>
    </TextblockParameter>
    <TextblockParameter>
      <tagName>LANGUAGE</tagName>
      <value>EST</value>
    </TextblockParameter>
  </TextblockParameters>
</MessageData>');
1 rows affected

SELECT t.foo, xt.*
FROM t, XMLTABLE('//TextblockParameter'
    PASSING t.xmlcol
    COLUMNS
        tagName VARCHAR2(100) PATH './tagName',
        value VARCHAR2(100) PATH './value'
) xt;


FOO
TAGNAME
VALUE


bar
BUSINESSPROCESSID
RETAIL

bar
SEGMENT
RESIDENTIAL

bar
TRANSACTIONTYPE
PURCHASE

bar
LANGUAGE
EST

FOO	TAGNAME	VALUE
bar	BUSINESSPROCESSID	RETAIL
bar	SEGMENT	RESIDENTIAL
bar	TRANSACTIONTYPE	PURCHASE
bar	LANGUAGE	EST