Using OPENXML to parse IIS MetaBase.xml

I'm trying to get a list of the web sites on an IIS 6 server into a SQL-Server table. I know I can get it with WMI etc but that would require an additional service to be running on the box, whereas I should be able to find the info by getting a proc to parse the MetaBase.xml file using OPENXML.

For those of you not familiar with MetaBase.xml, the relevant sections look a bit like the following:

<configuration xmlns="urn:microsoft-catalog:XML_Metabase_V64_0">
  <MBProperty>
    <IIsWebDirectory Location="/LM/W3SVC/1/ROOT/MySite1" AppFriendlyName="MySite1" AppIsolated="2" AppPoolId="MySite1" AppRoot="/LM/W3SVC/1/ROOT/MySite1" DontLog="TRUE">     
    </IIsWebDirectory>
    <IIsWebDirectory Location="/LM/W3SVC/1/ROOT/MySite2" AppFriendlyName="MySite2" AppIsolated="2" AppPoolId="MySite2" AppRoot="/LM/W3SVC/1/ROOT/MySite2" DontLog="TRUE">     
    </IIsWebDirectory>
  开发者_StackOverflow中文版</MBProperty>
</configuration>

I'm using the following SQL to try parse it:

DECLARE @XMLPath VARCHAR(MAX)
SELECT @XMLPath = 'C:\Temp\MetaBase.xml'

DECLARE @RawXML XML, @sql NVARCHAR(4000), @params NVARCHAR(4000), @handle INT
SELECT @sql = N'SELECT @res = (SELECT * FROM OPENROWSET (BULK '''+ @XMLPath +''', SINGLE_BLOB)x)'
SELECT @params = N'@res XML OUTPUT'
EXEC sp_executesql @sql, @params, @res = @RawXML OUTPUT

SELECT @RawXML

EXEC sp_xml_preparedocument @handle OUTPUT, @RawXML
SELECT * 
FROM OPENXML(@handle, 'MBProperty/IISWebDirectory', 3) WITH (AppFriendlyName VARCHAR(800), Location VARCHAR(800), AppRoot VARCHAR(800), AppPoolId VARCHAR(800), DefaultDoc VARCHAR(800))
EXEC sp_xml_removedocument @handle

I'm seeing the XML correctly loaded into @RawXML but get nothing from the OPENXML query. I'm guessing its something to do with the path perhaps but have tried a number of combinations (e.g. 'configuration/MBProperty/IISWebDirectory') to no avail.

You need to add configuration to the path and change IISWebDirectory to IIsWebDirectory since XML is case sensitive and you need to add the xpath_namespaces parameter.

EXEC sp_xml_preparedocument @handle OUTPUT, @RawXML, '<root xmlns:ns="urn:microsoft-catalog:XML_Metabase_V64_0" />'

SELECT * 
FROM OPENXML(@handle, 'ns:configuration/ns:MBProperty/ns:IIsWebDirectory', 3) WITH
  (AppFriendlyName VARCHAR(800),
   Location VARCHAR(800),
   AppRoot VARCHAR(800),
   AppPoolId VARCHAR(800),
   DefaultDoc VARCHAR(800))
EXEC sp_xml_removedocument @handle

Or you could query the XML variable directly.

;with xmlnamespaces ('urn:microsoft-catalog:XML_Metabase_V64_0' as ns)
select
  n.i.value('@AppFriendlyName', 'varchar(800)') as AppFriendlyName,
  n.i.value('@Location', 'varchar(800)') as Location,
  n.i.value('@AppRoot', 'varchar(800)') as AppRoot,
  n.i.value('@AppPoolId', 'varchar(800)') as AppPoolId,
  n.i.value('@DefaultDoc', 'varchar(800)') as DefaultDoc
from @RawXML.nodes('ns:configuration/ns:MBProperty/ns:IIsWebDirectory') as n(i)

I am going to play devils advocate here and suggest that this is the wrong approach.

The metabase file is not always up to date. IIS caches metabase updates and flushes them to disk periodically. I've experienced delays of up to 5 minutes between changing a property using ADSI or WMI and it being flushed to disk when the server is under load.

You say that you'd need an extra service to be running on the machine to gather this data. Surely you still need some kind of process to be gathering this data from the metabase file anyway?

Another very important reason why this is also a bad idea is that when you parse this file you will need to resolve inherited properties yourself. One of the major technical design aspects of the IIS6 metabase is property inheritance.

For example, if you're collecting Script Maps then this is an inherited property. Unless you've explicitly set the ScriptMap or perhaps the DefaultDoc (or some other inherited property) on a site, virtual directory or application then you'll need to walk back up the metabase tree to find this value.

That's making a rod for your own back. You should use the API's (ADSI, System.DirectoryServices [the API that is the path of least resistance] or WMI) to query the metabase rather than read this file directly. Trust me, I've been here before.