error reading xml file using BULK INSERT 1

DougP · Jun 6, 2005

The File test.xml exists but I get this error when trying to read it

Server: Msg 4860, Level 16, State 1, Line 4
Could not bulk insert. File 'C:\test.xml' does not exist.

(0 row(s) affected)

Code:

-- Create a table to hold the data
CREATE TABLE #textfile (line varchar(8000))
-- Read the text file into the temp table
BULK INSERT #textfile FROM 'C:\test.xml'
GO
-- Now read it
SELECT * FROM #textfile

I am in Query Analyzer for testing
Any ideas?
TIA

DougP, MCP, A+

gmmastros · Jun 6, 2005

The files you reference from QA are actually referenced from the server. The C: drive you think is being used may not be the C: drive actually used.

Or explained another way, does this file that "exists", exist on the same computer that is running SQL Server?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

DougP · Jun 6, 2005

no
Its on a client machine
So I need this \\Mt-dev0001\CdrvDoug\test.xml

I browsed to network places and found the file then drug it to the Start Run command and that what it gave me.

then I re-ran it and now I get this error

Server: Msg 4861, Level 16, State 1, Line 4
Could not bulk insert because file '\\Mt-dev0001\CdrvDoug\test.xml' could not be opened. Operating system error code 1326(Logon failure: unknown user name or bad password.).

(0 row(s) affected)

How do I add a login? or What do I do now?

DougP, MCP, A+

gmmastros · Jun 6, 2005

My guess is that the login account used for SQL Server does not have access to "\\Mt-dev0001\CdrvDoug".

There are several ways around this, and I'm sure others will have suggestions too.

If this is a "once and done" bulk insert, my suggestion would be to copy the file to the sql server machine and then re-run your code.

If you need to do this repeatedly, I would suggest making a folder on the SQL Server machine (limiting the permissions as strictly as possible), then copying the file to the server before bulk inserting it.

I hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

DougP · Jun 6, 2005

How can I add the permissions to that folder?
I have 'sa' privileges to the SQL server I am using.
I can't get to the SQL server machine to add any folders to it. I can just see it through Enterprise Manager. I have full permissions through there.

TIA

DougP, MCP, A+

JamesLean · Jun 6, 2005

Another option is to grant permissions on the \\Mt-dev0001\CdrvDoug share to the SQL Server service account. Do you have access to set permissions on that folder?

--James

gmmastros · Jun 6, 2005

You could try using xp_cmdShell to add the folder. Something like this...

xp_cmdshell 'md c:\BulkInsertFiles'

I'm not sure how to set the folder permissions from xp_cmdshell.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

DougP · Jun 6, 2005

Ok I did that in Query Analyzer and got NULL in the grid
But again when I try to copy the file I get the same error

I can use the AddUsers command like so
xp_cmdshell 'AddUsers ...'
but what user do I put in for permissions on my drive?
SA ???

DougP, MCP, A+

JamesLean · Jun 6, 2005

As I said above, you need to provide permissions on whichever folder you choose to use to the SQL Server service account.

--James

DougP · Jun 6, 2005

Ok thanks
I got permission to copy the file to the server.
But now I have another error

Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Line 5
XML parsing error: Invalid at the top level of the document.

For some reason I can only see 255 characters on screen, so I don't know if this is all its reading in? Or the Query Analyzer has a screen limit of 255?

here is all my code

Code:

-- Create a table to hold the data
CREATE TABLE #textfile (line varchar(2000))
-- Read the text file into the temp table
BULK INSERT #textfile FROM 'D:\FTP AREA\test.xml'
GO
-- Now read it
SELECT * FROM #textfile

GO

DECLARE @hdoc int
DECLARE @doc varchar(8000)
Set @doc = 'Select * From #textfile'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc

DougP, MCP, A+

philhege · Jun 6, 2005

You can expand the result set in QA by adjusting the value in Tools -> Options ->Results-Maximum Characters Per Column (the text box about a third of the way down the tab).

So, what's the top level of your document? is it the XML declaration? Did you try stripping it out?

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'm not as think as you confused I am.
-----------
Flabbergasted (a.): Amazed at how much weight one has gained.
-----------
Oyster (n.): One who sprinkles their conversation with Yiddish expressions.

DougP · Jun 6, 2005

yes I tried that too
here is the xml now same error though.

Code:

<root>
	<Accession></Accession>
	<AdmitDate></AdmitDate>
	<BillingNumber></BillingNumber>
	<Comments></Comments>
	<CopyPhysicianIDs></CopyPhysicianIDs>
	<CustName>PWENT</CustName>
	<DDJobNumber>77420537127</DDJobNumber>
	<DictationBegin>03/07/05 06:19:59 AM</DictationBegin>
	<DictationEnd>3/7/2005 7:35:00 AM</DictationEnd>
	<DictatorID>7831</DictatorID>
	<DischargeDate></DischargeDate>
	<DOB></DOB>
	<DocType>2</DocType>
	<Quality2Begin>3/8/2005 8:48:08 AM</Quality2Begin>
	<Quality2End>3/8/2005 8:49:10 AM</Quality2End>
	<Quality2ID>MTWMDC0001</Quality2ID>
	<QualityBegin>03/08/05 04:01:58 AM</QualityBegin>
	<QualityEnd>03/08/05 04:12:11 AM</QualityEnd>
	<QualityID>MAPVAS0001</QualityID>
	<ReferIn></ReferIn>
	<ReversePatName>xxxxxxxxx</ReversePatName>
	<Review>0</Review>
</root>

it shows just fine in Visual Studio.net in the data view. each column is correct.
the only way this seems to work is embedding XML in the T-SQL as in the example:

Code:

DECLARE @hdoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
      <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
      <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
   <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
      <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc

I need to read a file off a drive if possible.
Even If I copy the example above to a file I still get the same error.

DougP, MCP, A+

JamesLean · Jun 6, 2005

You're not selecting the data into the variable properly. Try this:

Code:

-- Create a table to hold the data
CREATE TABLE #textfile (line varchar(8000))

-- Read the text file into the temp table
BULK INSERT #textfile FROM 'D:\FTP AREA\test.xml'
GO

DECLARE @hdoc int,
  @doc varchar(8000)

SELECT @doc = line FROM #textfile
PRINT @doc

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc

--James

DougP · Jun 6, 2005

Ok great ssomething is working
I got this now in the messages window and no errors

Code:

(2 row(s) affected)

<ClinicalDocument><Demographics><Accession></Accession>

But how do I save the XML in @doc to a table?
I mean this prepares the document:
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc
for what?
Do you "Insert into SomeTable @doc" or something?

TIA

DougP, MCP, A+

JamesLean · Jun 6, 2005

First, if that's what's in your variable then it looks like you have a carriage return in your XML file. This is causing the XML string to be inserted over two (or more) rows in your table so your variable isn't getting the whole string.

Second, once you have the whole XML string in your variable you need to use OPENXML to actually read the values from it. Look it up in BOL for more info.

--James

DougP · Jun 6, 2005

Ok great thanks
have a star

DougP, MCP, A+

DougP · Jun 6, 2005

Final Rendition:
SQL code

Code:

-- Create a table to hold the data
DROP TABLE #textfile
-- Create a table to hold the data
CREATE TABLE #textfile (line varchar(8000))

-- Read the text file into the temp table
BULK INSERT #textfile FROM 'D:\FTP AREA\test.xml'
GO

DECLARE @hdoc int,
  @doc varchar(8000)

SELECT @doc = line FROM #textfile
PRINT @doc

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc

SELECT * FROM OpenXML(@hDoc, '/ClinicalDocument',1) 
         WITH (Accession varchar(50), 
		AdmitDate varchar(50),
		BillingNumber varchar(50))

XML file read in 'D:\FTP AREA\test.xml' to make it work
Note the information must be in attributes not elements

Code:

<?xml version="1.0"?>
<ClinicalDocument Accession="234" AdmitDate="12/3/04" BillingNumber="33232"/>

The following below does not work, must have each item in an attribute, not elements. 

<?xml version="1.0"?>
<ClinicalDocument><Accession>234</Accession><AdmitDate>12/3/04</AdmitDate><BillingNumber>33232></BillingNumber></ClinicalDocument>

DougP, MCP, A+

DougP · Jun 6, 2005

One last problem
trying to insert the contents of the XML @doc into another table.

Error = columns don't matchup

Code:

ID ParentID node  Name                prefix  URI   dataType  prev   text
-------------------------------------------------------------------------------
0	NULL	1	ClinicalDocument	NULL	NULL	NULL	NULL	NULL
2	0	2	Accession	NULL	NULL	NULL	NULL	NULL
3	0	2	AdmitDate	NULL	NULL	NULL	NULL	NULL
4	0	2	BillingNumber	NULL	NULL	NULL	NULL	NULL
5	0	2	Comments	NULL	NULL	NULL	NULL	NULL
6	0	2	CopyPhysicianIDs	NULL	NULL	NULL	NULL	NULL
7	0	2	CustName	NULL	NULL	NULL	NULL	NULL
57   [red]7	3	#text	NULL	NULL	NULL	NULL	PWENT[/red]
8	0	2	DDJobNumber	NULL	NULL	NULL	NULL	NULL
58	8	3	#text	NULL	NULL	NULL	NULL	77420537127

When I do a 'Select * FROM OpenXML(@hDoc, '/ClinicalDocument',1)'
There are extra columns for each value besides the column names.
Need to pick out the values somehow.
Does my Select Statement need tweaking again?

DougP, MCP, A+

JamesLean · Jun 7, 2005

Attribute-centric

Code:

DECLARE @xml varchar(8000),
	@doc int

SET @xml = '<ClinicalDocument Accession="234" AdmitDate="12/3/04" BillingNumber="33232" />'

EXEC sp_xml_preparedocument @doc OUTPUT, @xml

SELECT * FROM OPENXML(@doc, '/ClinicalDocument', 1)
WITH (Accession varchar(50),
	AdmitDate varchar(50),
	BillingNumber varchar(50)
)

EXEC sp_xml_removedocument @doc

Element-centric

Note the XML does not need the carriage returns - just here for display purposes

Code:

DECLARE @xml varchar(8000),
	@doc int

SET @xml = '<ClinicalDocument>
	<Accession>234</Accession>
	<AdmitDate>12/3/04</AdmitDate>
	<BillingNumber>33232</BillingNumber>
</ClinicalDocument>'

EXEC sp_xml_preparedocument @doc OUTPUT, @xml

SELECT * FROM OPENXML(@doc, '/ClinicalDocument', 2)
WITH (Accession varchar(50),
	AdmitDate varchar(50),
	BillingNumber varchar(50)
)

EXEC sp_xml_removedocument @doc

Inserting to another table

Code:

INSERT table (accession, admitdate, billingnumber)
SELECT * FROM OPENXML(@doc, '/ClinicalDocument', 2)
WITH (Accession varchar(50),
	AdmitDate varchar(50),
	BillingNumber varchar(50)
)

--James

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

error reading xml file using BULK INSERT 1

MIS

Programmer

MIS

Programmer

MIS

Programmer

Programmer

MIS

Programmer

MIS

Programmer

MIS

Programmer

MIS

Programmer

MIS

MIS

MIS

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor