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