[DB-SIG] Question on odbc with cross apply and for xml...

[M.-A. Lemburg]

Christopher Eckman wrote:
> Hi all,
> 
> 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
>      from
>        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)
>      go  
> 
> 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 
> 
> cursor.execute(sample_query)
> for row in cursor.fetchall()...
> 
> Any help would be appreciated.

You could try this with mxODBC to see whether it's a problem related to
the ODBC driver or not.

Note that string processing such as what you are applying to the
"operators" is much better done in Python than at the SQL level.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Nov 27 2006)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::