By: Douglas Correa | Updated: 2016-03-01 | Comments (3) | Related: More > Import and Export
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):
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.
About the author
Douglas Correa is a database professional, focused on tuning, high-availability and infrastructure.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2016-03-01
