Post reply

Opening XML file from SQL Server 2005

  • November 14, 2006 at 9:44 am

    #174948

    I am creating a stored procedure that accepts XML and inserts its values into database tables. For now, I open XML file and pass its contents as a parameter to S.P. What I need to do is to have this stored proc read XML file directly from a disk, so instead of passing XML I will pass only file name for it.

    Is it possible to do ?

    Thanks

     

  • November 15, 2006 at 6:35 am

    #671926

    Yes, it's possible. You can use OPENROWSET to load any file into database. For xml file, the following is an example:

     SET NOCOUNT ON

    DECLARE @xml xml

    SELECT @xml=CAST(BulkColumn as xml) FROM OPENROWSET(BULK N'C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Package Samples\ProcessXMLData Sample\DataFiles\Orders.xml', SINGLE_BLOB) A

    Now the file Orders.xml is loaded into the variable @xml. Youcan use xquery or OPENXML to handle it, or save it to a table.

     

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply