The Real MTCS SQL Server 2008 Exam 70/432 Prep Kit- P74: Congratulations on your journey to become certified in SQL Server 2008. This book will help prepare you for your exam and give you a practical view of working with SQL Server 2008. | ETL Techniques Chapter 8 347 Using OPENROWSET BULK . OPENROWSET can also be used to BULK load data just like the BCP and BULK INSERT statements. We have already covered bulk loading in some detail so we will leave this topic up to your own research. One comment on using OPENROWSET as an alternative to BCP or BULK INSERT is that parsing the external data file property OPENROWSET BULK . requires that you use a format file. Format files are optional with BCP and BULK INSERT if the command arguments can describe the formatting of the file but OPENROWSET BULK . must have a format file. However using the BULK option of OPENROWSET does allow you to do something that you can t do with BCP and BULK INSERT read the entire external data file as a single value. This can be useful if you are trying to populate a large data type field varchar max nvarchar max varbinary max or xml with the contents of the file. You can do this by using the SINGLE_BLOG SINGLE_ CLOB and SINGLE_NCLOB options. When you use any of those options OPENROWSET reads the external file and returns it as a single column single row rowset in other words a single value that is either a large binary data type SINGLE_BLOG character value SINGLE_CLOB or Unicode character value SINGLE_NCLOB . Here is an example if you had an XML file with the following contents xml version Presidents President ID 1 FirstName George LastName Washington President ID 2 FirstName John LastName Adams President ID 3 FirstName Thomas LastName Jefferson Presidents And if you had a table with the following structure CREATE TABLE SomeXML RowID int IDENTITY 1 1 PRIMARY KEY TheXML xml You could use the OPENROWSET function to read the contents of the XML file into the column like this INSERT INTO SomeXML TheXML SELECT FROM OPENROWSET 348 Chapter 8 ETL Techniques BULK N C sample z SINGLE_BLOB AS XMLFile The following example shows a query of the SomeXML table and the results SELECT FROM SomeXML --Results RowID .
