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.

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

Thanks...

How can you sending in parameters in XML format?