Importing XML into a table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Importing XML into a table

Author

Topic

sven2
Yak Posting Veteran

57 Posts

Posted - 2010-04-01 : 04:36:54

Hello,

I want to import an XML file into a table called ImporterenWerknemers.
In this table I have the same fields that are present in the XML file.

I try the do this with the following script:

Use TA
go
DECLARE @xmlFileName VARCHAR(300)
SELECT @xmlFileName = 'c:\TA\testxml.xml'

INSERT INTO ImporterenWerknemers(xmlFileName, xml_data)

SELECT ''' + @xmlFileName + ''', xmlData
FROM
(
SELECT *
FROM OPENROWSET (BULK 'c:\TA\TestXML.xml' , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)

But I always get an error like:

Msg 207, Level 16, State 1, Line 4
Invalid column name 'xmlFileName'.

What am I doing wrong?

Thanks in advance,
Sven.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 05:43:14

you need to use dynamic sql for that. some thing like


INSERT INTO ImporterenWerknemers(xmlFileName, xml_data)


EXEC ('SELECT ''' + @xmlFileName + ''', xmlData 
FROM
(
SELECT * 
FROM OPENROWSET (BULK 'c:\TA\TestXML.xml' , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources