SQL Server 2014 with PowerShell v5 Cookbook

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, ...