CSExport -- Getting the ILM Connector Space into SQL

How can I query the ILM Connector Space?

"You can't." -- Yes you can.

"You have to WMI." --  But WMI is limited in what you can query and slow

"You have to use CSExport and then use XML tools." -- that works, but this may be better

The above are various answers you may receive. However, thanks to the power of SQLXML we can issue SQL queries against the ILM Connector Space (after it has been exported using CSExport).

The following code will only work on SQL 2005. Some of the SQLXML tricks involved here have been described previously

So I will discuss some of the new tricks.

The csexport file spits out XML that looks a lot like entity attribute value schema and this means that each attribute gets its own row when you bring it into SQL. Since that doesn't work well for me to write further queries I use a pivot operator to make the data relational.

The code below will retrieve data from the pending import holograms of an AD connectorspace and doesn't handle multi-valued attributes.

 

SET NOCOUNT ON

DECLARE @ADXML TABLE (ADID int identity (1,1), XMLFROMCS XML)

INSERT @ADXML (XMLFROMCS)

SELECT * 

FROM OPENROWSET(BULK 'C:\FuzzyLogic\ADExport\ADExport.xml',SINGLE_BLOB) AS AD

DECLARE @ADXMLData XML

SELECT @ADXMLData = XMLFROMCS

FROM @ADXML

DECLARE @docHandle int

EXEC sp_xml_preparedocument @docHandle OUTPUT, @ADXMLData

TRUNCATE TABLE dbo.adUsers 

INSERT dbo.adUsers (dn, [cn],[department],[displayname],[employeeid],[givenname],[mail],[sn],[title])

SELECT dn, [cn],[department],[displayname],[employeeid],[givenname],[mail],[sn],[title]

FROM

    (

            SELECT dn, attrname, attrvalue  

            FROM OPENXML(@docHandle, N'/cs-objects/cs-object/pending-import-hologram/entry/attr/value',2)  

             WITH 

                (dn nvarchar(450) '../../@dn'

                ,primaryobjectclass nvarchar(450) '../../primary-objectclass'

                ,attrname nvarchar(450) '../@name'

                ,attrvalue nvarchar(450) '.'

                ,multivalued nvarchar(450) '../@multivalued'

                ) adusers

            WHERE adusers.primaryobjectclass = 'user' AND adusers.multivalued = 'false'

                AND attrname in ('cn','department','displayname', 'employeeid', 'givenName', 'mail', 'sn','title') 

        ) AS ADList1

    PIVOT (MIN(attrvalue) FOR attrname in ([cn],[department],[displayname],[employeeid],[givenname],[mail],[sn],[title])

 )AS ADUserPivot

EXEC sp_xml_removedocument @docHandle