OPENXML vs OPENROWSET and xml data type

Question

Why would you ever use OPENXML instead of OPENROWSET (BULK 'file', SINGLE_BLOB) and cast to the xml data type?

Answer 1

I think those are quite different things you're comparing here:

• OPENROWSET just simply imports a file on disk as a single blob - this works great if you do need that file content as a single big chunk of information - if you want to store the entire file contents as a single XML field

• OPENXML is very different - it will open and interpret the XML file and will turn it into a rowset - it will give you rows and columns of data, based off that source XML file, and you can then store that information in a table - in rows and columns. You're not getting the XML as a single big chunk, but it's been "shredded" into rows and columns for you

So it really depends on what your needs are - if you need the XML as a big chunk and store it that was - use OPENROWSET.

If you need the data represented in the XML as columns and rows, use OPENXML (or alternatively: use the OPENROWSET first to import the XML as a whole, and then use the XQuery functions in SQL Server 2005 and up to shred it once you've loaded it)