CODEWITHSUNDEEP
sql-serverxmlt-sqlbcpopenrowset
osm0sis
osm0sis

Reputation: 31

Getting 'Attribute "FIELDQUOTE" could not be specified for this type' when using .xml format file for a T-SQL OPENROWSET BULK Query

I am getting an 'Attribute "FIELDQUOTE" could not be specified for this type.' that has me pulling my hair out trying to perform an OPENROWSET query to work so I can bulk import records from a .csv file.

I've tried to simplify things to narrow down where my issue might be coming from but am still at a loss.

My SQL Server query is simple:

CREATE TABLE #TempMines (
MINE_ID NVARCHAR(10),
COAL_METAL_IND int,
CURRENT_MINE_TYPE DEC(5,2),
CURRENT_MINE_STATUS NVARCHAR(5),
CURRENT_STATUS_DT DATE);

INSERT INTO #TempMines (MINE_ID, COAL_METAL_IND, CURRENT_MINE_TYPE, CURRENT_MINE_STATUS, CURRENT_STATUS_DT)
SELECT MINE_ID, COAL_METAL_IND, CURRENT_MINE_TYPE, CURRENT_MINE_STATUS, CURRENT_STATUS_DT
FROM OPENROWSET(
    BULK 'C:\sqlStuff\sampleData.csv',
    FORMATFILE = 'C:\sqlStuff\sampleFormat.xml'
) AS TempMinesData;

Therefore my .xml format file is also strait forward:

<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="12" COLLATION="SQL_Latin1_General_CP1_CI_AS" FIELDQUOTE='"'/>
    <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12" FIELDQUOTE='"'/>
    <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12" FIELDQUOTE='"'/>
    <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="7" COLLATION="SQL_Latin1_General_CP1_CI_AS" FIELDQUOTE='"'/>
    <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="12" FIELDQUOTE='"'/>
  </RECORD>
  <ROW>
    <COLUMN SOURCE="1" NAME="MINE_ID" xsi:type="SQLNVARCHAR"/>
    <COLUMN SOURCE="2" NAME="COAL_METAL_IND" xsi:type="SQLINT"/>
    <COLUMN SOURCE="3" NAME="CURRENT_MINE_TYPE" xsi:type="SQLDECIMAL" PRECISION="5" SCALE="2"/>
    <COLUMN SOURCE="4" NAME="CURRENT_MINE_STATUS" xsi:type="SQLNVARCHAR"/>
    <COLUMN SOURCE="5" NAME="CURRENT_STATUS_DT" xsi:type="SQLDATE"/>
  </ROW>
</BCPFORMAT>

It's just a local host database. I've double checked my permissions for the directory and individual .xml and .csv files, and am running SSMS as an admin. File Permissions

File Permission too

Additionally I've double checked to make sure my line endings are consistent with my the settings in my .xml file line endings

However, no matter what I do I seem to come back to this error and am not sure what the cause could possibly be.

Error

This seems like such a simple task! Any help is greatly appreciated!

Upvotes: 0

Views: 164

Answers (0)

Related Questions