Book Image

SQL Server 2014 with PowerShell v5 Cookbook

By : Donabel Santos
Book Image

SQL Server 2014 with PowerShell v5 Cookbook

By: Donabel Santos

Overview of this book

Table of Contents (21 chapters)
SQL Server 2014 with PowerShell v5 Cookbook
Credits
About the Author
Acknowledgments
About the Reviewers
www.PacktPub.com
Preface
Index

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 follows:

    ...