Date: 04/15/05 (SQL Server)    Keywords: xml, sql

I have an XML document which contains 10,000 rows of item master information, 12 columns per row. I want to read that XML document and use it in a SELECT statement, just as if it was a table or view.

The SQL help file suggests using OPENXML, which looks pretty simple, but in order to use that, I need to create an internal representation of the XML document with sp_xml_preparedocument.
In order to do THAT, I have to have the entire XML document in a text parameter. This is not a small file (4.8MB), and is representing about 10,000 rows of data. No way in hell can I pull all of that into an ntext variable.

I have never worked with XML in SQL Server before. I am familiar with the data in the document (.XML file contains the schema information in the header) but not with the T-SQL functions and procedues used to manipulate XML data.

How do I read the XML file, either as a part of a SQL SELECT, or by dumping it into a temporary cursor?

Source: http://www.livejournal.com/community/sqlserver/24405.html