XML Bulk Load

One of the first things that you’ll probably want to do to take advantage of the new XML data type is to load your XML documents into XML columns from disk. The new XML bulk load features make that task quite easy. This feature provides a high-speed mechanism for loading XML documents into SQL Server columns. You can see an example of using XML bulk load in the following listing:

INSERT into MyXMLDocs(MyXMLDoc) SELECT * FROM OPENROWSET    (Bulk 'c:\temp\MyXMLDoc.xml', SINGLE_CLOB) as x

In this example the INSERT statement is used to insert the results of the SELECT * FROM OPENROWSET statement into the MyXMLDoc column in the MyXMLDocs table. The OPENROWSET function uses the Bulk rowset provider for OPENROWSET to read data in from the file ‘C:\temp\MyXMLDoc.xml’. You can see the contents of the MyXMLDoc.xml file in the following listing:

<MyXMLDoc xmlns="http://MyXMLDocSchema">        <DocumentID>3</DocumentID>        <DocumentBody>"The Third Body"</DocumentBody> </MyXMLDoc>

If you execute this command from the SQL Server Management Studio, you need to remember that this will be executed on the SQL Server system, and therefore the file and path references must be found on the local server system. The SINGLE_CLOB argument specifies that the data from the file will be inserted into a single row. If you omit the SINGLE_CLOB argument, then the data from the file can be inserted into multiple rows. By default, the Bulk provider for the OPENROWSET function will split the rows on the Carriage Return character, which is the default row delimiter. Alternatively, you can specify the field and row delimiters using the optional FIELDTERMINATOR and ROWTERMINATOR arguments of the OPENROWSET function.