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 Go to next message
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 #650548 is a reply to message #650538] Wed, 27 April 2016 08:04 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
OK, so it looks like wrapping the listagg up in a to_clob will do the trick. If any xml experts know of a better way, I'm all ears.
Re: Stitching strings into xml [message #650549 is a reply to message #650538] Wed, 27 April 2016 08:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
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 #650551 is a reply to message #650548] Wed, 27 April 2016 08:35 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
You 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.


[Updated on: Wed, 27 April 2016 08:38]

Report message to a moderator

Re: Stitching strings into xml [message #650560 is a reply to message #650549] Wed, 27 April 2016 10:23 Go to previous messageGo to next message
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 Smile

[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 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Solomon Yakobson wrote on Wed, 27 April 2016 14:35
You 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 #650562 is a reply to message #650561] Wed, 27 April 2016 10:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I posted a CLOB version of T. Kyte's STRAGG there.

It is not directly usable in your case as the equivalent of
select listagg(xml, '') within group (order by subid) xml from myData group by id;

would be
select id, stragg4(xml) from mydata group by id;

or
select stragg4(xml) over (partition by id order by subid) xml from mydata;

but the first one does not guarantee the order in each group and the second one returns duplicates and DISTINCT (or GROUP BY) can't be used as the returned value is a CLOB.

So there is some work to do on it. If you are interested, tell me.

Re: Stitching strings into xml [message #650564 is a reply to message #650562] Wed, 27 April 2016 11:39 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Nice one Michel. Even if I can't apply it in this situation, I'll definitely take a look. Thanks Smile
Re: Stitching strings into xml [message #650569 is a reply to message #650564] Wed, 27 April 2016 13:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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  		 '&lt;', '<'), '&gt;', '>'), '&quot;', '"')) 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 #650598 is a reply to message #650575] Thu, 28 April 2016 04:08 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
To all, many thanks, this is awesome. I'm pulled between a few different projects at the moment, so when I get the time I'll confirm. Again, apologies for the transcription errors.

Cheers
Re: Stitching strings into xml (merged) [message #650675 is a reply to message #650598] Fri, 29 April 2016 04:49 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
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

Previous Topic: Extract XML value with no local name
Next Topic: Using XML to insert into a table with self-reference
Goto Forum:
  


Current Time: Thu Mar 28 08:57:16 CDT 2024