Importing XML - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Importing XML

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-03-12 : 08:58:40

I have a directory that contains x amount of xml files. I wouldlike to import them into a XML field.

I have tried
CREATE TABLE T (IntCol int, XmlCol xml)
GO
DECLARE @FilePath varchar(1000) = '\\TEST\XML Reports\'
DECLARE @FileName varchar(100) = '253461_7007_20699.xml'
Declare @Full Varchar(200) = @FilePath + @FileName
INSERT INTO T(XmlCol)
SELECT * FROM OPENROWSET(
BULK @Full,
SINGLE_BLOB) AS x
GO

SELECT * FROM T

But get an error
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '@Full'.

Question
1. is is possible to pass a parameter instead of a filename
2. If 1 is possible, can i create a cursor to loop through all the files in the directory and import them using Tsql ?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-12 : 09:31:17

It does not like a variable in that context. This would parse:

INSERT INTO T(XmlCol)
SELECT * FROM OPENROWSET(
BULK '\\TEST\XML Reports\253461_7007_20699.xml',
SINGLE_BLOB) AS x

So would this:

DECLARE @FilePath	varchar(1000) = '\\TEST\XML Reports\'
DECLARE @FileName	varchar(100) = '253461_7007_20699.xml'
Declare @Full Varchar(200) = @FilePath + @FileName

DECLARE @sql NVARCHAR(4000);
SET @sql = 
'INSERT INTO T(XmlCol) SELECT * FROM OPENROWSET( BULK ''' + @Full+ ''',SINGLE_BLOB) AS x';

exec sp_executesql @sql ;

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-03-12 : 10:16:33

Thank you :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources