Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2002
    Location
    India
    Posts
    38

    Thumbs up Unanswered: Reading XML File SQL Server

    hi to all
    I have been trying to read XML file in SQL Server with Stored Procedure. but i coud't get any solution.

    can any one Help me.

    I have XML file with List Of Countryes each Country has Id.

    I need to read all Country Id,CountryName and need to be insert into DataBase or Return as String (output parameter).

    How do I achive this? please Help me.

    Cheers

    Buddu
    -- Prasad --

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    declare @objFSys int
    declare @objFile int
    declare @blnEndOfFile int
    declare @strLine varchar(4000)

    exec sp_OACreate 'Scripting.FileSystemObject', @objFSys out

    -- Change the file path to the one that is passed to your stored procedure
    exec sp_OAMethod @objFSys, 'OpenTextFile', @objFile out, 'C:\test.txt', 1
    exec sp_OAMethod @objFile, 'AtEndOfStream', @blnEndOfFile out
    while @blnEndOfFile=0 begin
    exec sp_OAMethod @objFile, 'ReadLine', @strLine out
    -- Here you got one line from the file
    select @strLine
    exec sp_OAMethod @objFile, 'AtEndOfStream', @blnEndOfFile out
    end
    exec sp_OADestroy @objFile
    exec sp_OADestroy @objFSys
    --------
    Change accordingly to your requirement.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •