none
how to import file xml with bcp utility RRS feed

  • 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?

    Thank you in advance


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

Answers

  • 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 namelessg 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 reply

    why 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 reply

    why 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 namelessg 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)
              FROM   loadtbl
              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

    Well, I did mean after loading the file.

    Tuesday, June 28, 2016 9:08 PM