>>>Hi,
>>>
>>>I need a way to send some of the data from a VFP table(s) to a customer for importing into their SQL Server. I am thinking of outputting data from the table(s) into XML file. Is CURSORTOXML() sufficient to create such an XML? Or XML Adapter class is preferred?
>>>
>>>TIA.
>>
>>CursorToXML is sufficient. I recently implemented an SP using XML as a source. I can post it if you want.
>>Although if you want them to use SSIS to import the data, may be Excel or CSV will be better.
>
>I would like to see how that works, because in some situations I can imagine that will be much faster to process than separate SQL passthrough statements.

Here is the SP:

CREATE PROCEDURE siriussp_ImportCreditNumbers ( 
                @vcStr VARCHAR(MAX)) 
--============================================== 
AS 
  BEGIN 
    SET NOCOUNT  ON; 
     
    DECLARE  @x XML 
     
    SET @x = CONVERT(XML,@vcStr) 
     
    IF OBJECT_ID('TempDB..#csrCardNos','U') IS NOT NULL 
      DROP TABLE #csrCardNos 
     
    CREATE TABLE #csrCardNos ( 
      num_assign CHAR(17), 
      num_source INT) 
     
    INSERT INTO #csrCardNos 
               (num_assign, 
                num_source) 
    SELECT T.n.value('(num_assign/text())[1]','char(17)') AS [num_Assign], 
           T.n.value('(num_source/text())[1]','int')      AS [num_Source] 
    FROM   @x.nodes('/VFPData/cardnos') AS T(n); 
     
    --select * from #csrCardNos 
    IF EXISTS (SELECT 1 
               FROM   #csrCardNos CN 
                      INNER JOIN gst_pass GP 
                        ON CN.num_assign = GP.swipe_no) 
      SELECT 'The credit numbers in the file have been already assigned!' AS ErrorMsg 
    ELSE 
      BEGIN 
        BEGIN TRY 
          INSERT INTO assignno 
                     (ckd_out_to, 
                      num_assign, 
                      num_source) 
          SELECT -1, 
                 num_assign, 
                 num_source 
          FROM   #csrCardNos 
           
          SELECT '' AS ErrorMsg 
        END TRY 
         
        BEGIN CATCH 
          DECLARE  @ErrorSeverity INT, 
                   @ErrorNumber   INT, 
                   @ErrorMessage  VARCHAR(4000), 
                   @ErrorState    INT, 
                   @ErrorLine     INT, 
                   @ErrorProc     VARCHAR(200) 
           
          -- Grab error information from SQL functions 
          SET @ErrorSeverity = ERROR_SEVERITY() 
           
          SET @ErrorNumber = ERROR_NUMBER() 
           
          SET @ErrorMessage = ERROR_MESSAGE() 
           
          SET @ErrorState = ERROR_STATE() 
           
          SET @ErrorLine = ERROR_LINE() 
           
          SET @ErrorProc = ERROR_PROCEDURE() 
           
          SET @ErrorMessage = 'Problem inserting Card Numbers.' + CHAR(13) + 
          'SQL Server Error Message is: ' + CAST(@ErrorNumber AS VARCHAR(10)) + 
          ' in procedure: ' + @ErrorProc + ' Line: ' + CAST(@ErrorLine AS VARCHAR(10)) + 
          ' Error text: ' + @ErrorMessage
           
          -- Not all errors generate an error state, to set to 1 if it's zero 
          IF @ErrorState = 0 
            SET @ErrorState = 1 
           
          -- If the error renders the transaction as uncommittable or we have open transactions, we may want to rollback
          IF @@TRANCOUNT > 0 
            BEGIN 
              --print 'Rollback transaction' 
              ROLLBACK TRANSACTION 
            END 
           
          SELECT @ErrorMessage AS ErrorMsg 
        END CATCH 
      END 
  END 

GO
/* Test Cases
PRINT 'Import Credit Numbers:'
declare @Time datetime2(7) = SYSDATETIME(), @Elapsed int
 EXECUTE dbo.siriussp_ImportCreditNumbers  0x;
  
set @Elapsed = DATEDIFF(microsecond,@time, getdate())
print 'Elapsed: ' + convert(varchar(10),@Elapsed) + ' microseconds'

*/
/* Test Cases
PRINT 'Import Credit Numbers:'
declare @Time datetime2(7) = SYSDATETIME(), @Elapsed int
 EXECUTE dbo.siriussp_ImportCreditNumbers  0x;
  
set @Elapsed = DATEDIFF(microsecond,@time, getdate())
print 'Elapsed: ' + convert(varchar(10),@Elapsed) + ' microseconds'

*/

And it will be a bit hard to dig searching for the actual call from VFP, but I believe I create XML using cursortoxml and then use strconv to pass the info.