Import Active Directory Data to a SQL Server Table

Problem

Active Directory has a lot of great information that can be used for other systems. In a previous tip we looked at how to export this data using PowerShell and in this tip we will look at how to load the XML data into a SQL Server table.

Solution

As you can see below, the XML data has a lot of information that we don’t really need, so we will look at how to load the data and extract just the parts that are of value.

Before importing the XML file, create a table to receive the XML data.

CREATE TABLE XMLTable(
Id INT IDENTITY PRIMARY KEY,
XMLData XML,
LoadedDateTime DATETIME
);
GO

Next, load the XML file to the table using OpenRowSet. The path can be a network path like \\server\folder\xmlfile.xml, but the user needs permission to access that folder. Here we will load the entire file to the XML column in the table.

INSERT INTO XMLTable(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() 
FROM OPENROWSET(
BULK 'c:\temp\AD1.xml', 
SINGLE_BLOB) AS x;

The below statements allows us to select the columns we want, use an XML variable to get the columns for each node. In this case the XMLNAMESPACES is necessary.

DECLARE @XML AS XML
SELECT @XML = XMLData FROM XMLwithOpenXML
;WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/powershell/2004/04')
SELECT 
   City = Props.value('S[@N="City"][1]', 'varchar(100)'),
   CanonicalName = Props.value('S[@N="CanonicalName"][1]','nvarchar(200)'),
   Company = Props.value('S[@N="Company"][1]', 'nvarchar(200)'),
   Country = Props.value('S[@N="Country"][1]', 'nvarchar(200)'),
   Created = Props.value('DT[@N="Created"][1]', 'datetimeoffset(7)'),
   CreateTimeStamp = Props.value('DT[@N="createTimeStamp"][1]', 'datetimeoffset(7)'),
   Department = Props.value('S[@N="Department"][1]', 'varchar(100)'),
   DisplayName = Props.value('S[@N="DisplayName"][1]', 'nvarchar(200)'),
   DistinguishedName = Props.value('S[@N="DistinguishedName"][1]', 'nvarchar(MAX)'),
   [Enabled] = Props.value('B[@N="Enabled"][1]', 'nvarchar(MAX)'),
   Fax = Props.value('S[@N="Fax"][1]', 'nvarchar(20)'),
   GivenName = Props.value('S[@N="GivenName"][1]', 'nvarchar(MAX)'),
   ipPhone = Props.value('S[@N="ipPhone"][1]', 'nvarchar(MAX)'),
   LastBadPasswordAttempt = Props.value('DT[@N="LastBadPasswordAttempt"][1]', 'datetimeoffset(7)'),
   LastLogonDate = Props.value('DT[@N="LastLogonDate"][1]', 'datetimeoffset(7)'),
   LockedOut = Props.value('B[@N="LockedOut"][1]', 'nvarchar(20)'),
   logonCount = Props.value('I32[@N="logonCount"][1]', 'int'),
   Manager = Props.value('S[@N="Manager"][1]', 'nvarchar(MAX)'),
   Name = Props.value('S[@N="Name"][1]', 'nvarchar(MAX)'),
   [Owner] = Props.value('S[@N="Owner"][1]', 'nvarchar(MAX)'),
   [Group] = Props.value('S[@N="Group"][1]', 'nvarchar(MAX)'),
   ObjectClass = Props.value('S[@N="ObjectClass"][1]', 'nvarchar(MAX)'),
   ObjectGUID = Props.value('G[@N="ObjectGUID"][1]', 'nvarchar(MAX)'),
   OfficePhone = Props.value('S[@N="OfficePhone"][1]', 'nvarchar(MAX)'),
   PasswordExpired = Props.value('B[@N="PasswordExpired"][1]', 'nvarchar(20)'),
   PasswordLastSet = Props.value('DT[@N="PasswordLastSet"][1]', 'datetimeoffset(7)'),
   PasswordNeverExpires = Props.value('B[@N="PasswordNeverExpires"][1]', 'nvarchar(20)'),
   PasswordNotRequired = Props.value('B[@N="PasswordNotRequired"][1]', 'nvarchar(20)'),
   PostalCode = Props.value('S[@N="PostalCode"][1]', 'nvarchar(MAX)'),
   SamAccountName = Props.value('S[@N="SamAccountName"][1]', 'nvarchar(MAX)'),
   [State] = Props.value('S[@N="State"][1]', 'nvarchar(MAX)'),
   StreetAddress = Props.value('S[@N="StreetAddress"][1]', 'nvarchar(MAX)'),
   st = Props.value('S[@N="st"][1]', 'nvarchar(MAX)'),
   Surname = Props.value('S[@N="Surname"][1]', 'nvarchar(MAX)'),
   telephoneNumber = Props.value('S[@N="telephoneNumber"][1]', 'nvarchar(MAX)'),
   Title = Props.value('S[@N="Title"][1]', 'nvarchar(MAX)'),
   UserPrincipalName = Props.value('S[@N="UserPrincipalName"][1]', 'nvarchar(200)'),
   whenChanged = Props.value('DT[@N="whenChanged"][1]', 'datetimeoffset(7)'),
   whenCreated = Props.value('DT[@N="whenCreated"][1]', 'datetimeoffset(7)'),
   LastBadPasswordAttempt = Props.value('DT[@N="LastBadPasswordAttempt"][1]', 'datetimeoffset(7)')
FROM
   @XML.nodes('Objs/Obj/Props') AS XTbl(Props)

The result set (the output below was split to show you some of the columns):

Result

Now let’s create a table where this data can be stored.

CREATE TABLE tbADInformation (
   ID int identity primary key,
   City varchar(100),
   CanonicalName nvarchar(200),
   Company nvarchar(200),
   Country nvarchar(200),
   Created datetimeoffset(7),
   CreateTimeStamp datetimeoffset(7),
   Department varchar(100),
   DisplayName nvarchar(200),
   DistinguishedName nvarchar(1000),
   [Enabled] nvarchar(50),
   Fax nvarchar(20),
   GivenName nvarchar(500),
   ipPhone nvarchar(100),
   LastBadPasswordAttempt datetimeoffset(7),
   LastLogonDate datetimeoffset(7),
   LockedOut nvarchar(20),
   logonCount int,
   Manager nvarchar(1000),
   Name nvarchar(300),
   [Owner] nvarchar(100),
   [Group] nvarchar(100),
   ObjectClass nvarchar(200),
   ObjectGUID nvarchar(200),
   OfficePhone nvarchar(100),
   PasswordExpired nvarchar(20),
   PasswordLastSet datetimeoffset(7),
   PasswordNeverExpires nvarchar(20),
   PasswordNotRequired nvarchar(20),
   PostalCode nvarchar(100),
   SamAccountName nvarchar(MAX),
   [State] nvarchar(100),
   StreetAddress nvarchar(1000),
   st nvarchar(100),
   Surname nvarchar(200),
   telephoneNumber nvarchar(100),
   Title nvarchar(100),
   UserPrincipalName nvarchar(200),
   whenChanged datetimeoffset(7),
   whenCreated datetimeoffset(7)
)

Then we will insert the result into the table.

DECLARE @XML AS XML
SELECT @XML = XMLData FROM XMLwithOpenXML
;WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/powershell/2004/04')
INSERT INTO tbADInformation(
City ,
CanonicalName ,
Company ,
Country ,
Created ,
CreateTimeStamp ,
Department ,
DisplayName ,
DistinguishedName ,
[Enabled] ,
Fax ,
GivenName ,
ipPhone ,
LastBadPasswordAttempt ,
LastLogonDate ,
LockedOut ,
logonCount ,
Manager ,
Name ,
[Owner] ,
[Group] ,
ObjectClass ,
ObjectGUID ,
OfficePhone ,
PasswordExpired ,
PasswordLastSet ,
PasswordNeverExpires ,
PasswordNotRequired ,
PostalCode ,
SamAccountName ,
[State] ,
StreetAddress ,
st ,
Surname ,
telephoneNumber ,
Title ,
UserPrincipalName ,
whenChanged ,
whenCreated 
)
SELECT 
City = Props.value('S[@N="City"][1]', 'varchar(100)'),
CanonicalName = Props.value('S[@N="CanonicalName"][1]','nvarchar(200)'),
Company = Props.value('S[@N="Company"][1]', 'nvarchar(200)'),
Country = Props.value('S[@N="Country"][1]', 'nvarchar(200)'),
Created = Props.value('DT[@N="Created"][1]', 'datetimeoffset(7)'),
CreateTimeStamp = Props.value('DT[@N="createTimeStamp"][1]', 'datetimeoffset(7)'),
Department = Props.value('S[@N="Department"][1]', 'varchar(100)'),
DisplayName = Props.value('S[@N="DisplayName"][1]', 'nvarchar(200)'),
DistinguishedName = Props.value('S[@N="DistinguishedName"][1]', 'nvarchar(MAX)'),
[Enabled] = Props.value('B[@N="Enabled"][1]', 'nvarchar(MAX)'),
Fax = Props.value('S[@N="Fax"][1]', 'nvarchar(20)'),
GivenName = Props.value('S[@N="GivenName"][1]', 'nvarchar(MAX)'),
ipPhone = Props.value('S[@N="ipPhone"][1]', 'nvarchar(MAX)'),
LastBadPasswordAttempt = Props.value('DT[@N="LastBadPasswordAttempt"][1]', 'datetimeoffset(7)'),
LastLogonDate = Props.value('DT[@N="LastLogonDate"][1]', 'datetimeoffset(7)'),
LockedOut = Props.value('B[@N="LockedOut"][1]', 'nvarchar(20)'),
logonCount = Props.value('I32[@N="logonCount"][1]', 'int'),
Manager = Props.value('S[@N="Manager"][1]', 'nvarchar(MAX)'),
Name = Props.value('S[@N="Name"][1]', 'nvarchar(300)'),
[Owner] = Props.value('S[@N="Owner"][1]', 'nvarchar(100)'),
[Group] = Props.value('S[@N="Group"][1]', 'nvarchar(100)'),
ObjectClass = Props.value('S[@N="ObjectClass"][1]', 'nvarchar(MAX)'),
ObjectGUID = Props.value('G[@N="ObjectGUID"][1]', 'nvarchar(MAX)'),
OfficePhone = Props.value('S[@N="OfficePhone"][1]', 'nvarchar(MAX)'),
PasswordExpired = Props.value('B[@N="PasswordExpired"][1]', 'nvarchar(20)'),
PasswordLastSet = Props.value('DT[@N="PasswordLastSet"][1]', 'datetimeoffset(7)'),
PasswordNeverExpires = Props.value('B[@N="PasswordNeverExpires"][1]', 'nvarchar(20)'),
PasswordNotRequired = Props.value('B[@N="PasswordNotRequired"][1]', 'nvarchar(20)'),
PostalCode = Props.value('S[@N="PostalCode"][1]', 'nvarchar(MAX)'),
SamAccountName = Props.value('S[@N="SamAccountName"][1]', 'nvarchar(MAX)'),
[State] = Props.value('S[@N="State"][1]', 'nvarchar(MAX)'),
StreetAddress = Props.value('S[@N="StreetAddress"][1]', 'nvarchar(MAX)'),
st = Props.value('S[@N="st"][1]', 'nvarchar(MAX)'),
Surname = Props.value('S[@N="Surname"][1]', 'nvarchar(MAX)'),
telephoneNumber = Props.value('S[@N="telephoneNumber"][1]', 'nvarchar(MAX)'),
Title = Props.value('S[@N="Title"][1]', 'nvarchar(MAX)'),
UserPrincipalName = Props.value('S[@N="UserPrincipalName"][1]', 'nvarchar(200)'),
whenChanged = Props.value('DT[@N="whenChanged"][1]', 'datetimeoffset(7)'),
whenCreated = Props.value('DT[@N="whenCreated"][1]', 'datetimeoffset(7)')
FROM
@XML.nodes('Objs/Obj/Props') AS XTbl(Props)

There you have it. We now have the exported Active Directory data in a SQL Server table.

Next Steps

Read the previous tip Using PowerShell to export Active Directory information.
Read this to learn about all of the Active Directory attributes.
Here is more info about xmlnamespaces.
Also, here is additional info about using the nodes() method against a variable of XML type.

Douglas Correa

Douglas Correa is a database professional focused on performance tuning database, high-availability, and infrastructure. He has been working with SQL Server for more than ten years. He has performance-tuned databases ranging from 1 gigabyte to terabytes working with PowerShell, SQL Server and Oracle. He has certifications from Microsoft (MCSA SQL Server, MTA Network) and Oracle (OCE, OCA 11g).

Leave a ReplyCancel Reply