Xml insert

Other SQL Server Topics
1

Hi all,

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.

  1. 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);

please help
Dhiraj

2

when you say, " I don't be use" do you mean:

  1. I am not using
  2. I don't want to use
3

you can use SSIS or powershell to loop through the source folder and ingest?

4

I don't want to use

5

I don't want to use

6

ssis is the way i'd do it

7

In Above code I don't Want use Openrowswet(BULK 'FilePath') because i do not give the permission for bulk so please help.
also SSIS is not solution

8

why is ssis not a solution? Why don't you want to use BULK as in the example?

9

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.

Why is SSIS not a solution?

1 Like
10

i have not give the privileges for Bulk because of that second way search

11

i have use stored procedure and this procedure i give parameter as file name or full file path.
ex.

  1. exec Xmlinsert 'Filename',(File name)
  2. exec Xmlinsert 'E:\Filename' (With path)
12

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...

Again - why is SSIS not a solution for you?

13

Sir then can I use another server path give the bulk statement if give the another server path location then give me syntax please

14

please use ssis for this. that's what it's designed for