Обратное преобразование спецсимволов для oracle xml

Необходимо в запросе выполнить конкатенацию строк в группировке. Так как для Oracle 10g стандартной функции нет, реализовал через XML:
select id, cast(sys_xmlagg(xmlelement(col, '<' || name || '>')).extract('/ROWSET/COL/text()') as varchar2(4000)) from table group by id;
Проблема в том что на выходе получаю:
1 <name1> 2 <name2> 3 <name1><name2> 4 <name1><name2><name3>
А нужно:
1 2 3 4
Возможно есть стандартная функция обратного преобразования спецсимволов по лучше чем replace?

Ответ

По моему мнению, для приведённого примера, replace или regexp_replace это лучший выбор.
Другая альтернатива, использовать другие средства, также доступные в 10g, не "злоупотребляя" XmlType. Например, использовать аналитические функции:
with data as ( select 1 id, 'name1' name from dual union all select 2 id, 'name1' name from dual union all select 2 id, 'name2' name from dual union all select 3 id, 'name1' name from dual union all select 3 id, 'name2' name from dual union all select 3 id, 'name3' name from dual) select id, max(sys_connect_by_path(name||'>', '<')) keep (dense_rank last order by this) names from ( select id, name, row_number() over (partition by id order by name) this, row_number() over (partition by id order by name)-1 prev from data ) group by id connect by prior this = prev and prior id = id start with this = 1 ;
1 2 3
Если всё таки предпочтительна аггрегатная функция, то в 10g есть не документированная функция wmsys.WM_CONCAT. Так, как она неофициальная и не поддерживается, то не желательно её использование в продуктивной среде. Можно написать свою функцию по её подобию. Пример кода, помимо прочего, есть на спроси Тома. Такое решение будет возможно даже более изящным, чем с sys_connect_by_path
create or replace type myAggType as object ( values_ varchar2(32767), static function ODCIAggregateInitialize(ctx in out myAggType) return number, member function ODCIAggregateIterate(self in out myAggType, value in varchar2) return number, member function ODCIAggregateTerminate(self myAggType, returnValue out varchar2, flags number) return number, member function ODCIAggregateMerge(self in out myAggType, ctx2 in myAggType) return number ); /
create or replace type body myAggType IS static function ODCIAggregateInitialize(ctx in out myAggType) return number is begin ctx := myAggType (null); return ODCIConst.Success; end;
member function ODCIAggregateIterate(self in out myAggType, value varchar2) return number is begin self.values_ := self.values_||'<'||value||'>'; return ODCIConst.Success; end;
member function ODCIAggregateTerminate(self myAggType, returnValue out varchar2, flags number) return number is begin returnValue := self.values_; return ODCIConst.Success; end;
member function ODCIAggregateMerge(self in out myAggType, ctx2 myAggType) return number is begin self.values_ := self.values_||ctx2.values_; return ODCIConst.Success; end; end; /
create or replace function myAgg (input varchar2) return varchar2 parallel_enable aggregate using myAggType; /
with data as (<см. выше>) select id, myAgg(name) names from data group by id ;
ID NAMES ---- ---------------------------------------- 1 2 3
А также, если данных не много, можно создать специфическую функцию, которая получит запрос как sys_refcursor или данные с collect и выдаст как возврат конкантинацию данных в виде, удовлетворяющем конкретным техническим требованиям.
Предыдуший ответ (не подходит, т.к работает только при возврате одного узла)
В вашем примере extract возвращает XMLType. Поэтому cast() конвертирует clob, получаемый из XMLType, в varchar2, но он никакого представления не имеет о его XML содержимом, т.е shit-in/shit-out.
Попрубуйте с помощью extractValue() вернуть скалярные значения узлов. Его тип возврата по умолчанию - varchar2(4000). Ну где то так:
select extractValue(xml, '/col[1]/text()') val1, extractValue(xml, '/col[2]/text()') val2 from ( select xmlagg(xmlel) xml from ( select xmlelement("col", '<' || 'name1' || '>') xmlel from dual union all select xmlelement("col", '<' || 'name2' || '>') xmlel from dual ));
Вывод: