select distinct() with XML query functions.

[c***@warpspeed.com.au]

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

[Knut Stolze]

Post by c***@warpspeed.com.au
Hi all.
I'm really stuck with getting the right output from a XML query.
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);
<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.

[c***@warpspeed.com.au]

Post by Knut Stolze
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

[Knut Stolze]

Post by c***@warpspeed.com.au

Post by Knut Stolze
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.
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.