select distinct() with XML query functions.

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • chrisg@warpspeed.com.au
    #1

    select distinct() with XML query functions.

    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>CUSTCAR E</SCRIPT>
    <SCRIPT>LEAD</SCRIPT>
    </SCRIPTS>


    I just can not get it happening.

    I would appreciate any support/help.

    -Chris

    Tags: xml distinct
    • Knut Stolze
      #2
      Re: select distinct() with XML query functions.

      chrisg@warpspee d.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>CUSTCAR E</SCRIPT>
      <SCRIPT>LEAD</SCRIPT>
      </SCRIPTS>
      How about this:

      SELECT XMLSERIALIZE(XM LELEMENT(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

        Comment

        • chrisg@warpspeed.com.au
          #3
          Re: select distinct() with XML query functions.

          On Feb 15, 5:44 pm, Knut Stolze <sto...@de.ibm. comwrote:
          >
          How about this:
          >
          SELECT XMLSERIALIZE(XM LELEMENT(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(CO NTENT 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><SCRIP T>CUSTCARE </SCRIPT><SCRIPT> LEAD </SCRIPT></
          SCRIPTS>

          1 record(s) selected.

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

          Danke.

          -Chris

            Comment

            • Knut Stolze
              #4
              Re: select distinct() with XML query functions.

              chrisg@warpspee d.com.au wrote:
              On Feb 15, 5:44 pm, Knut Stolze <sto...@de.ibm. comwrote:
              >>
              >How about this:
              >>
              >SELECT XMLSERIALIZE(XM LELEMENT(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(CO NTENT 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><SCRIP T>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

                Comment

                Previous template Next
                Working...