DB2 XML FUNCTIONS

             db2数据库，t0表的一条记录关联t1表的多条记录，需求是要查询出 [t0.col1],[t1.col2,t1.col2,t1.col2...]这种格式的数据。

             用db2的xml函数可以满足，SQL:

with

v0 as (selectt0.task_id,t0.user_id,t0.group_id,t0.activity_name,t0.subject from task t0where t0.status='32' and t0.task_id in ('166807','166819') ),

v1 as (select REPLACE(REPLACE(XMLSERIALIZE(CONTENTxmlagg(xmlelement(NAME a, t1.USER_ID||',')) AS VARCHAR(100)),'<A>',''),'</A>',' ') as users,t1.group_idfrom  usr_grp_mem_xref t1

 joinv0 on t1.group_id=v0.group_id group by t1.group_id)

select v0.task_id,v0.activity_name,v0.subject,v1.usersfrom v0,v1 where v0.group_id = v1.group_id with ur;

Db2 Xml Functions:

XMLELEMENT

REC2XML

XML2CLOB

XMLSERIALIZE

XMLAGG

XMLATTRIBUTES

XMLNAMESPACES

XMLFOREST

XMLCONCAT

详细内容查看链接：

http://www.toadworld.com/platforms/ibmdb2/w/wiki/7712.xml-publishing-functions