The below .xml processes a .txt file into a sql table.
Now this .txt file has changed where as 1-1 Contains part 1 of Repair Code and 22-23 contains part 2 of Repair code.
So, the Repair code is now 3 positions long instead of 2. The Repair Code Name is same length as before in positions 24-58.
How would I change below .xml to process this new 3 position Repair Code.
I mean the new 3 position Repair Code would have to be brought together so I can put it in a 3 Char sql field and the Repair Code Name would have to be put in the current 35 varchar field.
The old Repair Code was in positions 1-2 (see .xml below). Why they did not put the new Repair Code into positions 1-3 is a mystery to me.
Here is Stored Procedure code that processes this Repair Code table:
/****** Object: StoredProcedure [dbo].[LoadRepairCode] Script Date: 09/11/2014 22:05:23 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo].[LoadRepairCode] AS
TRUNCATE TABLE dbo.RepairCode
EXECUTE sp_tableoption RepairCode, 'table lock on bulk load', 1
-- Perform the bulk insert of the flat-file received from the mainframe.
-- Note that the records are not all fixed-length.
INSERT INTO dbo.RepairCode
FROM OPENROWSET(BULK '\\fmrprdfiles\sqlprdfiles
) AS Temp;
Here is the current RepairCode.xml'
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="2" COLLATION="SQL_Latin1_Gene
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="35" COLLATION="SQL_Latin1_Gene
<COLUMN SOURCE="1" NAME="RepCD" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="2" NAME="RepCd-Name" xsi:type="SQLVARYCHAR"/>