# how to import file xml with bcp utility

• ### Question

• Hello

I created table

CREATE TABLE table_test (xmldata xml);
GO

I don't understand how to import the xml file in my table with bcp utility?.

my file XML is on the desktop.

someone can give me an example?

• Edited by Wednesday, June 22, 2016 10:34 PM
Wednesday, June 22, 2016 9:49 PM

• No you can not use as XML because using BCP the row is inserted into the table line by line and it is not in the exact XML format. Hence it will throw error message when you try to insert into an XML data type field. It is an incomplete XML formatted line.

To be picky: there is no problem to have BCP to insert records that runs over line breaks, it all depends on what terminators you have. The problem with the XML file is no terminator at all. The one exception would be if the entire XML is a single line and it is followed by a line break. Or, by some really lucky circumstance, the XML document is followed by a white-space character sequence that does not apper in the XML itself.

Here are two quite poor workarounds:

1) Define the closing tag as the record terminator and import into an nvarchar(MAX) column. Then copy to an XML column restoring the closing tag. Note that this will not work if the XML is a fragment with repeating top nodes.

2) Find out the exact length of the XML file, and then craft a format file with the length of the file in the fourth column and thus handle it as a fixed length file.

I have not tested any of these.

• Marked as answer by Thursday, June 30, 2016 10:04 PM
Friday, June 24, 2016 10:31 AM

### All replies

• I would prefer to go with the second option because XML contains should normally treated as a single blob and not read as line by line.

drop table xmltab
create table xmltab(myxml nvarchar(max))

declare @sql varchar(500)
set @sql = 'bcp [myDB].[dbo].[xmltab] in "C:\Users\abc\Desktop\text.xml" -T -c'
exec master.dbo.xp_cmdshell  @sql
go
select * from xmltab
-----------------------------
drop table xmltab1
create table xmltab1(myxml xml)
INSERT INTO xmltab1(myxml)
SELECT * FROM OPENROWSET(
BULK 'C:\Users\abc\Desktop\text.xml',
SINGLE_BLOB) AS x;
go
select * from xmltab1

Regards, RSingh

Thursday, June 23, 2016 4:13 AM
• I would prefer to go with the second option because XML contains should normally treated as a single blob and not read as line by line.

drop table xmltab
create table xmltab(myxml nvarchar(max))

declare @sql varchar(500)
set @sql = 'bcp [myDB].[dbo].[xmltab] in "C:\Users\abc\Desktop\text.xml" -T -c'
exec master.dbo.xp_cmdshell  @sql
go
select * from xmltab
-----------------------------
drop table xmltab1
create table xmltab1(myxml xml)
INSERT INTO xmltab1(myxml)
SELECT * FROM OPENROWSET(
BULK 'C:\Users\abc\Desktop\text.xml',
SINGLE_BLOB) AS x;
go
select * from xmltab1

Regards, RSingh

thanks for the replywhy in the first example you use narchar?my column is a xml type. Can I use bcp utility with xml column ? in this case as?
Thursday, June 23, 2016 5:07 PM
• Importing an XML document with BCP is not that terribly simple, because BCP is not designed for this scenario. It would only be possible under special circumstances.
Instead use OPENROWSET as Rajen showed you. However, this method will not work if you want to load the file in an SQL Server instance which runs on a different machine, unless you can place the file somewhere SQL Server can read the file.

Thursday, June 23, 2016 9:22 PM
• thanks for the replywhy in the first example you use narchar?my column is a xml type. Can I use bcp utility with xml column ? in this case as?
No you can not use as XML because using BCP the row is inserted into the table line by line and it is not in the exact XML format. Hence it will throw error message when you try to insert into an XML data type field. It is an incomplete XML formatted line.

Regards, RSingh

Friday, June 24, 2016 8:33 AM
• No you can not use as XML because using BCP the row is inserted into the table line by line and it is not in the exact XML format. Hence it will throw error message when you try to insert into an XML data type field. It is an incomplete XML formatted line.

To be picky: there is no problem to have BCP to insert records that runs over line breaks, it all depends on what terminators you have. The problem with the XML file is no terminator at all. The one exception would be if the entire XML is a single line and it is followed by a line break. Or, by some really lucky circumstance, the XML document is followed by a white-space character sequence that does not apper in the XML itself.

Here are two quite poor workarounds:

1) Define the closing tag as the record terminator and import into an nvarchar(MAX) column. Then copy to an XML column restoring the closing tag. Note that this will not work if the XML is a fragment with repeating top nodes.

2) Find out the exact length of the XML file, and then craft a format file with the length of the file in the fourth column and thus handle it as a fixed length file.

I have not tested any of these.

• Marked as answer by Thursday, June 30, 2016 10:04 PM
Friday, June 24, 2016 10:31 AM
• Yes I do agree Erland. Sometimes it returns EOF not exist in the file and bla bla.

Another work around could be to concatenate all rows using below query once all lines are inserted into the table so as to get a full XML format.

SELECT Stuff((SELECT ' ' + [VALUE]
FROM   @TEMP
FOR xml path('')), 1, 1, '') COLS


Regards, RSingh

Friday, June 24, 2016 11:36 AM
• Yes, that could work. But it is a little precarious, because you would need to have an IDENTITY column to track the row number and hope that it gets populated in order. Also, you need to use the TYPE option. On the other hand, there is no need to strip the extra delimiter you add. Thus, you would do something like:

SELECT (SELECT value + char(13) + char(10)
ORDER  BY linenumber
FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')

Friday, June 24, 2016 11:56 AM
• Yes rightly pointed out. Thanks.

Regards, RSingh

Friday, June 24, 2016 11:58 AM
• but so it is right?

bcp s1.dbo.table_test in myfile.xml -T -c -S localhost\sqlexpress

but I'm not sure that has loaded the the whole xml file contents.

Select *from table_test does not show the entire file.

my file is large 110MB
Monday, June 27, 2016 2:32 PM
• How many rows are there in the table?

How many lines are there in the file when you open it in an editor?

Is there a line break after the final closing tag?

Monday, June 27, 2016 9:23 PM
• Is there a line break after the final closing tag? yes.

How many rows are there in the table? the table is emptybefore loading

How many lines are there in the file when you open it in an editor?113782

Monday, June 27, 2016 10:50 PM
• How many rows are there in the table? the table is emptybefore loading