[SQL Server] How To Load Multiple XML Files |
| How To Load Multiple XML Files Posted: 21 Aug 2013 06:13 PM PDT 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_2at 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 firstDECLARE @isExists INT--Cost Centresexec master.dbo.xp_fileexist 'C:\inetpub\wwwroot\Prof1t\FTP\<CLIENT_FOLDER>\Departments_1.xml', @isExists OUTPUTIF @isExists = 1BEGIN 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'ENDELSEBEGINprint 'Departments_1.XML doesn''t exist'ENDHere's the XML example:<?xml version="1.0" encoding="UTF-8"?>-<Departments> -<Department> <DepartmentName>MAGNUM POC</DepartmentName> <CostCentreCode>30</CostCentreCode> </Department> </Departments>Thanks Teee |
| Posted: 22 Aug 2013 04:54 AM PDT Hello all,Is there a way to determine who deleted a view?Thanks |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server Newbies To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment