How To Import A XML File Into SQL Server 2005…
- 5
- Add a Comment
Hello again…
Today I wanted to share how to import a XML file into SQL Server 2005. There are different ways to import a XML file either through T-SQL, SSIS, VB Script, etc. I have found the easiest method for just getting and XML file into SQL Server 2005 is to use T-SQL.
I found the following T-SQL code on the internet and works without issue. You can find the link here. Here is the code:
CREATE TABLE XmlImportTest(
xmlFileName VARCHAR(300) NOT NULL,
xml_data XML NOT NULL
)
GO
DECLARE @xmlFileName VARCHAR(300)
SELECT @xmlFileName = ‘c:\customer.xml’
– dynamic sql is just so we can use @xmlFileName variable in OPENROWSET
EXEC(‘INSERT INTO XmlImportTest(xmlFileName, xml_data)
SELECT ‘ ‘ ‘ + @xmlFileName + ‘ ‘ ‘, xmlData
FROM(
SELECT *
FROM OPENROWSET (BULK ‘ ‘ ‘ + @xmlFileName + ‘ ‘ ‘ , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
‘)
GO
After you import the file you will see the XML file stored within SQL Server 2005 by running the SELECT * FROM XmlImportTest table.
Tomorrow I will post how to “parse” out the XML file into a relational format that you can then import into a table!

5 Comments
Faisal
July 5th, 2008
at 1:53pm
thanks
Erwin van der Horst
December 16th, 2008
at 6:14am
How do I get the identity value of the row just inserted.
SCOPE_IDENTITY() does not work. @@IDENTITY can be wrong too.
Thanks!
VISHNU
April 4th, 2009
at 3:36am
thanks so much
amy
September 2nd, 2009
at 11:07am
I am getting following error
Cannot bulk load because the file “C:\Batch.20090805180934.xml” could not be opened. Operating system error code 5(Access is denied.).
Can you help please.
Thanks,
Archana
sqlsquirrel
September 2nd, 2009
at 8:13pm
Thank you for putting the error code as well. Normally when I have seen this error message it could mean one of 2 things… 1) The .xml file is write protected
or
2) The service account that SQL Server is running under does not have access to this file. I see you have the file on the C drive so I find it hard that SQL Server would not have access to this file.
I would check the file to see if there are any security concerns around it.
Let us know what you find!
Squirrel