从query_to_xml()输出中提取XML值

Question

我正在做从SQL Server到PostgreSQL的迁移，在转换查询时遇到困难

My SQL Server query:

SELECT * FROM dbo.Stl_Mtr_userdetails AS table1 JOIN( SELECT urmo.urm_userid, 
STUFF((SELECT ',' + urm.urm_role_name FROM dbo.STL_CS_Mtr_userrolemapping urm  
WHERE urm.urm_userid = 'test2' AND urm.urm_status = 'A' AND urm.urm_appid = 'BCA'   
FOR XML PATH('')),1,1,'') [user_roles],urmo.urm_appid FROM dbo.Stl_CS_Mtr_userrolemapping  
urmo WHERE urmo.urm_appid = 'BCA' AND urmo.urm_userid = 'test2'  
GROUP BY urmo.urm_userid,urmo.urm_appid) AS table2 ON  
table1.ud_userid = table2.urm_userid WHERE (table1.ud_userid = 'test2')

PostgreSQL查询

SELECT * FROM STL_Mtr_userdetails AS table1 JOIN( SELECT urmo.urm_userid,  
query_to_xml('SELECT '','' || urm.urm_role_name FROM STL_CS_Mtr_userrolemapping urm WHERE  
urm.urm_userid = ''test2'' AND urm.urm_status = ''A'' AND   
urm.urm_appid = ''BCA''', true, false, '')  
user_roles,urmo.urm_appid FROM STL_CS_Mtr_userrolemapping urmo   
WHERE urmo.urm_appid = 'BCA' AND urmo.urm_userid = 'test2'   
GROUP BY urmo.urm_userid,urmo.urm_appid) AS table2 ON   
table1.ud_userid = table2.urm_userid WHERE (table1.ud_userid = 'test2')

上述转换后的PostgreSQL查询在执行时工作良好，并为

产生以下结果user_roles场。

<table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<_x003F_column_x003F_>,role1</_x003F_column_x003F_>
</row>
<row>
<_x003F_column_x003F_>,role2</_x003F_column_x003F_>
</row>
</table>

我在从xml中提取值并获得user_roles的输出role1,role2时面临困难.

请帮我一下。

Answer 1

如果需要逗号分隔的角色列表，请使用string_agg()而不是query_to_xml()

SELECT * 
FROM STL_Mtr_userdetails AS table1 
JOIN( 
SELECT urmo.urm_userid,  
string_agg(urmo.urm_rol_name, ',') as user_roles,
urmo.urm_appid 
FROM STL_CS_Mtr_userrolemapping urmo   
WHERE urmo.urm_appid = 'BCA'
AND urm.urm_status = 'A' 
AND urmo.urm_userid = 'test2'   
GROUP BY urmo.urm_userid, urmo.urm_appid
) AS table2 ON table1.ud_userid = table2.urm_userid 
WHERE table1.ud_userid = 'test2'