I have a select statement FOR XML EXPLICIT which extracts information from a SQL2005 database table. The statement looks like this:
SELECT
       1 AS TAG
      ,NULL AS PARENT
      ,'http://www.xxx.com/ASR/CR/PHONE' [CRP:PHONE_STATISTICS_TABLE!1!xmlns:CRP]
      ,'http://www.w3.org/2001/XMLSchema-instance' [CRP:PHONE_STATISTICS_TABLE!1!xmlns:xsi]
      ,'http://www.xxx.com/ASR/CR/PHONE PHONE_STATISTICS.xsd' [CRP:PHONE_STATISTICS_TABLE!1!xsi:schemaLocation]
      ,VendorID + '_PHONE_' + substring ((convert(varchar(100),activity_date, 121)), 1, 10) AS [CRP:PHONE_STATISTICS_TABLE!1!FILE_NAME!element]
      ,COUNT(*) AS [CRP:PHONE_STATISTICS_TABLE!1!RECORD_COUNT!element]
      ,VendorID AS [CRP:PHONE_STATISTICS_TABLE!1!VENDOR_CODE!element]
      ,REPLACE(cast(Activity_Date as varchar(100)),'T00:00:00','T12:00:00') AS [CRP:PHONE_STATISTICS_TABLE!1!ACTIVITY_DATE!element]

,NULL AS [CRP:PHONE_STATISTICS!2!RECORD_NUMBER!element]
FROM tbl_xml_report

WHERE activity_date= (select CAST(FLOOR(@mydate) AS datetime))
GROUP BY [VendorID],Activity_Date

UNION ALL

SELECT
      2 AS TAG
      ,1 AS PARENT
      ,NULL --CRP:PHONE_STATISTICS_TABLE attribute xmlns:CRP
      ,NULL --CRP:PHONE_STATISTICS_TABLE attribute xmlns:xsi
      ,NULL --CRP:PHONE_STATISTICS_TABLE attribute xsi:SchemaLocation
      ,NULL --CRP:PHONE_STATISTICS_TABLE element file_name
      ,NULL --CRP:PHONE_STATISTICS_TABLE element record_count
,VendorID
,Activity_Date
,RECORD_NUMBER
FROM tbl_xml_REPORT
WHERE activity_date= (select CAST(FLOOR(@mydate) AS datetime))
ORDER BY [CRP:PHONE_STATISTICS_TABLE!1!VENDOR_CODE!element],[CRP:PHONE_STATISTICS_TABLE!1!ACTIVITY_DATE!element]
FOR XML EXPLICIT

If I run this statement in the query window the return set is an XML file exactly as i need it.
I am trying to automate daily generation of this XML file, but have not found a way to get the same output.
does anyone have an idea how to solve this ?

Have you tried using SSIS?

Adam Gossage
Lake Wylie, SC, USA

I actually managed using BCP.EXE.