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
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 OPENROWSET (BULK ‘ ‘ ‘ + @xmlFileName + ‘ ‘ ‘ , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
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!