sql server bulk insert xml formant file



Normally I can find the answer to my SQL Server issues on here, but I can't seem to in this case.


I am trying to load a fixed width text file using Bulk Insert and an XML format file. I have used the same process and XML file on another fixed width, excpt with less columns.


SQL Server Code



BULK INSERT [dbo].[raw_addr] FROM '\\PATH\addr.txt'
WITH (
FORMATFILE = '\\PATH\addr.xml',
ROWTERMINATOR='\r\n');


XML Code:





<?xml version="1.0"?>
<BCPFORMAT xmlns="http://ift.tt/1jM4qUa" xmlns:xsi="http://ift.tt/1qxi0ge">
<RECORD>
<FIELD ID="1" xsi:type="CharFixed" LENGTH="9" />
<FIELD ID="2" xsi:type="CharFixed" LENGTH="50" />
<FIELD ID="3" xsi:type="CharFixed" LENGTH="50" />
<FIELD ID="4" xsi:type="CharFixed" LENGTH="10" />
<FIELD ID="5" xsi:type="CharFixed" LENGTH="50" />
<FIELD ID="6" xsi:type="CharFixed" LENGTH="10" />
<FIELD ID="7" xsi:type="CharFixed" LENGTH="30" />
<FIELD ID="8" xsi:type="CharFixed" LENGTH="3" />
<FIELD ID="9" xsi:type="CharFixed" LENGTH="30" />
<FIELD ID="10" xsi:type="CharTerm" TERMINATOR="\r\n" LENGTH="4" />
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="address_number" xsi:type="SQLNVARCHAR" />
<COLUMN SOURCE="2" NAME="addr_linel" xsi:type="SQLNVARCHAR" />
<COLUMN SOURCE="3" NAME="addr_line2" xsi:type="SQLNVARCHAR" />
<COLUMN SOURCE="4" NAME="street_no" xsi:type="SQLNVARCHAR" />
<COLUMN SOURCE="5" NAME="street_name" xsi:type="SQLNVARCHAR" />
<COLUMN SOURCE="6" NAME="street_type" xsi:type="SQLNVARCHAR" />
<COLUMN SOURCE="7" NAME="locality" xsi:type="SQLNVARCHAR" />
<COLUMN SOURCE="8" NAME="state" xsi:type="SQLNVARCHAR" />
<COLUMN SOURCE="9" NAME="country" xsi:type="SQLNVARCHAR" />
<COLUMN SOURCE="10" NAME="postcode" xsi:type="SQLNVARCHAR" />
</ROW>
</BCPFORMAT>



No comments:

Post a Comment