Avoiding Setting xp_cmdshell in SQL 2005

Would anyone know, how to work around a potential problem of not using xp_cmdshell.

For our users, We are using xp_cmdshell to look up the xml files in a particular folder and process them into Database. xp_cmdshell is used to read the contents of the file and build up the string for the stored procedure parameter input.

In SQL 2000, xp_cmdshell was set by default. in SQL 2005 not set by default and some of my users doesn't want to set it suggesting security concerns.

Thanks.

You could use OPENROWSET with BULK option for upload xml file as sigle blob into one database field. For example:

Code Snippet

INSERT INTO myTable(Document)
SELECT * FROM OPENROWSET(BULK N'C:\data.xml', SINGLE_BLOB) AS Document

Then you could use xml data type functions for split data. See xml.value, xml.nodes in BOL