Inserting XML into SQL Server

In this recipe, we will insert the content of some XML files into a SQL Server table that has XML columns.

Getting ready

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.

How to do it...

These are the steps required to insert the contents of XML files into SQL Server:

1. Open PowerShell ISE as an administrator.
2. Import the SQLPS module as...