E-Mail:

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!


					
					

One Comment

thanks

What Do You Think?

 

Want to Start a Blog Here for Free?

Are you an expert in one subject or another? If your goal is to help others and dispense your hard-earned information back to the community, get involved in our community site today! You can write about anything - no matter the topic. Exceptional candidates will be offered the chance to contribute to (and generate revenue from) the main Lockergnome site. Join us today!

Administration - Jan 4, 2008

The Different States Of Worker Threads

63 queries / 0.189 seconds.