In this recipe, we will insert the content of some XML files into a SQL Server table that has XML columns.
We will create a sample table that we can use for this recipe. Run the following script in SQL Server Management Studio to create a table named SampleXML
that has an XML field:
USE SampleDB GO IF OBJECT_ID('SampleXML') IS NOT NULL DROP TABLE SampleXML GO CREATE TABLE SampleXML ( ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, FileName VARCHAR(200) , InsertedDate DATETIME DEFAULT GETDATE() , InsertedBy VARCHAR(100) DEFAULT SUSER_SNAME() , XMLStuff XML , FileExtension VARCHAR(50) )
Create a directory called C:\DATA\
if it does not exist and copy the sample XML files that come with the book scripts. Alternatively, you can use your own directory and XML files.