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:
Open PowerShell ISE as an administrator.
Import the SQLPS module as follows:
...