Bulk load XML file (SQL2005)

I have saved a dataset as xml on the disk. I want to bulk load the xml file
into a table in the SQL server 2005. I have checked out openrowset (t-sql)
but it seems to me that it can only load data in one column of type xml of a
table. I
dont want to use sp_xml_preparedocument to create an in memory
representation of the xml file.
I know I can use SQLXML in my code but is'nt the functionallity also in the
new System.Data.SqlXml namespace? I have found very little info about this.
Any ideas? Other solutions?
Regards Andreas :-)
PS! I use VS.NET with ADO.NET 2.0, C# and SQL Server 2005 CTP in my project.
Hi,
You can use SQLXMLBulkload component through Interop.
Here is a reference you can use to work from :
http://msdn.microsoft.com/library/de...us/dnanchor/ht
ml/anch_SQLXML.asp
Thanks,
Monica Frintu
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
|||System.Data.Sqlxml will not ship with Visual Studio.Net 2005. So it is not
recommended to use that.
Use SQLXML 3.0 native components through interop instead.
thanks
Chandra
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"AWHK" <awhk@.newsgroup.nospam> wrote in message
news:eNvvyJQcFHA.2696@.TK2MSFTNGP09.phx.gbl...
>I have saved a dataset as xml on the disk. I want to bulk load the xml file
> into a table in the SQL server 2005. I have checked out openrowset (t-sql)
> but it seems to me that it can only load data in one column of type xml of
> a table. I
> dont want to use sp_xml_preparedocument to create an in memory
> representation of the xml file.
> I know I can use SQLXML in my code but is'nt the functionallity also in
> the
> new System.Data.SqlXml namespace? I have found very little info about
> this.
> Any ideas? Other solutions?
>
> Regards Andreas :-)
>
> PS! I use VS.NET with ADO.NET 2.0, C# and SQL Server 2005 CTP in my
> project.
>
>
|||Here is a whitepaper that explains using SqlXml3 Bulkload in .Net:
http://msdn.microsoft.com/library/de...exchsqlxml.asp
--
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"AWHK" <awhk@.newsgroup.nospam> wrote in message
news:eNvvyJQcFHA.2696@.TK2MSFTNGP09.phx.gbl...
>I have saved a dataset as xml on the disk. I want to bulk load the xml file
> into a table in the SQL server 2005. I have checked out openrowset (t-sql)
> but it seems to me that it can only load data in one column of type xml of
> a table. I
> dont want to use sp_xml_preparedocument to create an in memory
> representation of the xml file.
> I know I can use SQLXML in my code but is'nt the functionallity also in
> the
> new System.Data.SqlXml namespace? I have found very little info about
> this.
> Any ideas? Other solutions?
>
> Regards Andreas :-)
>
> PS! I use VS.NET with ADO.NET 2.0, C# and SQL Server 2005 CTP in my
> project.
>
>

Bulk Insert -- Access denied issues - 2

Hi All

Same situation as described here , same issue.

SQL Server(SQL2005 on Windows2003) uses domain account. This domain account enabled to be trusted for delegation. Client connects to server using Windows auth. Client issues BULK INSERT with UNC path. Statement returns error:

Cannot bulk load because the file "\\Server\pub\file.txt" could not be opened. Operating system error code 5(Access is denied.)

SQL 2000 runs this statement successfully so statement and file are OK. Everyone has all permissions on network share. Domain account granted all permissions explicitly so there is no access troubles.

Audit show anonymous connections.

Question is - how to put delegation in work?

Thank you in advance,

Alexander Sinitsin

I don't think this is a problem with delegation. How does the client connect to SQL Server and issues the BULK INSERT? Is it SQL or Windows authentication? If it is Windows authentication then the service account credentials will not be used (change from SQL Server 2000) so the connected user / login needs to have the permissions to access the file. For SQL logins, the service account credentials will be used. So could you please describe your scenario?

1. What is the SQL Server service account?

2. Where is the data file located?

3. How does the user connect to SQL Server? SQL or Windows Auth?

4. Who has permissions on the data file? If the user connects using Windows auth then check for the login else check the service account.

|||

Hi Umachandar Jayachandran!

Thanks for your answer.

>How does the client connect to SQL Server and issues the BULK INSERT? Is it SQL or Windows authentication?

Client connected using Windows authentication.

>service account credentials will not be used (change from SQL Server 2000)

Sure. Server should use client's credential to access file.

>so the connected user / login needs to have the permissions to access the file.

Connected user has full permissions to access the file. Even more, this user is a file owner-creator.

>1. What is the SQL Server service account?

SQL Server run under domain account. This domain account enabled to be trusted for delegation.

>2. Where is the data file located?

Data file located on some PC in network. Not on SQL Server locally.

>3. How does the user connect to SQL Server? SQL or Windows Auth?

Windows Auth.

>4. Who has permissions on the data file? If the user connects using Windows auth then check for the login else check the service account.

Everybody has all permissions on the data file. Client connected has all permissions. Even SQL Server's domain account has all permissions. I can access this file under client's credentials without any problems.

As far as I can see, in this scenario SQL Server 2005 should use client credentials to access file. But when I run BULK INSERT, access audit show anonymous access, not client's access.

Best regards,

Alexander Sinitsin