select distinct() with XML query functions.

[chrisg]

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]

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

[chrisg]

On Feb 15, 5:44 pm, Knut Stolze <[e-mail]:

>
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

[Knut Stolze]

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

On Feb 15, 5:44 pm, Knut Stolze <[e-mail]:

>>
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