Home » Developer & Programmer » JDeveloper, Java & XML » Stitching strings into xml (merged) (Oracle 11.2.0.3.0)
| Stitching strings into xml (merged) [message #650538] |
Wed, 27 April 2016 05:58  |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
Setup
I have a table, it can be reduced to 3 columns:
create table myData as (id number, subid number xml varchar2(4000));
insert into myData values(10, 10, '<?xml version "1.0" encoding="UTF-8" standalone="no"?> <Body> <People> <person> <RefId>123</RefId> <Id>xyz<Id/> </person> </Pe');
insert into myData values(10, 10, 'ople> </Body>');
Once xml goes beyond 2500 characters it is split into a second (and if necessary subsequent) row(s) that are grouped by id and ordered by subid (this cannot be changed, external source to the data)
I need to stitch that string back together and be able to work with it.
Now, I've tried it using listagg:
with t as (select listagg(xml, '') within group (order by subid) xml
from myData
group by id)
, t1 as (select xmltype(xml)
from t)
select distinct x.column_value.getrootelement()
from t1
, xmltable('Body/*'
passing t1.xml) x
Unfortunately, this returns zero rows.
If I take the result of the listagg and paste it into the query as a string at t1: ( select xmltype('PasteTheListAgg result here') xml ...) It seems to work perfectly well.
If I create a table out of the ListAgg result, we're good to go, but I don't want to do that if I can avoid it.
Looking for the gotcha that I'm not spotting, any help appreciated.
I've obviously cut the example waaaay back, so if I've missed anything pertinent, let me know.
|
|
|
|
|
|
| Re: Stitching strings into xml [message #650549 is a reply to message #650538] |
Wed, 27 April 2016 08:23  |
 |
Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Not knowing what is from original data and what is from typo specific of the test case, I post everything:
First, I got the same thing in 11.2.0.4 with the original query:
SQL> with t as (select listagg(xml, '') within group (order by subid) xml
2 from myData
3 group by id)
4 , t1 as (select xmltype(xml) x
5 from t)
6 select distinct x.column_value.getrootelement()
7 from t1
8 , xmltable('Body/*'
9 passing t1.x) x
10 /
no rows selected
Removing the intermediate query I get:
SQL> with t as (select listagg(xml, '') within group (order by subid) xml
2 from myData
3 group by id)
4 select distinct x.column_value.getrootelement()
5 from t
6 , xmltable('Body/*'
7 passing xmltype(t.xml)) x
8 /
passing xmltype(t.xml)) x
*
ERROR at line 7:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00210: expected '=' instead of '"'
Error at line 1
ORA-06512: at "SYS.XMLTYPE", line 310
ORA-06512: at line 1
After analyze the error comes from a missing = after "version":
SQL> with t as (select listagg(xml, '') within group (order by subid) xml
2 from myData
3 group by id)
4 select distinct x.column_value.getrootelement()
5 from t
6 , xmltable('Body/*'
7 passing xmltype(replace(t.xml,'version ','version='))) x
8 /
passing xmltype(replace(t.xml,'version ','version='))) x
*
ERROR at line 7:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00225: end-element tag "person" does not match start-element tag "Id"
Error at line 1
ORA-06512: at "SYS.XMLTYPE", line 310
ORA-06512: at line 1
There "<Id/>" should "</Id>":
SQL> with t as (select listagg(xml, '') within group (order by subid) xml
2 from myData
3 group by id)
4 select distinct x.column_value.getrootelement()
5 from t
6 , xmltable('Body/*'
7 passing xmltype(replace(replace(t.xml,'version ','version='),'Id/>','/Id>'))) x
8 /
X.COLUMN_VALUE.GETROOTELEMENT()
---------------------------------------------------------------------------------------------------
People
1 row selected.
Remind: I used 11.2.0.4.
|
|
|
|
|
|
| Re: Stitching strings into xml [message #650560 is a reply to message #650549] |
Wed, 27 April 2016 10:23 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
Yeah, apologies for the numerous typos (C&P not possible so had to type the string, no excuses, shoulda been more careful). Definitely could be a version diff as when I fixed the typo issues in my env I still got the issue.
No matter, based on SY's response I'm up the creek with this approach anyway 
[Updated on: Wed, 27 April 2016 10:24] Report message to a moderator |
|
|
|
| Re: Stitching strings into xml [message #650561 is a reply to message #650551] |
Wed, 27 April 2016 10:30 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
Solomon Yakobson wrote on Wed, 27 April 2016 14:35You can wrap LISTAGG into TO_CLOB all day long - LISTAGG will fail as soon as length exceeds 4000 bytes. Storing XML as VARCHAR2 is root of your issues. Change it to XMLTYPE or CLOB and get rid of subid.
SY.
Thanks for the response, hadn't realised that limitation in ListAgg. Unfortunately this is the structure demanded by the consumer of the data, cr4p but it is what it is.
I'll figure something out, cheers.
|
|
|
|
|
|
|
|
| Re: Stitching strings into xml [message #650569 is a reply to message #650564] |
Wed, 27 April 2016 13:49 |
|
Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Here's a new stragg function which returns a CLOB, takes an order id (second parameter) and a delimiter (third parameter). So in your case, use as:
select stragg5(stragg5_elem(xml, subid, '')) from mydata group by id
create or replace type stragg5_elem as object (
value varchar2(4000),
order_id integer,
delim varchar2(10)
)
/
create or replace type stragg5_array as table of stragg5_elem
/
create or replace type stragg5_type as object
(
data stragg5_array,
result clob,
static function ODCIAggregateInitialize (sctx IN OUT stragg5_type)
return number,
member function ODCIAggregateIterate (self IN OUT stragg5_type,
value IN stragg5_elem)
return number,
member function ODCIAggregateTerminate (self IN stragg5_type,
returnValue OUT clob,
flags IN number)
return number,
member function ODCIAggregateMerge (self IN OUT stragg5_type,
ctx2 IN stragg5_type)
return number
);
/
create or replace type body stragg5_type
is
static function ODCIAggregateInitialize (sctx IN OUT stragg5_type)
return number
is
begin
sctx := stragg5_type (stragg5_array(), null);
dbms_lob.createtemporary (lob_loc => sctx.result,
cache => TRUE,
dur => dbms_lob.call);
return ODCIConst.Success;
end;
member function ODCIAggregateIterate (self IN OUT stragg5_type,
value IN stragg5_elem)
return number
is
begin
data.extend;
data(data.count) := value;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate (self in stragg5_type,
returnValue out clob,
flags in number)
return number
is
b_first boolean := true;
begin
returnValue := result;
for x in (
select value, order_id, delim from table(data) order by order_id
) loop
if b_first then b_first := false;
else returnvalue := returnValue || x.delim;
end if;
returnValue := returnValue || x.value;
end loop;
return ODCIConst.Success;
end;
member function ODCIAggregateMerge (self IN OUT stragg5_type,
ctx2 IN stragg5_type)
return number
is
begin -- not really tested ;)
for i in 1..ctx2.data.count loop
data.extend;
data(data.count) := ctx2.data(i);
end loop;
return ODCIConst.Success;
end;
end;
/
sho err
create or replace function stragg5 (input stragg5_elem)
return clob
parallel_enable aggregate using stragg5_type;
/
I didn't verify very deeply the ODCIAggregateMerge part which is executed when you run the query in parallel mode but I think it will work.
[Updated on: Fri, 29 April 2016 09:46] Report message to a moderator |
|
|
|
| Re: Stitching strings into xml (merged) [message #650575 is a reply to message #650538] |
Wed, 27 April 2016 19:04 |
 |
Barbara Boehmer
Messages: 9103
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following uses corrections to your xml data to make it valid, as previously mentioned by others. I also changed the subid's to different numbers, instead of the same number, so that they can be used for ordering as you stated. The xmltype in the query is unnecessary and was just used to demonstrate that it does return valid xml and format it automatically. I have not tested this on larger strings, but theoretically it should work. Please test it on your data and let me know. You may need to nest additional replace statements or use some other method to convert all of the various &..; back to <, >, ", and so forth.
SCOTT@orcl> column xml format a65
SCOTT@orcl> select * from mydata order by id, subid
2 /
ID SUBID XML
---------- ---------- -----------------------------------------------------------------
10 1 <?xml version="1.0" encoding="UTF-8" standalone="no"?> <Body> <Pe
ople> <person> <RefId>123</RefId> <Id>xyz</Id> </person> </Pe
10 2 ople> </Body>
2 rows selected.
SCOTT@orcl> set define off
SCOTT@orcl> select id,
2 xmltype
3 (replace (replace (replace
4 (xmlagg(xmlelement(e,xml,'').extract('//text()') order by subid).GetClobVal(),
5 '<', '<'), '>', '>'), '"', '"')) xml
6 from mydata
7 group by id
8 order by id
9 /
ID XML
---------- -----------------------------------------------------------------
10 <?xml version="1.0" encoding="WINDOWS-1252" standalone='no'?>
<Body>
<People>
<person>
<RefId>123</RefId>
<Id>xyz</Id>
</person>
</People>
</Body>
1 row selected.
|
|
|
|
|
|
| Re: Stitching strings into xml (merged) [message #650675 is a reply to message #650598] |
Fri, 29 April 2016 04:49 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
|
OK, update for those who are interested. Barbara's solution fits perfectly to what I was trying to do, so that's what I'm going with. Michel, I'm going to spend some time playing with your solution, the environment I'm in makes your solution slightly less optimal (in that it involves the creation of multiple objects as opposed to only using that which is already available) but many thanks for your time, I'm sure I'll learn from what you've posted.
|
|
|
|
| Re: Stitching strings into xml (merged) [message #650689 is a reply to message #650675] |
Fri, 29 April 2016 09:45 |
|
Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
There was a bug in ODCIAggregateTerminate function when delimiter is not a single character.
It should be modified as (I also modified in the original post):
member function ODCIAggregateTerminate (self in stragg5_type,
returnValue out clob,
flags in number)
return number
is
b_first boolean := true;
begin
returnValue := result;
for x in (
select value, order_id, delim from table(data) order by order_id
) loop
if b_first then b_first := false;
else returnvalue := returnValue || x.delim;
end if;
returnValue := returnValue || x.value;
end loop;
return ODCIConst.Success;
end;
The delimiter can be of any length, including 0; in addition, it can be different for each value; for instance if I want the delimiter to be the department number:
SQL> select deptno, stragg5(stragg5_elem(empno||' '||ename, empno, ' /'||deptno||'/ ')) names
2 from emp group by deptno;
DEPTNO NAMES
---------- -----------------------------------------------------------------------------------------------
10 7782 CLARK /10/ 7839 KING /10/ 7934 MILLER
20 7369 SMITH /20/ 7566 JONES /20/ 7788 SCOTT /20/ 7876 ADAMS /20/ 7902 FORD
30 7499 ALLEN /30/ 7521 WARD /30/ 7654 MARTIN /30/ 7698 BLAKE /30/ 7844 TURNER /30/ 7900 JAMES
[Updated on: Fri, 29 April 2016 09:51] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Sun Jan 26 01:12:47 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
