Hi all, I'm having a problem returning XML from a stored proc I've created. The query SELECTs from 5 tables with a combo of INNER and LEFT JOINs. But the problem is that SQL returns the XML in a structure that doesn't make sense - some tables are output as child nodes of unrelated tables. Here's the query:SELECT T.task_id, S.sched_id, I.intf_code, Im.ftp_name, Ex.ftp_nameFROM dbo.Task T INNER JOIN dbo.sched S ON T.task_id = S.sched_task_id INNER JOIN dbo.intf I ON T.task_intf_code = I.intf_code LEFT JOIN dbo.ftp Im ON T.task_import_ftp_id = Im.ftp_id LEFT JOIN dbo.ftp Ex ON T.task_export_ftp_id = Ex.ftp_idFOR XML AUTO
which returns:<T task_id="1"> <S sched_id="1"> <I intf_code="Test"> <Im ftp_name="ImxFTP"> <Ex ftp_name="ImxFTP"/> </Im> </I> </S></T>
So it's assuming that each table is a child of the previous table referenced in the query. How can I tell the #&(*&(* thing how to structure it? I've tried using FOR XML EXPLICT but it doesn't seem to output any of the child records..The output of this stored proc will be used to populate some .NET classes via serialization. I don't want to change the structure of my objects because SQL is getting confused.Can anyone shine some light on what I'm doing wrong??Cheers,Timbtw - Using SQL 2000 with SQLXML 3
FOR XML AUTO problem
Posted - 2005-12-19 : 19:29:38
