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

Avoiding Empty Tags using XML Explicit..

Hey all, a nice little challenge for you as I've searched all over..

Here is some very simple XML Explicit code:

SELECT 1 AS TAG,
NULL AS Parent,
product AS [ParentTag!1!AProduct!Element]

FROM tblTable

WHERE X = Y

FOR XML EXPLICIT

When there is no results, I get an empty tag.

<ParentTag/>

This I do not want. Insted I would like nothing. Is there a simple way to achieve this?

Using SQL Server 2000

Looking forward to your responses!

Roqs

Dunno if its just my SQL Server but mine is working fine. I get a blank when no data is returned (Not the <ParentTag/>)

Tried this with northwind.

SELECT 1 AS TAG,

NULL AS Parent,

CompanyName AS [ParentTag!1!AProduct!Element]

FROM Customers

WHERE CustomerID = 'LFKI'

FOR XML EXPLICIT

Autonumbering the Records from XML

Hi,

I have a table "Del_Table", which must contain a column with autoincrement ability. I have added one column named as "Auto" and put it as Primary key, Identity and Integer. I am grabbing the data from an XML file. I have tried with the following Stored Procedure, but it gives an error as "An explicit value for the identity column in Del_Table can only be specified when a column list is used and IDENTITY_INSERT is ON". I have tried to to turn on IDENTITY_INSERT but still it does not work.

Code Snippet

CREATE PROCEDURE insertForecast
(@.OrderDoc ntext)
AS
DECLARE

@.hDoc int

EXEC sp_xml_preparedocument
@.hDoc output,
@.OrderDoc

INSERT INTO [Del_Table]
SELECT *
from OPENXML (@.hDoc,'Data/Delivery_Forecast/Forecast',3)
with
(
[ID] numeric '@.mp:id',
Document_Number char(35) '@.Document_Number',
Com_Date char(35) '@.Com_Date',
Code char (35) '@.code',
Port char (35) '@.port',
EPort char (35) '@.eport',
Number char (35) '@.number',
Instruction_Code char (35) '@.inst_Code',
Delivery_Date char (35) '@.del_date',
Quantity char (35) '@.quantity',
Status_Indicator char (35) '@.status_indicator'
)
ORDER BY
Document_Number

EXEC sp_xml_removedocument @.hDoc
GO

Any suggestions?

Thanks...

Specify the columns in the INSERT statement like the error message is stating (and still use the IDENTITY_INSERT of course).

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com