Can OPENROWSET parameters be built dynamically? This feels like an ‘obvious “yes” ‘ kind of question, but nevertheless: would it not be possible with OPENROWSET to have sub-queries regarding the file path that import for particular procedurally-named files, such as: [Bus] + ‘-‘ + [School] + ‘-‘ + [Time (AM/MD/PM)] + ‘.xml’ ?

So for...

CREATE TABLE Route (Bus (int, 4), School (string, 4), Time (string, 2) )
INSERT INTO Route (0804,CRES,PM),(0758,CRES, MD),(1106,LHS,MD),(0804,CHMS,PM)
GO

CREATE TABLE Excel (Name (char,16))
INSERT INTO Excel (0804-CRES-PM.xml,0758-BVES-AM.xml,1106-LHS-MD.xml)
GO

UPDATE SeatingChart
FROM OPENROWSET (...’[Bus] + ‘-‘ [School] + ‘-‘ + [Route] + ‘.xml’
SET [Route]Seat = Excel.[Route]Seat
WHERE Name = [Bus] + ‘-‘ + [School] + ‘-‘ + [Time] + ‘.xml’


The file being imported from has columns for both the AM &amp; PM seating charts in the same spreadsheet, so I’m not sure know how to about writing the generalized statement that will input into the
You could in theory do this, but it would require building the update statement with dynamic SQL. Basically, you declare a string variable, build it with metadata from the database, and then execute that string. Erland Sommarskog has an epic post on this: The Curse and Blessings of Dynamic SQL. 

However, that's gonna require a lot of work and a lot of debugging.

Plus, for performance reasons, you don't really wanna fire a new OPENROWSET for every row of a table you're updating. 

Instead, I'd suggest doing a single OPENROWSET call to pull all of the file contents into a temp table, and then doing your update statement from the temp table.