Importing multiple xml files with T-SQL

Asked Aug 30 '13 at 08:27

Active Aug 30 '13 at 08:30

Viewed 2,074 times

How can I insert multiple xml files into a SQL Server table?

I have managed to get only 1 named file with this script:

CREATE TABLE #ZSK70 (Name varchar(25) , Value varchar(30),);

INSERT INTO #ZSK70 (Name, Value) 
SELECT X.variable.query('Name').value('.', 'varchar(25)'),
       X.variable.query('Value').value('.', 'varchar(30)')
FROM ( 
SELECT CAST(x AS XML)
FROM OPENROWSET(
     BULK 'path+filename.xml',

     SINGLE_BLOB) AS T(x)
     ) AS T(x)
CROSS APPLY x.nodes('CollectedData/variable') AS X(variable);

Any help is kindly appreciated.

edited Aug 30 '13 at 08:30

marc_s

732,580
175
1,330
1,459

asked Aug 30 '13 at 08:27

user2725348

xml files have the same structure? – Anda Iancu Aug 30 '13 at 08:30
This might help: http://stackoverflow.com/questions/2969866/bulk-insert-multiple-xml-files-with-ssis-2008 – Anda Iancu Aug 30 '13 at 08:35
the exact same structure. only the name changes but with the same lenght. – user2725348 Aug 30 '13 at 08:35
i'm working with sql server 2005 – user2725348 Aug 30 '13 at 08:37
1

Check: http://stackoverflow.com/questions/6956409/ssis-reading-multiple-xml-files-from-folder – Anda Iancu Aug 30 '13 at 08:40

Importing multiple xml files with T-SQL

0 Answers0