Untitled

Using OPENXML to parse IIS MetaBase.xml

<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>

  </MBProperty>

</configuration>

       

DECLARE @XMLPath VARCHAR(MAX)

SELECT @XMLPath = 'C:TempMetaBase.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

       

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

       

;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)