SQL Server Performance

load data from xml file to MS SQL Server 2005

Discussion in 'SQL Server 2005 General Developer Questions' started by ashish.johri, Aug 10, 2007.

  1. ashish.johri New Member

    Hi,
    I am unsuccessfully trying to load data from xml file to MS SQL Server 2005 with the query as follows:DECLARE @xmlDoc XML
    SET
    @xmlDoc = ( SELECT * FROM OPENROWSET ( BULK 'C:Documents and SettingsajohriDesktopaa.xml', SINGLE_CLOB ) AS xmlData)SELECT
    @xmlDoc
    This gives me the error that Cannot bulk load because the file "C:Documents and SettingsajohriDesktopaa.xml" could not be opened. Operating system error code 3(The system cannot find the path specified.).Could sb tell me how to get this done?
  2. Madhivanan Moderator

    Make sure file is located at Server's directory and not at Client's directory
  3. satya Moderator

    As specified ensure path is correct and also ensure the relevant account has permissions to access that file. In general it is better to create a share with READ permission for EVERYONE group, where the file & directory can be accessed.
  4. ashish.johri New Member

    Hi the file is in the server's directory and also this is read only by everyone. I think that I have to do some OLEDB settings so that SQL Server can read this xml file.
    M I thinking in the right direction?
  5. satya Moderator

    True and this is what referred in MSDN forums:
    To use a SQL destination you need to run the package on the machine that hosts SQL Server (destination). If you want to use a remote SQL Server, use OLEDB destination.
  6. ashish.johri New Member

    I could not get the exact answer of my problem. Could you please refer me some url which describes about the loading of data from xml to MS SQL DB? Probably that would help me out of this mess. Thanks in advance.
  7. ashish.johri New Member

    Hi sir,
    <?xml version="1.0" encoding="UTF-8"?> was written in XML file instead of <?xml version="1.0" standalone="yes"?>.
    I am able to get the contents of xml file into sql server as it is while now I am asked to make one table with the schema same as xml file's. Now how to proceed? Pls let me know some URL for this.
  8. satya Moderator

  9. ashish.johri New Member

    Is there any limit on the no of characters or data in xml file that can be exported from xml to MS SQL Server?
  10. satya Moderator

  11. dineshasanka Moderator

    You can do this from SSIS. In SSIS you are having a component called XML File source. from that you can give the xml file and then you can give the designation as sql server database

Share This Page