Geek Answers Handbook

OPENXML vs OPENROWSET and xml data type

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

+4
source share
1 answer

I think these are very different things that you are comparing here:

  • OPENROWSET simply imports the file to disk as a single blob - this works fine if you want the contents of the file to be one large amount of information - if you want to save the full contents of the file as one XML field

  • OPENXML is very different - it will open and interpret the XML file and turn it into a set of rows - it will provide you with rows and columns of data based on this source XML file, and you can save this information in a table - in rows and columns. You don't get XML as one big chunk, but it has been “shredded” into rows and columns for you.

So it really depends on your needs - if you need XML as a big chunk and save it, then use OPENROWSET .

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

+1
source

More articles:


All Articles