Using T-SQL to read an XML feed directly into an XML field in a table?

Question

I see a LOT of resources about producing feeds from T-SQL2005, about parsing XML into a database schema, processing, bulk operations, etc. What I don't see is how to have, for example, a statement/s in a stored proc or function which can simply access a URL for an XML feed, load the XML into a table field or sproc variable and close the connection.

I understand it might be necessary to use an external layer, like a C# web app, but it would be great (considering all the other complex functions T-SQL provides) to just read a feed or file directly.

Answer 1

Nothing inbuilt that I'm aware of. It would be possible to do this through the Object Automation extended stored procedures but you would be better off using the CLR for this (if you have to do it within SQL Server at all - I feel an external app might be better).

Answer 2

You can use OPENROWSET if the file is on the same server, example

CREATE TABLE XmlImportTest
(
        xml_data xml
)
GO

INSERT INTO XmlImportTest(xml_data)
SELECT  xmlData 
FROM
(
    SELECT  * 
    FROM    OPENROWSET (BULK 'c:\TestXml.xml' , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)

If you want to import all of them from a folder, take a look at How to import a bunch of XML files from a directory in T-SQL

If you need to acces it from a URL then SSIS could do it if it is a web service or perhaps even SQLCLR