Import XMLs Files into 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

SQL Server 2008 Forums

Transact-SQL (2008)

Import XMLs Files into Table

Author

Topic

IK1972

56 Posts

Posted - 2012-09-15 : 16:37:09

I have 100 XMLs files and I want to import into one table.

Like my table structure is like this.

CREATE Table dbo.Test(ID int, Data XML)

now how i can import all xml file into this table and every row should hold only one XML file so If I have 100 XMLs files then after import this table should have 100 rows. I want to do this through TSQL not with SSIS.

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-15 : 16:50:51

Do the following for each file

INSERT INTO dbo.Test
SELECT 1 AS ID,
       *
FROM   OPENROWSET(BULK 'c:\temp\xmltest.txt', SINGLE_BLOB) AS DATA

In the example above, C:\temp refers to the C drive on the server. If your files are at another location, use the network path.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-15 : 22:00:35

you need a loop to loop through files and insert them one by one onto table or you need 100 inserts.

i would put list of file paths in an excel sheet and generate the 100 inserts from excel. then copy paste it onto a ssms window and execute

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

IK1972

56 Posts

Posted - 2012-09-15 : 23:13:02

I want to do this in automatic job or with SP.

Issue in both solution is I dont know the file name and 100 I give as example maybe some day its 100 and some day more and file names are dynamic GUID so I dont know the file name I only know the Folder/Location where these files exists.

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-16 : 10:42:07

Use some type of a script that you are comfortable with - cmd, perl, Powershell or anything else. If you are using Powershell, a script such as this would be what you need - this is only a template, you will need to make appropriate changes to your path, filenames etc.:

$id = 1;
foreach ($filename in Get-ChildItem 'C:\temp\*.xml' )
{
    Write-Host "Inserting id = $id, filename $filename";
    sqlcmd -d YourDatabaseNameHere -Q `
    ( `
        "INSERT INTO dbo.Test SELECT $id AS ID,*  FROM OPENROWSET(BULK '$filename', SINGLE_BLOB) AS DATA " `
    )
    $id++;
}

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-16 : 12:21:11

quote:
Originally posted by IK1972

I want to do this in automatic job or with SP.

Issue in both solution is I dont know the file name and 100 I give as example maybe some day its 100 and some day more and file names are dynamic GUID so I dont know the file name I only know the Folder/Location where these files exists.

Thanks

you can still make the package execute automatically from the job.

see an example here ( a package that does file processing. In yoour case you just need to replace it with XML population step)

http://visakhm.blogspot.com/2012/05/package-to-implement-daily-processing.html

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources