The table has a CLOB column in which the XML text is stored:

& lt; address & gt;
 & lt; element type = "district" & gt; Brest & LT; / Element & gt;
 & lt; element type = "region" & gt; & lt; / element & gt;
 & lt; element type = "ctype" key = "1" & gt; city & lt; / element & gt;
 & lt; element type = "City" & gt; cobrin & lt; / element & gt;
 & lt; element type = "stype" key = "1" & gt; Street & lt; / element & gt;
 & lt; element type = "Street" & gt; Suvorov & lt; / element & gt;
 & lt; element type = "house" & gt; 33 & lt; / element & gt;
 & lt; element type = "Building" & gt; & lt; / element & gt;
 & lt; element Type = "Flat" & gt; 6 & lt; / element & gt;
 & lt; element type = "postcode" & gt; 225306 & lt; / element & gt;
& lt; / address & gt;

I want to disassemble it by SQL by obtaining a gluing of values.
In this case, I want to get at the exit

Brest Kobrin Ulitasvorova 33 6

Tell me how to achieve such a result?

Answer 1, Authority 100%

with t as
(Select.
'& lt; address & gt;
 & lt; element type = "district" & gt; Brest & LT; / Element & gt;
 & lt; element type = "region" & gt; & lt; / element & gt;
 & lt; element type = "ctype" key = "1" & gt; city & lt; / element & gt;
 & lt; element type = "City" & gt; cobrin & lt; / element & gt;
 & lt; element type = "stype" key = "1" & gt; Street & lt; / element & gt;
 & lt; element type = "Street" & gt; Suvorov & lt; / element & gt;
 & lt; element type = "house" & gt; 33 & lt; / element & gt;
 & lt; element type = "Building" & gt; & lt; / element & gt;
 & lt; element Type = "Flat" & gt; 6 & lt; / element & gt;
 & lt; element type = "postcode" & gt; 225306 & lt; / element & gt;
& lt; / address & gt; ' AS XML_
from dual)
SELECT EXTRACTVALUE (S.COLUMN_VALUE, '/ * / ELEMENT [@Type =' 'District' ']') || ' '||
    ExtractValue (S.Column_Value, '/ * / Element [@Type =' 'city' ']') || ' '||
    'Street' || ExtractValue (S.Column_Value, '/ * / Element [@Type =' 'Street' ']') || ' '||
    ExtractValue (S.Column_Value, '/ * / Element [@Type =' 'House' ']') || ' '||
    ExtractValue (S.COLUMN_VALUE, '/ * / ELEMENT [@Type =' 'Flat' ']') AS VAL
From T, Table (XMLSequence (XMLType (T.XML_), '/ Address'))) s

Answer 2, Authority 75%

with x as (
 SELECT XMLTYPE ('
& lt; address & gt;
 & lt; element type = "district" & gt; Brest & LT; / Element & gt;
 & lt; element type = "region" & gt; & lt; / element & gt;
 & lt; element type = "ctype" key = "1" & gt; city & lt; / element & gt;
 & lt; element type = "City" & gt; cobrin & lt; / element & gt;
 & lt; element type = "stype" key = "1" & gt; Street & lt; / element & gt;
 & lt; element type = "Street" & gt; Suvorov & lt; / element & gt;
 & lt; element type = "house" & gt; 33 & lt; / element & gt;
 & lt; element type = "Building" & gt; & lt; / element & gt;
 & lt; element Type = "Flat" & gt; 6 & lt; / element & gt;
 & lt; element type = "postcode" & gt; 225306 & lt; / element & gt;
& lt; / address & gt; ') xfield
 from dual.
)
Select Address
from xmltable ('
 Let $ R: =
 & lt; address & gt;
  {$ Doc / Address / Element [@ Type = "District"]}, {$ doc / address / element [@ Type = "City"]}, {$ Doc / Address / Element [@ Type = "Stype"]} {$ Doc / Address / Element [@ Type = "Street"]}, {$ doc / address / element [@ type = "house"]}, {$ Doc / Address / Element [@ Type = "Flat"] }
 & lt; / address & gt;
 Return $ R '
 Passing (SELECT XFIELD from X) AS "DOC"
 Columns Address Varchar2 (1000) Path 'Address'
);

Brest, Kobrin, Street, Suvorova, 33, 6

Answer 3, AUTHORITY 75%

function xmlsequence declared obsolete. Instead, it is recommended to use xmltable In the new code:

with data as (
  SELECT XMLTYPE ('
  & lt; address & gt;
    & lt; element type = "district" & gt; Brest & LT; / Element & gt;
    & lt; element type = "region" & gt; & lt; / element & gt;
    & lt; element type = "ctype" key = "1" & gt; city & lt; / element & gt;
    & lt; element type = "City" & gt; cobrin & lt; / element & gt; 
& lt; element type = "stype" key = "1" & gt; Street & lt; / element & gt;
    & lt; element type = "Street" & gt; Suvorov & lt; / element & gt;
    & lt; element type = "house" & gt; 33 & lt; / element & gt;
    & lt; element type = "Building" & gt; & lt; / element & gt;
    & lt; element Type = "Flat" & gt; 6 & lt; / element & gt;
    & lt; element type = "postcode" & gt; 225306 & lt; / element & gt;
  & lt; / address & gt;
  ') AS XML from Dual
)
SELECT PCODE || ' '|| District ||' '|| CTYPE ||' '|| City ||' '|| stype ||' '|| Street ||' '|| house ||' / '|| Flat
FROM DATA D.
Cross Join Xmltable ('Address' Passing D.Xml Columns
PCode Varchar2 (8) Path '/ Address / Element [@ Type = "Postcode"]',
District Varchar2 (64) Path 'Address / Element [@ Type = "District"]',
CTYPE VARCHAR2 (32) Path '/ Address / Element [@ Type = "CType"] [@ Key = "1"]',
City Varchar2 (64) Path 'Address / Element [@ Type = "City"]',
STYPE VARCHAR2 (16) PATH '/ ADDRESS / ELEMENT [@ Type = "stype"] [@ Key = "1"]',
Street Varchar2 (64) Path 'Address / Element [@ Type = "Street"]',
House Number Path 'Address / Element [@ Type = "House"]',
Flat Number Path 'Address / Element [@ Type = "Flat"]'
);

Conclusion:

225306 Brest city Kobrin Suvorov Street 33/6

Answer 4

Based on https://stackoverflow.com/a/4715997/1646082

Column You must have a XMLType type (i.e. you need to make conversion) and then you can perform SQL type:

select
 ExtractValue (XMLCOL, '/ * / ELEMENT') Txtaddress
From youurtable