OraFAQ Forum: JDeveloper, Java & XML » Group Rows up in blocks

Home » Developer & Programmer » JDeveloper, Java & XML » Group Rows up in blocks

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Group Rows up in blocks [message #180155]

Fri, 30 June 2006 07:07

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Here's a little problem I'm stuck on.

I'm writting a package to send data in XML format to a remote site. Everything works fine, except for this one thing.

I store the data to be transmitted in a table called extract_data.
There is a view on this table, called extract_xml that converts this data into single pieces of XML.

I have a query that will concatenate the data from extract_xml into one single piece of XML, which I then transmit.

Sadly, the site I'm sending this to can only handle a limited number of rows at a time (100 in real life, as the XML chunks are quite large) and I'm sending a lot of data. For the sake of this example, we'll assume it can handle 4 rows at a time.

I need some way of modifying my query to batch the rows from extract_xml up into groups of 4 and then pass them into the XMLCONCAT function.

SQL> create table extract_data (col_1  varchar2(30), col_2  varchar2(30));

Table created.

SQL> 
SQL> insert into extract_data (select 'Line '||lvl, 'Data '||lvl from (select level lvl from dual connect by level < 11));

10 rows created.

SQL> 
SQL> create or replace view extract_xml as
  2   SELECT xmlelement("transmit_row",
  3                     xmlconcat(xmlelement("COL_1",     col_1),
  4                               xmlelement("COL_2",     col_2))) xml
  5   FROM   extract_Data;

View created.

SQL>                              
SQL>                  
SQL> select * from extract_xml;

XML
--------------------------------------------------------------------------------
<transmit_row>
  <COL_1>Line 1</COL_1>
  <COL_2>Data 1</COL_2>
</transmit_row>

<transmit_row>
  <COL_1>Line 2</COL_1>
  <COL_2>Data 2</COL_2>
</transmit_row>

<transmit_row>
  <COL_1>Line 3</COL_1>
  <COL_2>Data 3</COL_2>
</transmit_row>

<transmit_row>
  <COL_1>Line 4</COL_1>
  <COL_2>Data 4</COL_2>
</transmit_row>

<transmit_row>
  <COL_1>Line 5</COL_1>
  <COL_2>Data 5</COL_2>
</transmit_row>

<transmit_row>
  <COL_1>Line 6</COL_1>
  <COL_2>Data 6</COL_2>
</transmit_row>

<transmit_row>
  <COL_1>Line 7</COL_1>
  <COL_2>Data 7</COL_2>
</transmit_row>

<transmit_row>
  <COL_1>Line 8</COL_1>
  <COL_2>Data 8</COL_2>
</transmit_row>

<transmit_row>
  <COL_1>Line 9</COL_1>
  <COL_2>Data 9</COL_2>
</transmit_row>

<transmit_row>
  <COL_1>Line 10</COL_1>
  <COL_2>Data 10</COL_2>
</transmit_row>


10 rows selected.

Here is the query that concatentates all the XML chunks into one big piece of XML.

SQL> SELECT xmlelement("transmit_batch"
  2                    ,xmlconcat(CAST(MULTISET(SELECT xml 
  3                                             FROM   extract_xml) AS xmlsequencetype))) eqr_xml
  4  FROM   dual;

EQR_XML
--------------------------------------------------------------------------------
<transmit_batch>
  <transmit_row>
    <COL_1>Line 1</COL_1>
    <COL_2>Data 1</COL_2>
  </transmit_row>
  <transmit_row>
    <COL_1>Line 2</COL_1>
    <COL_2>Data 2</COL_2>
  </transmit_row>
  <transmit_row>
    <COL_1>Line 3</COL_1>
    <COL_2>Data 3</COL_2>
  </transmit_row>
  <transmit_row>
    <COL_1>Line 4</COL_1>
    <COL_2>Data 4</COL_2>
  </transmit_row>
  <transmit_row>
    <COL_1>Line 5</COL_1>
    <COL_2>Data 5</COL_2>
  </transmit_row>
  <transmit_row>
    <COL_1>Line 6</COL_1>
    <COL_2>Data 6</COL_2>
  </transmit_row>
  <transmit_row>
    <COL_1>Line 7</COL_1>
    <COL_2>Data 7</COL_2>
  </transmit_row>
  <transmit_row>
    <COL_1>Line 8</COL_1>
    <COL_2>Data 8</COL_2>
  </transmit_row>
  <transmit_row>
    <COL_1>Line 9</COL_1>
    <COL_2>Data 9</COL_2>
  </transmit_row>
  <transmit_row>
    <COL_1>Line 10</COL_1>
    <COL_2>Data 10</COL_2>
  </transmit_row>
</transmit_batch>

The output I need would be

EQR_XML
--------------------------------------------------------------------------------
<transmit_batch>
  <transmit_row>
    <COL_1>Line 1</COL_1>
    <COL_2>Data 1</COL_2>
  </transmit_row>
  <transmit_row>
    <COL_1>Line 2</COL_1>
    <COL_2>Data 2</COL_2>
  </transmit_row>
  <transmit_row>
    <COL_1>Line 3</COL_1>
    <COL_2>Data 3</COL_2>
  </transmit_row>
  <transmit_row>
    <COL_1>Line 4</COL_1>
    <COL_2>Data 4</COL_2>
  </transmit_row>
</transmit_batch>

<transmit_batch>
  <transmit_row>
    <COL_1>Line 5</COL_1>
    <COL_2>Data 5</COL_2>
  </transmit_row>
  <transmit_row>
    <COL_1>Line 6</COL_1>
    <COL_2>Data 6</COL_2>
  </transmit_row>
  <transmit_row>
    <COL_1>Line 7</COL_1>
    <COL_2>Data 7</COL_2>
  </transmit_row>
  <transmit_row>
    <COL_1>Line 8</COL_1>
    <COL_2>Data 8</COL_2>
  </transmit_row>
</transmit_batch>

<transmit_batch>
  <transmit_row>
    <COL_1>Line 9</COL_1>
    <COL_2>Data 9</COL_2>
  </transmit_row>
  <transmit_row>
    <COL_1>Line 10</COL_1>
    <COL_2>Data 10</COL_2>
  </transmit_row>
</transmit_batch>

This feels like it is either
a) Not possible
b) Relatively easy and I'm missing something embarrasingly trivial

I've got a workaround for this in place, that involves adding a batch_number column to the extract_data, pasing this up into the extract_xml view, and procedurally looping through the Concat query, selecting a different batch_number each time.

I'd rather do it in a single SQL though.

Over to you lot....

Report message to a moderator

Re: Group Rows up in blocks [message #180204 is a reply to message #180155]

Fri, 30 June 2006 13:06

andrew again
Messages: 2577
Registered: March 2000

Senior Member

you could always try something like decode(), mod(). I can't think of an easy way to determine if the last row has a closing tag already - or if you need to add one manually... I guess you could do a mod on sql%rowcount to figure it out...

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.6.0 - Production

SQL> set pagesize 56
SQL> select decode(mod(rownum-1, 5), 0, '<transmit_batch>'||ename,
  2                                  4, ename||'</transmit_batch>', ename) xml
  3   from emp;

XML
---------------------------
<transmit_batch>KING
BLAKE
CLARK
JONES
MARTIN</transmit_batch>
<transmit_batch>ALLEN
TURNER
JAMES
WARD
FORD</transmit_batch>
<transmit_batch>SMITH
SCOTT
ADAMS
MILLER

14 rows selected.

SQL> -- you can also chop the full resultset into roughly 10 buckets (similar to your procedural approach)
SQL> -- and just add an opening and closing tag with brute force
SQL> select xml from (
  2  select '<transmit_batch>' xml, 0 dummy_col from dual
  3  union all
  4  select ename, rownum from emp where substr(to_char(empno), -1) = '8'
  5  union all
  6  select '</transmit_batch>', 9999999999999999999999 dummy_col from dual
  7  order by 2);

XML
-----------------
<transmit_batch>
BLAKE
SCOTT
</transmit_batch>

SQL> select xml from (
  2  select '<transmit_batch>' xml, 0 dummy_col from dual
  3  union all
  4  select ename, rownum from emp where substr(to_char(empno), -1) = '9'
  5  union all
  6  select '</transmit_batch>', 9999999999999999999999 dummy_col from dual
  7  order by 2);

XML
-----------------
<transmit_batch>
KING
ALLEN
SMITH
</transmit_batch>

etc...

Report message to a moderator

Re: Group Rows up in blocks [message #180364 is a reply to message #180155]

Mon, 03 July 2006 04:57

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

I've got it.

select ceil(rownum/4) Batch, xmltype.getclobval(xmlelement("transmit_batch",xmlagg(xml))) XML
FROM   extract_xml
group by ceil(rownum/4);

<After some formatting>

BATCH XML
1 <transmit_batch>
    <transmit_row>
      <COL_1>Line 1</COL_1>
      <COL_2>Data 1</COL_2>
    </transmit_row>
    <transmit_row>
      <COL_1>Line 2</COL_1>
      <COL_2>Data 2</COL_2>
    </transmit_row>
    <transmit_row>
      <COL_1>Line 3</COL_1>
      <COL_2>Data 3</COL_2>
    </transmit_row>
    <transmit_row>
      <COL_1>Line 4</COL_1>
      <COL_2>Data 4</COL_2>
    </transmit_row>
  </transmit_batch>
2 <transmit_batch>
    <transmit_row>
      <COL_1>Line 5</COL_1>
      <COL_2>Data 5</COL_2>
    </transmit_row>
    <transmit_row>
      <COL_1>Line 6</COL_1>
      <COL_2>Data 6</COL_2>
    </transmit_row>
    <transmit_row>
      <COL_1>Line 7</COL_1>
      <COL_2>Data 7</COL_2>
    </transmit_row>
    <transmit_row>
      <COL_1>Line 8</COL_1>
      <COL_2>Data 8</COL_2>
    </transmit_row>
  </transmit_batch>
3 <transmit_batch>
    <transmit_row>
      <COL_1>Line 9</COL_1>
      <COL_2>Data 9</COL_2>
    </transmit_row>
    <transmit_row>
      <COL_1>Line 10</COL_1>
      <COL_2>Data 10</COL_2>
    </transmit_row>
  </transmit_batch>

Report message to a moderator

Re: Group Rows up in blocks [message #180371 is a reply to message #180364]

Mon, 03 July 2006 05:29

Maaher
Messages: 7065
Registered: December 2001

Senior Member

me too

The other topic is closed. One is enough. I've posted a link to here.

MHE

[Updated on: Mon, 03 July 2006 05:29]

Report message to a moderator

Previous Topic:	ORA-30951 Hell
Next Topic:	XML code to query databse

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 19:10:38 CDT 2024