Home » SQL & PL/SQL » SQL & PL/SQL » Grouping Data up in batches
| Grouping Data up in batches [message #180355] |
Mon, 03 July 2006 04:20  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Hi, I posted this question on Friday, and it got shunted off to the desolate wasteland that is the JDeveloper, Java and XML forum.
These is a fair bit of XML in this problem, but the heart of it is an SQL problem - How can I make my query return 3 rows of small data sets rather than one big row.
<Mod: If you realy don't think this belongs here, just delete it - the other query is still languishing in the outer darkness >
I have a problem with a package I've written. It's purpose is to send data in an XML format to a remote site. The XML generation and the comms side of things are all working fine, but there is a problem.
The data set I am transmitting contains about 50,000 rows of data. I have created a view of this data set to return each row as an XML chunk, and I have a query that will join these individual XML chunks together into a single piece of XML, which I can then transmit.
Unfortunately, the remote site can only handle about 100 rows at a time (each row produces over 2k of xml, so it's not that bad). So I'm looking for some way in SQL to batch up this data into small sets (we'll say 4 rows for this example), and then produce the XML for that.
Here's some SQL
This is the table the data lives in, and the view on that table that creates the XML
QL> create table extract_data (col_1 varchar2(30), col_2 varchar2(30));
Table created.
SQL> insert into extract_data (select 'Line '||lvl, 'Data '||lvl from (select level lvl from dual connect by level < 11));
10 rows created.
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> 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>
This is the query that I've got to combine all the individual XML rows into one single row:
SQL> SELECT xmlelement("transmit_batch"
2 ,xmlconcat(CAST(MULTISET(SELECT xml
3 FROM extract_xml) AS xmlsequencetype))) xml
4 FROM dual;
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>
What I need is a query that will return 3 rows, each of which contains a batched up XML for a subset of that data.
Something like this:
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>
I've got a procedural solution to this, where I load the data from the XML view into a staging table and assign a batch number to each group of rows, and then run an extract and transmit for each batch number, but I would much prefer to simply step through the resultset of a single query and do one transmit call for each row.
|
|
|
|
| Re: Grouping Data up in batches [message #180367 is a reply to message #180355] |
Mon, 03 July 2006 05:08  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I found a solution.
Sadly, it's a solution involving the builting XML functions, rather than a nifty SQL one, but it works, and that's all that matters.
The function I needed was XMLAGG - it seems to do everything that the rather clunky cast(multiset()) operation did, and can be grouped too.
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>
|
|
|
|
| Re: Grouping Data up in batches [message #180370 is a reply to message #180367] |
Mon, 03 July 2006 05:23 |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
I found another, using just SQL:
The script
set pages 1200
set long 5000
create table extract_data (col_1 varchar2(7), col_2 varchar2(7))
/
insert into extract_data (select 'Line '||lvl, 'Data '||lvl from (select level lvl from dual connect by level < 11))
/
SELECT DECODE(rn, 1, col_1, NULL) col_1a
, DECODE(rn, 2, col_1, NULL) col_1b
, DECODE(rn, 0, col_1, NULL) col_1c
, DECODE(rn, 1, col_2, NULL) col_2a
, DECODE(rn, 2, col_2, NULL) col_2b
, DECODE(rn, 0, col_2, NULL) col_2c
, rec
, rn
FROM ( SELECT col_1
, col_2
, mod(rownum+2,3) rn
, trunc(decode(rownum,1,1,rownum-1)/3) rec
FROM extract_data
)
/
CREATE VIEW mhe_foo
AS
SELECT MAX(DECODE(rn, 1, col_1, NULL)) col_1a
, MAX(DECODE(rn, 2, col_1, NULL)) col_1b
, MAX(DECODE(rn, 0, col_1, NULL)) col_1c
, MAX(DECODE(rn, 1, col_2, NULL)) col_2a
, MAX(DECODE(rn, 2, col_2, NULL)) col_2b
, MAX(DECODE(rn, 0, col_2, NULL)) col_2c
FROM ( SELECT col_1
, col_2
, mod(rownum,3) rn
, trunc(decode(rownum,1,1,rownum-1)/3) rec
FROM extract_data
)
GROUP BY rec
/
select xmlelement( "transmit_batch",
xmlconcat( xmlelement("transmit_row",
xmlconcat(xmlelement("COL_1", col_1a),
xmlelement("COL_2", col_2a)
)
),
xmlelement("transmit_row",
xmlconcat(xmlelement("COL_1", col_1b),
xmlelement("COL_2", col_2b)
)
),
xmlelement("transmit_row",
xmlconcat(xmlelement("COL_1", col_1c),
xmlelement("COL_2", col_2c)
)
)
)
) xml
from mhe_foo
/
DROP VIEW mhe_foo
/
drop table extract_data
/
The test run:
SQL> @C:\useful\orafaq
Table created.
10 rows created.
COL_1A COL_1B COL_1C COL_2A COL_2B COL_2C REC RN
------- ------- ------- ------- ------- ------- ---------- ----------
Line 1 Data 1 0 0
Line 2 Data 2 0 1
Line 3 Data 3 0 2
Line 4 Data 4 1 0
Line 5 Data 5 1 1
Line 6 Data 6 1 2
Line 7 Data 7 2 0
Line 8 Data 8 2 1
Line 9 Data 9 2 2
Line 10 Data 10 3 0
10 rows selected.
View created.
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_batch>
<transmit_batch>
<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_batch>
<transmit_batch>
<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_batch>
<transmit_batch>
<transmit_row>
<COL_1>Line 10</COL_1>
<COL_2>Data 10</COL_2>
</transmit_row>
<transmit_row>
<COL_1/>
<COL_2/>
</transmit_row>
<transmit_row>
<COL_1/>
<COL_2/>
</transmit_row>
</transmit_batch>
View dropped.
Table dropped.
SQL>
Is this what you were looking for?
MHE
|
|
|
|
|
|
|
|
| |
  |
Goto Forum:
Current Time: Fri May 03 08:57:59 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
