thayduck
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\ sqlprdfile s\FMR\Form ats\Repair Code.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_Gene ral_CP1_CI _AS"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="35" COLLATION="SQL_Latin1_Gene ral_CP1_CI _AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="RepCD" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="2" NAME="RepCd-Name" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>
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
FORMATFILE='\\fmrprdfiles\
) 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_Gene
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="35" COLLATION="SQL_Latin1_Gene
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="RepCD" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="2" NAME="RepCd-Name" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks.