My problem is i have insert .xml file into table without using Bulk statement
how to solve this problem and this file is save specific path how to get this file
and append on table data.
This code execute Right but i dont be use Bulk Statement
INSERT INTO Products (sku, product_desc)
SELECT X.product.query('SKU').value('.', 'INT'),
X.product.query('Desc').value('.', 'VARCHAR(30)')
FROM ( SELECT CAST(x AS XML) FROM OPENROWSET( BULK 'E:\Products+.xml',
SINGLE_BLOB) AS T(x) ) AS T(x)
CROSS APPLY x.nodes('Products/Product') AS X(product);
If you cannot get permissions for bulk insert - then you have to utilize another option. I would utilize SSIS as that is what it is designed for and works quite well...
Another option would be BCP - but automating that when you are not able to get bulk insert permissions may not be possible as you would have to be able to automate it from outside SQL Server.
This doesn't really answer the question - does this procedure work and if so why are you looking to change it? If you are saying that you want to build a stored procedure like the above - then you would have to use BULK INSERT or OPENROWSET or XP_CMDSHELL...