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

select distinct() with XML query functions.

P: n/a
Hi all.
I'm really stuck with getting the right output from a XML query.

Given this structure:

create table GENCMP.SCRIPTS
(
SCRIPT_ID CHAR(10) not null,
PAGE_NO INTEGER not null
);

CREATE unique INDEX GENCMP.SCRIPTS_0 on GENCMP.SCRIPTS
(SCRIPT_ID, PAGE_NO) pctfree 2 allow reverse scans ;
insert into GENCMP.SCRIPTS (SCRIPT_ID, PAGE_NO) values ('CUSTCARE',
1);
insert into GENCMP.SCRIPTS (SCRIPT_ID, PAGE_NO) values ('CUSTCARE',
2);
insert into GENCMP.SCRIPTS (SCRIPT_ID, PAGE_NO) values ('LEAD', 1);
insert into GENCMP.SCRIPTS (SCRIPT_ID, PAGE_NO) values ('LEAD', 2);
What query do I use to get the following output:

<SCRIPTS>
<SCRIPT>CUSTCARE</SCRIPT>
<SCRIPT>LEAD</SCRIPT>
</SCRIPTS>
I just can not get it happening.

I would appreciate any support/help.

-Chris

Feb 15 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
ch****@warpspeed.com.au wrote:
Hi all.
I'm really stuck with getting the right output from a XML query.

Given this structure:

create table GENCMP.SCRIPTS
(
SCRIPT_ID CHAR(10) not null,
PAGE_NO INTEGER not null
);

CREATE unique INDEX GENCMP.SCRIPTS_0 on GENCMP.SCRIPTS
(SCRIPT_ID, PAGE_NO) pctfree 2 allow reverse scans ;
insert into GENCMP.SCRIPTS (SCRIPT_ID, PAGE_NO) values ('CUSTCARE',
1);
insert into GENCMP.SCRIPTS (SCRIPT_ID, PAGE_NO) values ('CUSTCARE',
2);
insert into GENCMP.SCRIPTS (SCRIPT_ID, PAGE_NO) values ('LEAD', 1);
insert into GENCMP.SCRIPTS (SCRIPT_ID, PAGE_NO) values ('LEAD', 2);
What query do I use to get the following output:

<SCRIPTS>
<SCRIPT>CUSTCARE</SCRIPT>
<SCRIPT>LEAD</SCRIPT>
</SCRIPTS>
How about this:

SELECT XMLSERIALIZE(XMLELEMENT(NAME "SCRIPTS",
XMLELEMENT(NAME "SCRIPT", script_id)) AS VARCHAR(2000))
FROM ( SELECT DISTINCT script_id
FROM scripts ) AS t
That's on DB2 V9.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Feb 15 '07 #2

P: n/a
On Feb 15, 5:44 pm, Knut Stolze <sto...@de.ibm.comwrote:
>
How about this:

SELECT XMLSERIALIZE(XMLELEMENT(NAME "SCRIPTS",
XMLELEMENT(NAME "SCRIPT", script_id)) AS VARCHAR(2000))
FROM ( SELECT DISTINCT script_id
FROM scripts ) AS t

That's on DB2 V9.
Thank you so much! I could not work out where the distinct would fit
in. Brain fade me.

This is what I got working under V8:

SELECT XMLSERIALIZE(CONTENT XMLELEMENT(NAME "SCRIPTS",
XMLAGG(
XMLELEMENT( NAME "SCRIPT", SCRIPT_ID ) ) ) AS
VARCHAR(80) ) AS RESULT
FROM ( select distinct script_id from GENCMP.SCRIPTS ) as T;

RESULT
--------------------------------------------------------------------------------
<SCRIPTS><SCRIPT>CUSTCARE </SCRIPT><SCRIPT>LEAD </SCRIPT></
SCRIPTS>

1 record(s) selected.

:-))))))))))))))))

Danke.

-Chris

Feb 15 '07 #3

P: n/a
ch****@warpspeed.com.au wrote:
On Feb 15, 5:44 pm, Knut Stolze <sto...@de.ibm.comwrote:
>>
How about this:

SELECT XMLSERIALIZE(XMLELEMENT(NAME "SCRIPTS",
XMLELEMENT(NAME "SCRIPT", script_id)) AS VARCHAR(2000))
FROM ( SELECT DISTINCT script_id
FROM scripts ) AS t

That's on DB2 V9.

Thank you so much! I could not work out where the distinct would fit
in. Brain fade me.

This is what I got working under V8:

SELECT XMLSERIALIZE(CONTENT XMLELEMENT(NAME "SCRIPTS",
XMLAGG(
XMLELEMENT( NAME "SCRIPT", SCRIPT_ID ) ) ) AS
VARCHAR(80) ) AS RESULT
FROM ( select distinct script_id from GENCMP.SCRIPTS ) as T;

RESULT
--------------------------------------------------------------------------------
<SCRIPTS><SCRIPT>CUSTCARE </SCRIPT><SCRIPT>LEAD </SCRIPT></
SCRIPTS>

1 record(s) selected.

:-))))))))))))))))
Right. I didn't try this myself, so I missed the syntax error (missing
CONTENT keyword) and the XMLAGG function. Thanks for correcting that.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Feb 16 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.