XML : How to write a FULL XML record to column based on row count

I have a stored procedure that I am using to write custom XML based on a vendors requirements for integrating two systems. I would like to write each record to a column to bypass the char limitation in a sql column. I am including a very simple version of my SP. I have 600 fields in the true SP. I have 4700 records in the table and my XML is getting cut off after 200 rows process. Is there a way to return everything between the Command action="Upsert" invalidLookupBehavior="Skip" and the "/Command" in their own columns? I'm stumped. I apologize for the duplicate post.. TAB

  USE [DEV]  GO  /****** Object:  StoredProcedure [dbo].[MASTER_TABLE_XML_PHASE_I]   ******/  SET ANSI_NULLS ON  GO  SET QUOTED_IDENTIFIER ON  GO    ALTER PROCEDURE [dbo].[MASTER_TABLE_XML_PHASE_I_SIMPLE]  AS  declare  @xml nvarchar(max),  @metaEMPLOYEE nvarchar(max)    CREATE TABLE #MASTER_TABLE_IMPORT  (  [EMP_COMPANY_ID] [int] NOT NULL,  [EMP_LAST_NAME] [nvarchar](50) NULL,  [EMP_MIDDLE_NAME] [nvarchar](50) NULL,  [EMP_FIRST_NAME] [nvarchar](50) NULL,  [EMP_PREFIX] [nchar](6) NULL,  [EMP_PREFERRED_NAME] [nvarchar](50) NULL,  [EMP_FORMER_NAME] [nvarchar](50) NULL,  [EMP_SYSTEM_NUMBER] [nvarchar](100) NOT NULL,  [IMP_CREATE_DATE] [datetime] NULL,  [IMP_LAST_UPDATE_DATE] [datetime] NULL,  )    INSERT INTO #MASTER_TABLE_IMPORT  (  [EMP_COMPANY_ID] ,  [EMP_LAST_NAME] ,  [EMP_MIDDLE_NAME] ,  [EMP_FIRST_NAME],  [EMP_PREFIX] ,  [EMP_PREFERRED_NAME],  [EMP_FORMER_NAME] ,  [EMP_SYSTEM_NUMBER] ,  [IMP_CREATE_DATE],  [IMP_LAST_UPDATE_DATE]  )    SELECT  EMP_COMPANY_ID ,  EMP_LAST_NAME,  EMP_MIDDLE_NAME ,  EMP_FIRST_NAME,  EMP_PREFIX ,  EMP_PREFERRED_NAME,  EMP_FORMER_NAME ,  T1.EMP_SYSTEM_NUMBER,  IMP_CREATE_DATE,  IMP_LAST_UPDATE_DATE    FROM MASTER_TABLE_PHASE_I AS T1  INNER JOIN (SELECT EMP_SYSTEM_NUMBER ,MAX(IMP_CREATE_DATE) AS MaxDate  FROM MASTER_TABLE_PHASE_I  GROUP BY EMP_SYSTEM_NUMBER) AS T2  ON (T1.EMP_SYSTEM_NUMBER = T2.EMP_SYSTEM_NUMBER AND T1.IMP_CREATE_DATE =     T2.MaxDate)  /*OPEN XML FULL FILE TAGS*/  set @xml =  N'<DataChange><Commands>'  + N'' + CHAR(10);    /*OPEN EMPLOYEE TABLE*/  /*OPEN EMPLOYEE FIELDS*/    select @metaEMPLOYEE =  CONVERT(nvarchar(max),  (  (select  /*OPEN XML UNIQUE RECORD TAGS*/  '<Command action="Upsert" invalidLookupBehavior="Skip"><Tables><Table     name="EMPLOYEE"><Fields>'+  '<Field name="COMPANY_ID" lookupValue="False">84</Field>',  '<Field name="LAST_NAME">' + EMP_LAST_NAME + '</Field>',  '<Field name="MIDDLE_NAME">' + EMP_MIDDLE_NAME + '</Field>',  '<Field name="FIRST_NAME">' + EMP_FIRST_NAME + '</Field>',  '<Field name="PREFIX" lookupValue="True">' + EMP_PREFIX + '</Field>',  '<Field name="PREFERRED_NAME">' + EMP_PREFERRED_NAME + '</Field>',  '<Field name="FORMER_NAME">' + EMP_FORMER_NAME + '</Field>',  '<Field name="SYSTEM_NUMBER" recordIdentifier="True">' + EMP_SYSTEM_NUMBER +         '</Field>',  /*CLOSE EMPLOYEE FIELDS*/  '</Fields>',  /*CLOSE EMPLOYEE TABLE*/  '</Table>',  /*CLOSE EMPLOYEE RECORD ALL TABLES*/  '</Tables>',  /*CLOSE XML COMMAND*/  /*CLOSE XML UNIQUE RECORD TAGS*/  '</Command>'  FROM #MASTER_TABLE_IMPORT  WHERE 1=1    FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)')))  /*BUILD XML*/  /*CLOSING MASTER COMMAND*/  /*CLOSING MASTER DATA CHANGE*/    SET @xml = @xml + @metaEMPLOYEE +'</Commands></DataChange>'  SELECT @xml;    CREATE TABLE XMLDATA  (  xCol XML  ) ;    INSERT INTO XMLDATA ( xCol )  SELECT @xml      DECLARE @Command VARCHAR(255)  DECLARE @Filename VARCHAR(100)    SELECT @Filename = 'C:\Client_XML\Data.dat'    SELECT @Command = 'bcp "select xCol from ' + DB_NAME()  + '..XMLDATA" queryout '  + @Filename + ' -w -T -S' + @@servername  EXECUTE master..xp_cmdshell @command  --WRITE THE XML TO A FILE    SELECT CONVERT(nVARCHAR(max),BulkColumn)  FROM OPENROWSET(BULK 'C:\Client_XML\Data.dat', SINGLE_BLOB) AS x    DROP TABLE XMLDATA    

No comments:

Post a Comment