Allow reading of multiple XMLfile names from folder using SQL code

openrowset sql sqlbulkcopy tsql xml

Question

I have a created a stored procedure that functions fine with a hard coded XML file name. However I cannot keep hard coding the file name as every time a new file comes in it will have a different file name although the XML structure remains the same.

I wondered if anyone could help/point me in the right direction. Here is my code that works with the hard coded xml file name. Now I need it to read all the XML files that exist in the C:\temp\ folder and not just one.

CREATE PROC [dbo].[XMLIputData] as 

;WITH XMLNAMESPACES(DEFAULT 'http://www.sxi.co.za/XMLSchema')

INSERT INTO FNBXMLData 
    (  
        [CIpriority],
        [ref_num],
        [summary],
        [act_log],
        [impact.sym],
        [category.sym],
        [affected_resource.name],
        [affected_resource.class.type],
        [status.sym],
        [customer.billing_code.name], 
        [affected_resource.model.sym],
        [customer.first_name],
        [customer.last_name],
        [customer.phone_number],
        [customer.beeper_phone] ,
        [customer.email_address] ,
        [location.name] ,
        [affected_resource.location.address1] ,
        [affected_resource.location.address6] ,
        [affected_resource.location.city.sym] ,
        [affected_resource.location.state.sym] ,
        [category.service_type.sys] ,
        [affected_resource.znr_inc_service_type.sym] ,
        [affected_resource.znr_cr_service_type.sym] ,
        [category.service.type.sym] ,
        [SXI_SigReg_ActionField] ,
        [SXI_SigReg_EntryIdField] ,
        [SXI_SigReg_EpochTimeField] ,
        [SXI_SigReg_UniqueIdField]) 
SELECT  
        X.product.query('CIpriority').value('.', 'varchar(5)'),
        X.product.query('ref_num').value('.', 'varchar(20)'),
        X.product.query('summary').value('.', 'varchar(200)'),
        X.product.query('act_log').value('.', 'varchar(200)'),
        X.product.query('impact.sym').value('.', 'varchar(200)'),
        X.product.query('category.sym').value('.', 'varchar(200)'),
        X.product.query('affected_resource.name').value('.', 'varchar(200)'),
        X.product.query('affected_resource.class.type').value('.', 'varchar(200)'),
        X.product.query('status.sym').value('.', 'varchar(50)'),
        X.product.query('customer.billing_code.name').value('.', 'varchar(20)'),
        X.product.query('affected_resource.model.sym').value('.', 'varchar(50)'),
        X.product.query('customer.first_name').value('.', 'varchar(20)'),
        X.product.query('customer.last_name').value('.', 'varchar(20)'),
        X.product.query('customer.phone_number').value('.', 'varchar(20)'),
        X.product.query('customer.beeper_phone').value('.', 'varchar(20)'),
        X.product.query('customer.email_address').value('.', 'varchar(200)'),
        X.product.query('location.name').value('.', 'varchar(200)'),
        X.product.query('affected_resource.location.address1').value('.', 'varchar(200)'),
        X.product.query('affected_resource.location.address6').value('.', 'varchar(200)'),
        X.product.query('affected_resource.location.city.sym').value('.', 'varchar(200)'),
        X.product.query('affected_resource.location.state.sym').value('.', 'varchar(200)'),
        X.product.query('category.service_type.sys').value('.', 'varchar(200)'),
        X.product.query('affected_resource.znr_inc_service_type.sym').value('.', 'varchar(200)'),
        X.product.query('affected_resource.znr_cr_service_type.sym').value('.', 'varchar(200)'),
        X.product.query('category.service.type.sym').value('.', 'varchar(200)'),
        X.product.query('SXI_SigReg_ActionField').value('.', 'varchar(200)'),
        X.product.query('SXI_SigReg_EntryIdField').value('.', 'varchar(200)'),
        X.product.query('SXI_SigReg_EpochTimeField').value('.', 'varchar(200)'),
        X.product.query('SXI_SigReg_UniqueIdField').value('.', 'varchar(200)')

FROM ( 
SELECT CAST(x AS XML)
FROM OPENROWSET(
     BULK 'C:\temp\X-ServiceBroker~FNBUSD-DIEBOLD~702012850747092.75.xml',
     SINGLE_BLOB) AS T(x)
     ) AS T(x)
CROSS APPLY x.nodes('XServiceBroker') AS X(product);
GO

Popular Answer

You might try it like this

--You need a table to insert into
CREATE TABLE dbo.XmlTarget(MyXML XML);

--this is the dynamically changing filename
DECLARE @FileName NVARCHAR(250) =N'X:\PathToFile\TestXml.xml';

--this is a dynamic command to allow a changing filename
DECLARE @cmd NVARCHAR(MAX)=
'INSERT INTO dbo.XmlTarget(MyXML)
 SELECT CAST(x AS XML)
 FROM OPENROWSET(
        BULK ''' +  @FileName + ''',SINGLE_BLOB) AS T(x);'

--execute the statement
EXEC (@cmd);

--Now the XML is in the table
SELECT * FROM dbo.XmlTarget;

--CleanUp
DROP TABLE dbo.XmlTarget;


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why