Xrhrft

i am able to create the xml node in db2 but all the node creation i need under root node.

below code i have written as:

CREATE FUNCTION abc.sample(v_pf_wkstn_oid_sh smallint, 

                v_pf_wkstn_oid_lng integer,

                v_llr_oid_sh smallint,

                v_llr_oid_lng integer )



RETURNS xml



LANGUAGE SQL



BEGIN ATOMIC



DECLARE v_RtKeys xml;



SET v_RtKeys=(SELECT XMLAGG(XMLELEMENT(

              NAME "TableKeys",

              XMLELEMENT(

              NAME "rt", XMLAttributes( rt.rbase_field_name AS "rbase_field_name",

                                                   rt.wkstn_oid_sh AS "wkstn_oid_sh", 

                           rt.wkstn_oid_lng AS "wkstn_oid_lng",

                           rt_key_sqnc_num AS "rt_key_sqnc_num", 

                                   rt.rt_key_rtrvl_cd AS "rt_key_rtrvl_cd",

                           pf_wkstn_oid_sh AS "pf_wkstn_oid_sh", 

                           pf_wkstn_oid_lng AS "pf_wkstn_oid_lng", 

                           'N' as status_indc, 

                           rt.field_data_type AS "field_data_type", 

                           rt.field_size AS "field_size",

                           'Rate Key            ' as "DisplayType",

                           '' as "Author",

                           0 as "DateCreated"),

                XMLELEMENT(

                NAME "rb", XMLAttributes( rb.field_level_indc AS "field_level_indc",

                           rb.field_scope_indc AS "field_scope_indc")))) order by rt_key_sqnc_num asc ) 

                           FROM RATABASE.rt_tmplt_key rt inner join RATABASE.rbase_field_dict rb  

                                        on rb.rbase_field_name=rt.rbase_field_name 

                                        where pf_wkstn_oid_sh = v_pf_wkstn_oid_sh

                                                                                AND pf_wkstn_oid_lng = v_pf_wkstn_oid_lng 

                                                                                AND rb.llr_oid_lng = v_llr_oid_lng

                                                                                AND rb.llr_oid_sh = v_llr_oid_sh);



RETURN v_RtKeys;



END

once i have executed the above sample for each time i am getting for all rt node as below means multiple TableKeys

<TableKeys><rt rbase_field_name="ITEM NUM 4A                                       " wkstn_oid_sh="100" wkstn_oid_lng="2051" rt_key_sqnc_num="1" rt_key_rtrvl_cd="E" pf_wkstn_oid_sh="100" pf_wkstn_oid_lng="2050" STATUS_INDC="N" field_data_type="N" field_size="4" DisplayType="Rate Key            " Author="" DateCreated="0"><rb field_level_indc="I" field_scope_indc="D"/></rt></TableKeys><TableKeys><rt rbase_field_name="ITEM NUM 1A                                       " wkstn_oid_sh="100" wkstn_oid_lng="2052" rt_key_sqnc_num="2" rt_key_rtrvl_cd="R" pf_wkstn_oid_sh="100" pf_wkstn_oid_lng="2050" STATUS_INDC="N" field_data_type="N" field_size="1" DisplayType="Rate Key            " Author="" DateCreated="0"><rb field_level_indc="I" field_scope_indc="D"/></rt></TableKeys><TableKeys><rt rbase_field_name="ITEM NUM 1B                                       " wkstn_oid_sh="100" wkstn_oid_lng="2053" rt_key_sqnc_num="3" rt_key_rtrvl_cd="R" pf_wkstn_oid_sh="100" pf_wkstn_oid_lng="2050" STATUS_INDC="N" field_data_type="N" field_size="1" DisplayType="Rate Key            " Author="" DateCreated="0"><rb field_level_indc="I" field_scope_indc="D"/></rt></TableKeys><TableKeys><rt rbase_field_name="ITEM NUM 1C                                       " wkstn_oid_sh="100" wkstn_oid_lng="2054" rt_key_sqnc_num="4" rt_key_rtrvl_cd="R" pf_wkstn_oid_sh="100" pf_wkstn_oid_lng="2050" STATUS_INDC="N" field_data_type="N" field_size="1" DisplayType="Rate Key            " Author="" DateCreated="0"><rb field_level_indc="I" field_scope_indc="D"/></rt></TableKeys><TableKeys><rt rbase_field_name="ITEM NUM 1D                                       " wkstn_oid_sh="100" wkstn_oid_lng="2055" rt_key_sqnc_num="5" rt_key_rtrvl_cd="R" pf_wkstn_oid_sh="100" pf_wkstn_oid_lng="2050" STATUS_INDC="N" field_data_type="N" field_size="1" DisplayType="Rate Key            " Author="" DateCreated="0"><rb field_level_indc="I" field_scope_indc="D"/></rt></TableKeys><TableKeys><rt rbase_field_name="ITEM NUM 1E                                       " wkstn_oid_sh="100" wkstn_oid_lng="2056" rt_key_sqnc_num="6" rt_key_rtrvl_cd="R" pf_wkstn_oid_sh="100" pf_wkstn_oid_lng="2050" STATUS_INDC="N" field_data_type="N" field_size="1" DisplayType="Rate Key            " Author="" DateCreated="0"><rb field_level_indc="I" field_scope_indc="D"/></rt></TableKeys>

i need only one inside that all rt and rb node in db2 sample.

please suggest have a look this code provide your input.