Link to home
Start Free TrialLog in
Avatar of thayduck
thayduckFlag for United States of America

asked on

Process .txt file with a .xml format

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:

USE [FMR]
GO
/****** Object:  StoredProcedure [dbo].[LoadRepairCode]    Script Date: 09/11/2014 22:05:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


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
    SELECT *
     FROM OPENROWSET(BULK '\\fmrprdfiles\sqlprdfiles\FMR\data\RepairCode.txt',
     FORMATFILE='\\fmrprdfiles\sqlprdfiles\FMR\Formats\RepairCode.xml'
       ) AS Temp;


Here is the current RepairCode.xml'

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="2" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="35" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="RepCD" xsi:type="SQLCHAR"/>
  <COLUMN SOURCE="2" NAME="RepCd-Name" xsi:type="SQLVARYCHAR"/>
 </ROW>
</BCPFORMAT>
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of thayduck

ASKER

I did what you suggested and eveything is good now.

Thanks.