By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,213 Members | 2,108 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,213 IT Pros & Developers. It's quick & easy.

Need help with an XML querry

P: n/a
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

Nov 22 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
Nov 22 '06 #2

P: n/a
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

Nov 22 '06 #3

P: n/a
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
Nov 23 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.