How To Import A XML File Into SQL Server 2005…

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!