SQL Server has a handy option for loading XML data directly from the file system, the OPENROWSET function.  To load XML files into SQL Server, simply declare an xml data type variable and assign it the value of the OPENROWSET function with the BULK option.  Here's a quick example that uses the state-list.xml file (attached to this post, or available here):

DECLARE @xml XML;

-- Use OPENROWSET to read an XML file from the file system
SELECT @xml = BulkColumn
FROM OPENROWSET(BULK 'c:\state-list.xml', SINGLE_BLOB) TempXML

-- View the result
SELECT @xml;

The first OPENROWSET parameter is the filename to load, preceded by the BULK keyword.  The second parameter defines the type of the file.  Valid values are SINGLE_CLOB for character large object, SINGLE_NCLOB for Unicode character large objects, and SINGLE_BLOB for binary large object.  Microsoft recommends always setting this parameter to SINGLE_BLOB.  Also make sure that you alias the OPENROWSET (in this example I used "TempXML" as an alias).  The single column returned is named BulkColumn.

The only real caveats here are:

1. Make sure that your file actually exists where you tell it, or you'll obviously raise an exception,
2. Make sure your XML file is actually XML data if you're loading it into an xml data type variable, and
3. Make sure your SQL Server service account has access to the directory you want to read from.  On Windows Server installations the SQL Server service account may not have access to the root of the c: drive that I used in this example.

Another "little thing": the path to the filename is from the perspective of the server. So if you're logged into a remote server from a client computer, "C:\" is the root directory on the server not the client's local C: drive.

Also note that you cannot use a variable in place of the filename.  You have to give OPENROWSET a string literal.  To get around this, you can use dynamic SQL to construct your OPENROWSET function call if necessary.  **Always be cautious when using dynamic SQL!***

In the next post we'll discuss some xml data type-related functionality.