• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 92
  • Last Modified:

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>
0
thayduck
Asked:
thayduck
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
as you can check yourself in the XML format files:
http://msdn.microsoft.com/en-us/library/ms187833.aspx
it's not possible in the bulk import to directly combine 2 parts into 1 column

you will need to import into a staging table that has the needed structure, and go on from there.
0
 
thayduckAuthor Commented:
I did what you suggested and eveything is good now.

Thanks.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now