Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

OPENXML vs OPENROWSET and xml data type

Tags:

sql-server

xml

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

like image 786
Daniel Avatar asked Nov 28 '10 03:11

Daniel


1 Answers

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)

like image 152
marc_s Avatar answered Sep 30 '22 22:09

marc_s