Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    60

    Cool Unanswered: Returning XML from Stored Procedure

    I am not very familiar with db2.

    I know in SQL server if I had 2 tables and wanted to return XML, I would use Tag & Parents followed by XML EXPLICT. How can I accomplish this in db2..

    Example

    Table1 (
    ID int,
    NAME varchar(100)
    )
    table2 (
    ID int,
    FIRST_NAME varchar(100)
    )

    query output...

    <root>
    <TABLE1 ID="1" NAME = "SCOTT GROUP">
    <TABLE2 ID ="1" FIRST_NAME = "SCOTT"/>
    </TABLE1>
    </root>

    How can I accomplish this?

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by scottb
    I am not very familiar with db2.

    I know in SQL server if I had 2 tables and wanted to return XML, I would use Tag & Parents followed by XML EXPLICT. How can I accomplish this in db2..

    Example

    Table1 (
    ID int,
    NAME varchar(100)
    )
    table2 (
    ID int,
    FIRST_NAME varchar(100)
    )

    query output...

    <root>
    <TABLE1 ID="1" NAME = "SCOTT GROUP">
    <TABLE2 ID ="1" FIRST_NAME = "SCOTT"/>
    </TABLE1>
    </root>

    How can I accomplish this?
    Look at REC2XML, I've not used it but I believe it's what you're looking for.
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    Oct 2003
    Posts
    60

    sp

    How Can I change this into a stored procedure?


    select XML2CLOB(
    XMLELEMENT(NAME "CLIENT",
    XMLATTRIBUTES(A.ID as "CLIENT_ID", A.NAME as "CLIENT_NAME"),
    XMLAGG(
    XMLELEMENT( NAME "EMAIL",
    XMLATTRIBUTES( B.ADD as "EMAIL_ADDRESS")
    )
    )
    ))
    FROM CLIENT A, EMAIL B
    where A.ID = B.ID
    group by A.ID, A.NAME

  4. #4
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by scottb
    How Can I change this into a stored procedure?


    select XML2CLOB(
    XMLELEMENT(NAME "CLIENT",
    XMLATTRIBUTES(A.ID as "CLIENT_ID", A.NAME as "CLIENT_NAME"),
    XMLAGG(
    XMLELEMENT( NAME "EMAIL",
    XMLATTRIBUTES( B.ADD as "EMAIL_ADDRESS")
    )
    )
    ))
    FROM CLIENT A, EMAIL B
    where A.ID = B.ID
    group by A.ID, A.NAME
    Same as usual, this returns it as a result set:

    CREATE PROCEDURE GET_CLIENTINFO ()
    RESULT SETS 1
    LANGUAGE SQL
    BEGIN

    DECLARE C2 CURSOR WITH RETURN FOR
    select XML2CLOB(
    XMLELEMENT(NAME "CLIENT",
    XMLATTRIBUTES(A.ID as "CLIENT_ID", A.NAME as "CLIENT_NAME"),
    XMLAGG(
    XMLELEMENT( NAME "EMAIL",
    XMLATTRIBUTES( B.ADD as "EMAIL_ADDRESS")
    )
    )
    ))
    FROM CLIENT A, EMAIL B
    where A.ID = B.ID
    group by A.ID, A.NAME
    ;

    OPEN C2;
    end
    --
    Jonathan Petruk
    DB2 Database Consultant

  5. #5
    Join Date
    Oct 2003
    Posts
    60
    Thanks...

    How can you sending in parameters in XML format?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •