Share via

Not being able to read xml file by OPENROWSET

Sudip Bhatt 2,276 Reputation points
2020-11-27T11:37:30.357+00:00

This is my code. i have a xml file is in c drive but no way i am being able to read xml file from sql server using OPENROWSET permission.
DECLARE @XML XML
SELECT @XML = BulkColumn FROM OPENROWSET(BULK '\192.168.1.111\c$\test.xml', SINGLE_BLOB) x 

 --SELECT  
 --    t.c.value('(PersonId/PersonNr/text())[1]', 'VARCHAR(100)'),  
 --    t.c.value('(Namn/Tilltalsnamnsmarkering/text())[1]', 'INT')  
 --FROM @xml.nodes('*:ArrayOfFolkbokforingspostTYPE/*:FolkbokforingspostTYPE/*:Personpost') t(c)  
  
  
SELECT col.value('(Section/text())[1]', 'NVARCHAR(MAX)') AS Section  
 ,col.value('(LineItem/text())[1]', 'NVARCHAR(MAX)') AS LineItem     
 ,col.value('(XFundCode/text())[1]', 'NVARCHAR(MAX)') AS XFundCode  
 ,col.value('(StandardDate/text())[1]', 'NVARCHAR(MAX)') AS StandardDate  
 ,col.value('(StandardValue/text())[1]', 'VARCHAR(MAX)') AS StandardValue  
 ,col.value('(ActualProvidedByCompany/text())[1]', 'VARCHAR(MAX)') AS ActualProvidedByCompany  
FROM @xml.nodes('/Root/PeriodicalData') AS tab (col)

This line actually causing the issue
SELECT @XML = BulkColumn FROM OPENROWSET(BULK '\192.168.1.111\c$\test.xml', SINGLE_BLOB) x

Error message return from SSMS Bad or inaccessible location specified in external data source "(null)".

when i give simple path like SELECT @XML = BulkColumn FROM OPENROWSET(BULK 'c\test.xml', SINGLE_BLOB) x
then got different error message and it was Cannot bulk load. The file "c:\test.xml" does not exist or you don't have file access rights.

please tell me what right i need to give as a result i can read xml file from sql server. thanks

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions
0 comments
Accepted answer
  1. Olaf Helper 43,246 Reputation points
    2020-11-27T13:25:59.287+00:00

    '\192.168.1.102\c$\t

    C$ is a dedicated admin share and only admins can access this share; the SQL Server service account isn't an admin as I guess.
    Create a real share and grant access permissions for the SQL Server service account.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 107.2K Reputation points
    2020-11-27T14:23:31.6+00:00

    Some qualifications to Olaf's post. As long as you connect to SQL Server with Windows authentication, SQL Server will impersonate you when accessing the file share. That is, what matters are your own permission. Note that for it to work, Kerberos must be setup correctly, or else there will be errors.

    I would guess though that Olaf is correct on the administrative shares Work with real shares. And putting files directly under C:\ (or D:\ etc) is not a good idea.

    And to be clear: what matters here are the permissions in the file system, not SQL Server.

    0 comments