Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Import Active Directory Data to a SQL Server Table


By:   |   Read Comments (3)   |   Related Tips: More > Import and Export

ALERT: Did you know 66% of DBAs say their workload is increasing! - Click here to learn more.


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.

XML

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

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


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Douglas Correa Douglas Correa is a database professional, focused on tuning, high-availability and infrastructure.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, March 08, 2016 - 8:54:31 PM - Tony Back To Top

 I find using SSIS and running this type of query to be easier (skip the PowerShell step). Simply import into a table. *caution: limited number of rows (1000?)

SELECT sAMAccountName, givenName, sn, employeeID
FROM OpenQuery(ADSI, 'SELECT sAMAccountName, givenName, sn, company, title, mail, streetaddress, l, st, postalCode, telephoneNumber, employeeID
  FROM ''LDAP://<ldap name>''
  WHERE objectClass=''user'' AND objectCategory = ''Person''
  AND sAMAccountType=805306368 ')

 


Tuesday, March 01, 2016 - 8:25:50 AM - Armandex Back To Top

 Hi,

 

Changed 

SELECT @XML = XMLData FROM XMLwithOpenXML

for 

SELECT @XML = XMLData FROM XMLTable

 

and now is running almos half hour

 

 


Tuesday, March 01, 2016 - 7:34:10 AM - Armandex Back To Top

 Hi,

Thanks for the post, when running the third script i get this error: 

 

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'OpenXML'.

 

I'm using sql 2012

Can you please help

 

thanks!

Armando


Learn more about SQL Server tools