I will use OpenXML to import the data from XML file into database tables. The xml files are in FPT site. I need to read the data of that file into memory in Sql server. Then I can use following code to add data into table

How can I get the data in XML file in FTP site and put into variable or table in SQL?

Thanks

Jim

exec sp_Insert_Books_Openxml '<?xml version="1.0"?>
<Books>
      <Book>
            <Title>XML Application Development with MSXML 4.0</Title>
…….’

PROCEDURE sp_Insert_Books_Openxml
    @strXML ntext
AS
    DECLARE @iDoc int
    EXECUTE sp_xml_preparedocument @iDoc OUTPUT, @strXML

    INSERT INTO tblBooks (BookTitle, Publisher, DateOfPurchase)
      (SELECT * FROM OpenXML(@iDoc, '/Books/Book', 2)
                WITH 
               (Title VARCHAR(100), 
            Publisher VARCHAR(100),
            DateOfPurchase DateTime)
      )
    EXECUTE sp_xml_removedocument @iDoc