[DB-SIG] Question on odbc with cross apply and for xml...
royhobbsx42 at yahoo.com
Mon Nov 27 22:23:17 CET 2006
I am doing a select to concatenate a number of entries into a field like this under 'operators' (sample header is the first line):
name company uis_access_control uis_tp_ticketpassing operators
UNINA FOO unrestricted No uni-catherine_srvage,uni-robert_woyzik,uni-susan_fooman
using the SQL Server functionality cross apply and for xml. Sample select is below:
select support_group_name "name", sg.Company "company", sg.f5 "uis_access_control", sg.f6 "uis_tp_ticketpassing", sg.REZ_Manager "manager",
substring(memberList, 1, datalength(memberList)/2 - 1) "operators"
-- strip the last ',' from the list
ctm_support_group sg cross apply
(select convert(nvarchar(60), sgm.support_group_member_name) + ',' as [text()]
from tsmi_support_group_members sgm
where sg.Support_Group_ID = sgm.Support_Group_ID and sg.Company = 'UNINA' and sg.support_group_name like 'UNI-NA%'
order by support_group_name
for xml path('')) as Dummy(memberList)
The problem is when I call this via dbi and odbc it will always put 'None' for operators even though if I do this in TOAD or MS Query it will pull the correct values? I tried to get around this by making this a stored procedure but the behavior is the same. Is there something I am missing? I am calling this with the typical
for row in cursor.fetchall()...
Any help would be appreciated.
More information about the DB-SIG