Need help with an XML querry

[chrisg]

Hi All,
I've been struggeling with this for a while now, and I was
wondering if anyone could help me.

given:
drop table GENCMP.SCRIPTS;

--================================================== ============
-- Table: SCRIPTS
--================================================== ============
create table GENCMP.SCRIPTS
(
SCRIPT_ID CHAR(10) not null,
PAGE_NO INTEGER not null,
EFFECTIVE_FROM DATE not null,
EFFECTIVE_TO DATE not null,
USERID CHAR(8) not null,
PUBLISHED CHAR(1) not null,
SCRIPT_TEXT CLOB(32K) LOGGED NOT COMPACT NOT
NULL
);

CREATE unique INDEX GENCMP.SCRIPTS_0 on GENCMP.SCRIPTS
(SCRIPT_ID, PAGE_NO, EFFECTIVE_TO) pctfree 2 allow
reverse scans ;

insert into GENCMP.SCRIPTS (SCRIPT_ID, PAGE_NO, EFFECTIVE_FROM,
EFFECTIVE_TO, USERID, PUBLISHED, SCRIPT_TEXT) values ('CUSTCARE', 1,
'2006-01-01', '9999-12-31', 'APCPG', 'N', 'asd1');
insert into GENCMP.SCRIPTS (SCRIPT_ID, PAGE_NO, EFFECTIVE_FROM,
EFFECTIVE_TO, USERID, PUBLISHED, SCRIPT_TEXT) values ('CUSTCARE', 2,
'2006-01-01', '9999-12-31', 'APCPG', 'N', 'asd2');
insert into GENCMP.SCRIPTS (SCRIPT_ID, PAGE_NO, EFFECTIVE_FROM,
EFFECTIVE_TO, USERID, PUBLISHED, SCRIPT_TEXT) values ('CUSTCARE', 3,
'2006-01-01', '9999-12-31', 'APCPG', 'N', 'asd3');
insert into GENCMP.SCRIPTS (SCRIPT_ID, PAGE_NO, EFFECTIVE_FROM,
EFFECTIVE_TO, USERID, PUBLISHED, SCRIPT_TEXT) values ('CUSTCARE', 4,
'2006-01-01', '9999-12-31', 'APCPG', 'N', 'asd4');

select SCRIPT_ID, PAGE_NO, EFFECTIVE_FROM, EFFECTIVE_TO, USERID,
PUBLISHED from GENCMP.SCRIPTS;

You should see:

SCRIPT_ID PAGE_NO EFFECTIVE_FROM EFFECTIVE_TO USERID PUBLISHED
---------- ----------- -------------- ------------ -------- ---------
CUSTCARE 1 01-01-2006 31-12-9999 APCPG N
CUSTCARE 2 01-01-2006 31-12-9999 APCPG N
CUSTCARE 3 01-01-2006 31-12-9999 APCPG N
CUSTCARE 4 01-01-2006 31-12-9999 APCPG N
The query that I have is this:

select XMLSERIALIZE(CONTENT XMLELEMENT(NAME "SCRIPTS",
XMLAGG(

XMLELEMENT(NAME "SCRIPT",

XMLATTRIBUTES(SCRIPT_ID),

XMLELEMENT(NAME "script_text",

XMLATTRIBUTES(PAGE_NO),

SCRIPT_TEXT)

)
)
) AS CLOB
) from gencmp.scripts;
and it generates the following XML:

<SCRIPTS>
<SCRIPT SCRIPT_ID="CUSTCARE ">
<script_text PAGE_NO="1">asd1</script_text>
</SCRIPT>
<SCRIPT SCRIPT_ID="CUSTCARE ">
<script_text PAGE_NO="2">asd2</script_text>
</SCRIPT>
<SCRIPT SCRIPT_ID="CUSTCARE ">
<script_text PAGE_NO="3">asd3</script_text>
</SCRIPT>
<SCRIPT SCRIPT_ID="CUSTCARE ">
<script_text PAGE_NO="4">asd4</script_text>
</SCRIPT>
</SCRIPTS>
However, what I need is:

<SCRIPTS>
<SCRIPT SCRIPT_ID="CUSTCARE ">
<script_text PAGE_NO="1">asd1</script_text>
<script_text PAGE_NO="2">asd2</script_text>
<script_text PAGE_NO="3">asd3</script_text>
<script_text PAGE_NO="4">asd4</script_text>
</SCRIPT>
</SCRIPTS>
Can anyone help me massage the query into giving me what I need?

TIA,

-Chris

[Knut Stolze]

ch****@warpspeed.com.au wrote:

SCRIPT_ID PAGE_NO EFFECTIVE_FROM EFFECTIVE_TO USERID PUBLISHED
---------- ----------- -------------- ------------ -------- ---------
CUSTCARE 1 01-01-2006 31-12-9999 APCPG N
CUSTCARE 2 01-01-2006 31-12-9999 APCPG N
CUSTCARE 3 01-01-2006 31-12-9999 APCPG N
CUSTCARE 4 01-01-2006 31-12-9999 APCPG N
The query that I have is this:

select XMLSERIALIZE(CONTENT XMLELEMENT(NAME "SCRIPTS",
XMLAGG(

XMLELEMENT(NAME "SCRIPT",

XMLATTRIBUTES(SCRIPT_ID),

XMLELEMENT(NAME "script_text",

XMLATTRIBUTES(PAGE_NO),

SCRIPT_TEXT)

)
)
) AS CLOB
) from gencmp.scripts;
and it generates the following XML:

<SCRIPTS>
<SCRIPT SCRIPT_ID="CUSTCARE ">
<script_text PAGE_NO="1">asd1</script_text>
</SCRIPT>
<SCRIPT SCRIPT_ID="CUSTCARE ">
<script_text PAGE_NO="2">asd2</script_text>
</SCRIPT>
<SCRIPT SCRIPT_ID="CUSTCARE ">
<script_text PAGE_NO="3">asd3</script_text>
</SCRIPT>
<SCRIPT SCRIPT_ID="CUSTCARE ">
<script_text PAGE_NO="4">asd4</script_text>
</SCRIPT>
</SCRIPTS>
However, what I need is:

<SCRIPTS>
<SCRIPT SCRIPT_ID="CUSTCARE ">
<script_text PAGE_NO="1">asd1</script_text>
<script_text PAGE_NO="2">asd2</script_text>
<script_text PAGE_NO="3">asd3</script_text>
<script_text PAGE_NO="4">asd4</script_text>
</SCRIPT>
</SCRIPTS>

If I got this right, then you don't want to have the nested "<SCRIPT>" tags,
right? Therefore, you will have to put the XMLAGG in the right place like
this:

SELECT XMLSERIALIZE(
CONTENT XMLELEMENT(NAME "SCRIPTS",
XMLELEMENT(NAME "SCRIPT",
XMLATTRIBUTES(script_id),
XMLAGG(
XMLELEMENT(NAME "script_text",
XMLATTRIBUTES(page_no),
script_text))
ORDER BY page_no))
FROM ...
GROUP BY script_id

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[chrisg]

Knut Stolze wrote:

If I got this right, then you don't want to have the nested "<SCRIPT>" tags,
right? Therefore, you will have to put the XMLAGG in the right place like
this:

SELECT XMLSERIALIZE(
CONTENT XMLELEMENT(NAME "SCRIPTS",
XMLELEMENT(NAME "SCRIPT",
XMLATTRIBUTES(script_id),
XMLAGG(
XMLELEMENT(NAME "script_text",
XMLATTRIBUTES(page_no),
script_text))
ORDER BY page_no))
FROM ...
GROUP BY script_id

That is basically right. Given that you can not have multiple nested
XMLAGG()'s (is this correct?), I was wondering if I had it in the right
place.

This is the finished, working query:v

SELECT XMLSERIALIZE(CONTENT
XMLELEMENT(NAME "SCRIPTS",
XMLELEMENT( NAME "SCRIPT",
XMLATTRIBUTES(script_id),
XMLAGG(
XMLELEMENT(NAME "script_text",
XMLATTRIBUTES(page_no), script_text)
ORDER BY page_no
) ) ) AS CLOB
)
FROM GENCMP.SCRIPTS
GROUP BY script_id;

Thank you very much for pointing me in the right direction! :-)

It generates this:

<SCRIPTS>
<SCRIPT SCRIPT_ID="CUSTCARE ">
<script_text PAGE_NO="1">asd1</script_text>
<script_text PAGE_NO="2">asd2</script_text>
<script_text PAGE_NO="3">asd3</script_text>
<script_text PAGE_NO="4">asd4</script_text>
</SCRIPT>
</SCRIPTS>

Which is precisely what I wanted.

Thanks again.

-Chris

[Knut Stolze]

ch****@warpspeed.com.au wrote:

Given that you can not have multiple nested
XMLAGG()'s (is this correct?),

No, that is not correct (unless there are some constraints I don't know
about). Of course, a single subselect can only contain one level of
aggregation. Nested aggregations would immediately raise the question how
to group things on all levels. However, you can use nested subselects and
each subselect can do its own aggregation. That will give you the ability
to aggregate more:

SELECT XMLAGGR(xml_fragment), ...
FROM ( SELECT XMLAGGR(xml_column), ...
FROM ...
GROUP BY ... ) AS t(xml_fragment, ...)
GROUP BY ...

--
Knut Stolze
DB2 Information Integration Development
IBM Germany