SQL Query across Tables and XML Files Concurrently

I am attempting to execute a query on an internal database which will also pull in values from XMLs that are automatically generated by a web application. The query without the XML request works just as one would expect. I've pasted the query script at the bottom of this post. The issue is that I am unable to figure out a way to look at these XML files.

They have the following characteristics:

• They will always be in the same directory


• The names will always have the same convention which is
  
  Order.99999999.ItemData.88888888.xml where the 9s and 8s are random numerical strings


• The data structures are very similar across the board but NOT the exact same all of the time

The fields I need are always the same names. I want to match a field from the XML that looks like <StorefrontOrderId xmlns="">52264968</StorefrontOrderId> to the Storefront_Order_ID field in the dbo.StorefrontLog and then pull the following two fields from the XML, <letterCustomization>yes</letterCustomization> and <coverCustomization>yes</coverCustomization>.

    SELECT     StorefrontLog.Job_Number, StorefrontLog.User_Name, StorefrontProjectConfig.Project_Name, StorefrontLog.User_Email, StorefrontLog.Input_Qty, StorefrontLog.Ship_Name, 
                  StorefrontLog.Ship_Care_Of, StorefrontLog.Ship_Add_1, StorefrontLog.Ship_Add_2, StorefrontLog.Ship_City, StorefrontLog.Ship_State, StorefrontLog.Ship_Zip, 
                  StorefrontLog.Region_Code, StorefrontLog.Start_Time, StorefrontProjectConfig.DR_Inventory_NameFROM         StorefrontLog INNER JOIN
                  StorefrontProjectConfig ON StorefrontLog.Project_Name = StorefrontProjectConfig.Project_Name
WHERE     (StorefrontProjectConfig.DR_Inventory_Name = 'samplevalue1') and (StorefrontLog.User_Email not like '%samplevalue2%')
ORDER BY CONVERT(datetime,StorefrontLog.Start_Time, 121)

Software Details:

• Windows Server 2008 R2 Standard Service Pack 1 64-bit


• Microsoft SQLServer 2012