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 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:\XML Files\ 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 to insert the contents of XML files into SQL Server:

1. Open the PowerShell console application by going to Start | Accessories | Windows PowerShell...