1

I have an XML file that I want to import into SQL Server 2012. I haven't done it before and I don't know a lot. After some research, I tried to open the xml file with the following command that was run in SQL Server Management Studio. 

SELECT CAST(x as XML) FROM OPENROWSET(
BULK 'C:\bulk\Users_test1111.xml',
SINGLE_BLOB
) AS X

For this I get the following error: 

Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "C:\bulk\Users_test1111.xml" could not be opened. Operating system error code 3(The system cannot find the path specified.).

What do you suggest to do to get the file into the database?

Andrew
  • 6,254
  • 16
  • 59
  • 93
  • The error message `The system cannot find the path specified.` suggests that the file can't be found where it should be. Check that it's really there. – jpw Jul 18 '13 at 12:05
  • @jpg It is, but locally. Should it be on the same server as the sql server? – Andrew Jul 18 '13 at 12:06
  • 2
    Since you are referencing a path that is local to the machine you run the query on it has to be a path that is valid on that machine. So yes. – jpw Jul 18 '13 at 12:07
  • @jpw ok, this is solved. Now how to import that file into sql server? – Andrew Jul 18 '13 at 12:12
  • What do you mean by "import"? Do you want to store it "as is" in an XML column? Or parse it into multiple rows? – TToni Jul 18 '13 at 12:21
  • duplicate? http://stackoverflow.com/questions/1814761/parse-and-import-xml-into-table-in-sql-server – tgolisch Jul 18 '13 at 12:36

3 Answers3

1

I'm guessing that you're having the same issue that I am. Some of the commands used to load a file using SQL Server require the file to be on the actual DB server itself. If you're running SQL Server Management Studio from a different machine, then you'll have this issue.

Jon Senchyna
  • 7,867
  • 2
  • 26
  • 46
0

Use SSIS. Create an SSIS package with an XML data source. If you can't use/don't have integration services, use UNC file paths to eliminate local/network standpoint issues.

Edit:I failed to exercise google-fu prior to shooting off at the mouth and one of my initial ideas was incorrect.

Eric Hauenstein
  • 2,557
  • 6
  • 31
  • 41
0

Try this, I included some extra columns to show you.

The file also needs to be on the server itself (the path needs to be located on the server)

INSERT INTO TestTable(TestId, TestXml, TestText)

Values('1', (  
SELECT * FROM OPENROWSET(
   BULK 'C:\bulk\Users_test1111.xml',
   SINGLE_BLOB) AS x), 'Some Test Text')
KeyboardFriendly
  • 1,798
  • 5
  • 32
  • 42