After introducing us to OPENROWSET, Dave Mason is now digging deeper into the topic, looking at data and format files:
Let’s take a closer look at the format file 1 There is a single <RECORD>element with multiple <FIELD> elements that correspond to the fields in the data file. There is also a single <ROW> element with multiple <COLUMN> elements that correspond to table columns. Note the xsi:type attributes that specify the SQL data types for the columns of the returned rowset.
I’ve never had great luck with OPENROWSET reading files and tend to reach for SSIS, but I think part of that is I’d never seen as clear an example as Dave’s.