How To Load Multiple XML Files

Question

How To Load Multiple XML Files

Teee-SQL

SSCrazy

Points: 2218
More actions
August 22, 2013 at 1:13 am

#395344

Hi All,
I'm required to load multiple XML Files from one folder into a database table, I also need to check that the file starts with "Filename"
e.g Departments_1 I need to make sure the the file starts with "department" as there'll be other files in the folder e.g Departments_2
at the moment I am able to load one file at a time using the script below that checks if a file exists before loading,
is it possible to achieve this using T-Sql? any help would be appreciated.
Here's part of the script that I'm using to load the data into a temp table below:
--Load all XML data first
DECLARE @isExists INT
--Cost Centres
exec master.dbo.xp_fileexist 'C:\inetpub\wwwroot\Prof1t\FTP\<CLIENT_FOLDER>\Departments_1.xml', @isExists OUTPUT
IF @isExists = 1
BEGIN
print 'Departments.xml exists'
INSERT INTO tmpCostCentreXML (CostCentreCode, CostCentreName)
SELECT Y.CostCentres.query('CostCentreCode').value('.', 'VARCHAR(30)'),
Y.CostCentres.query('DepartmentName').value('.', 'VARCHAR(60)')
FROM (
SELECT CAST(x AS XML)
FROM OPENROWSET(
BULK 'C:\inetpub\wwwroot\Prof1t\FTP\<CLIENT_FOLDER>\Departments_1.xml',
SINGLE_BLOB) AS T(x)) AS T(x)
CROSS APPLY x.nodes('Departments/Department') as Y(CostCentres);
print 'Loaded Cost Centre XML'
END
ELSE
BEGIN
print 'Departments_1.XML doesn''t exist'
END
Here's the XML example:
<?xml version="1.0" encoding="UTF-8"?>
-<Departments> -<Department> <DepartmentName>MAGNUM POC</DepartmentName> <CostCentreCode>30</CostCentreCode> </Department> </Departments>
Thanks
Teee

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

T-SQL probably isn't the best tool for this job. This is something that I'd use SSIS for.

But, having made my disclaimer, you can probably do it in T-SQL, but you may need xp_dirtree (officially undocumented, so can change, use at your own risk, etc...). Here's a post that explains it (I haven't tried the code in the post), http://www.patrickkeisler.com/2012/12/how-to-use-xpdirtree-to-list-all-files-part2.html

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Teee-SQL SSCrazy Points: 2218 More actions · Answer 2

Thanks I'll have a look, I have found a temp work around in the meantime. 🙂

Polymorphist SSCommitted Points: 1909 More actions · Answer 3

I had to do this for the first time recently and found that using SSIS is the best way to go. You can both load the XML files into a table and then use either an XML task or SQL task to get the data out of the nodes into their respective tables, if that's what you are ultimately doing.