CODEWITHSUNDEEP
sqlsql-serverxml
Andrew
Andrew

Reputation: 6394

How to import an XML file into SQL Server 2012

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?

Upvotes: 1

Views: 11631

Answers (3)

Jon Senchyna
Jon Senchyna

Reputation: 8047

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.

Upvotes: 1

KeyboardFriendly
KeyboardFriendly

Reputation: 1798

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')

Upvotes: 0

Eric Hauenstein
Eric Hauenstein

Reputation: 2565

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.

Upvotes: 0

Related Questions