none
Parsing a Visual Studio project file

    Question

  • Hi,

    T-SQL/XML challenge here for anyone that fancies it. I want to parse the contents of an XML file using T-SQL. Here is the (cut-down) XML file:

    <Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003" DefaultTargets="Build" ToolsVersion="4.0">
     <PropertyGroup>
      <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>
      <Name>dbMergermarket2002</Name>
     </PropertyGroup>
     <PropertyGroup Condition=" '$(Configuration)' == 'Release' ">
      <OutputPath>.\sql\release\</OutputPath>
     </PropertyGroup>
     <!--Import the settings-->
     <Import Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlTasks.targets" />
     <ItemGroup>
      <Folder Include="Scripts" />
     </ItemGroup>
     <ItemGroup>
      <PropertiesFile Include="Properties\Database.sqlsettings" />
     </ItemGroup>
     <ItemGroup>
      <Build Include="Schema Objects\Schemas\dbo\Programmability\Stored Procedures\Procs1\ap_ABS_selectAdminCompanySearchLimited.proc.sql">
       <SubType>Code</SubType>
       <SuppressWarnings>4151</SuppressWarnings>
      </Build>
      <Build Include="Schema Objects\Schemas\dbo\Programmability\Stored Procedures\Procs1\ap_ABS_SelectAllClassDetailsForTrustSeries.proc.sql">
       <SubType>Code</SubType>
      </Build>
      <Build Include="Schema Objects\Schemas\dbo\Programmability\Stored Procedures\Procs1\ap_FAILOVER_EVENTQUEUE_AppendQueue.proc.sql">
       <SubType>Code</SubType>
       <SuppressWarnings>4151</SuppressWarnings>
      </Build>
     </ItemGroup>
    </Project>


    (I've also posted in raw form below)

    What I basically want to do is return all of the <Build> nodes that have a <SuppressWarnings> node in them. For the example above then I simply need to return:

    Schema Objects\Schemas\dbo\Programmability\Stored Procedures\Procs1\ap_ABS_selectAdminCompanySearchLimited.proc.sql
    Schema Objects\Schemas\dbo\Programmability\Stored Procedures\Procs1\ap_ABS_SelectAllClassDetailsForTrustSeries.proc.sql

    So far I've come up with this but it hasn't gotten me very far:

    select	_nodes._node.query('.') 
    from	(
    		select	cast('<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003" DefaultTargets="Build" ToolsVersion="4.0">
     <PropertyGroup>
      <Configuration Condition=" ''$(Configuration)'' == '''' ">Debug</Configuration>
      <Name>dbMergermarket2002</Name>
     </PropertyGroup>
     <PropertyGroup Condition=" ''$(Configuration)'' == ''Release'' ">
      <OutputPath>.\sql\release\</OutputPath>
     </PropertyGroup>
     <!--Import the settings-->
     <Import Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlTasks.targets" />
     <ItemGroup>
      <Folder Include="Scripts" />
     </ItemGroup>
     <ItemGroup>
      <PropertiesFile Include="Properties\Database.sqlsettings" />
     </ItemGroup>
     <ItemGroup>
      <Build Include="Schema Objects\Schemas\dbo\Programmability\Stored Procedures\Procs1\ap_ABS_selectAdminCompanySearchLimited.proc.sql">
       <SubType>Code</SubType>
       <SuppressWarnings>4151</SuppressWarnings>
      </Build>
      <Build Include="Schema Objects\Schemas\dbo\Programmability\Stored Procedures\Procs1\ap_ABS_SelectAllClassDetailsForTrustSeries.proc.sql">
       <SubType>Code</SubType>
      </Build>
      <Build Include="Schema Objects\Schemas\dbo\Programmability\Stored Procedures\Procs1\ap_FAILOVER_EVENTQUEUE_AppendQueue.proc.sql">
       <SubType>Code</SubType>
       <SuppressWarnings>4151</SuppressWarnings>
      </Build>
     </ItemGroup>
    </Project>' as XML).query('/') x
    		)t
    CROSS  APPLY x.nodes('/') _nodes(_node)

    Anyone up to the challenge?

    Thanks
    Jamie

     

     

     

     

     

     

     

     

     

     

    <Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003" DefaultTargets="Build" ToolsVersion="4.0">
      <PropertyGroup>
        <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>
        <Name>dbMergermarket2002</Name>
      </PropertyGroup>
      <PropertyGroup Condition=" '$(Configuration)' == 'Release' ">
        <OutputPath>.\sql\release\</OutputPath>
      </PropertyGroup>
      <!--Import the settings-->
      <Import Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlTasks.targets" />
      <ItemGroup>
        <Folder Include="Scripts" />
      </ItemGroup>
      <ItemGroup>
        <PropertiesFile Include="Properties\Database.sqlsettings" />
      </ItemGroup>
      <ItemGroup>
        <Build Include="Schema Objects\Schemas\dbo\Programmability\Stored Procedures\Procs1\ap_ABS_selectAdminCompanySearchLimited.proc.sql">
          <SubType>Code</SubType>
          <SuppressWarnings>4151</SuppressWarnings>
        </Build>
        <Build Include="Schema Objects\Schemas\dbo\Programmability\Stored Procedures\Procs1\ap_ABS_SelectAllClassDetailsForTrustSeries.proc.sql">
          <SubType>Code</SubType>
        </Build>
        <Build Include="Schema Objects\Schemas\dbo\Programmability\Stored Procedures\Procs1\ap_FAILOVER_EVENTQUEUE_AppendQueue.proc.sql">
          <SubType>Code</SubType>
          <SuppressWarnings>4151</SuppressWarnings>
        </Build>
      </ItemGroup>
    </Project>

     


    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    Monday, January 17, 2011 1:35 PM

Answers

  • You can also do that use WITH XMLNAMESPACES, which is more compact:

    ;WITH XMLNAMESPACES( 'http://schemas.microsoft.com/developer/msbuild/2003' AS ns)
    SELECT
    	build.query('.'),
    	build.value('ns:SuppressWarnings[1]','nvarchar(100)') AS [SuppressedWarnings],
    	build.value('@Include','nvarchar(1000)')
    FROM @xml.nodes('//ns:Build[ns:SuppressWarnings]') AS R(build)
    
    Monday, January 17, 2011 3:51 PM
  • Maybe something like this:

    DECLARE @xml xml;
    SET @xml = '<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003" DefaultTargets="Build" ToolsVersion="4.0">
     <PropertyGroup>
     <Configuration Condition=" ''$(Configuration)'' == '''' ">Debug</Configuration>
     <Name>dbMergermarket2002</Name>
     </PropertyGroup>
     <PropertyGroup Condition=" ''$(Configuration)'' == ''Release'' ">
     <OutputPath>.\sql\release\</OutputPath>
     </PropertyGroup>
     <!--Import the settings-->
     <Import Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlTasks.targets" />
     <ItemGroup>
     <Folder Include="Scripts" />
     </ItemGroup>
     <ItemGroup>
     <PropertiesFile Include="Properties\Database.sqlsettings" />
     </ItemGroup>
     <ItemGroup>
     <Build Include="Schema Objects\Schemas\dbo\Programmability\Stored Procedures\Procs1\ap_ABS_selectAdminCompanySearchLimited.proc.sql">
      <SubType>Code</SubType>
      <SuppressWarnings>4151</SuppressWarnings>
     </Build>
     <Build Include="Schema Objects\Schemas\dbo\Programmability\Stored Procedures\Procs1\ap_ABS_SelectAllClassDetailsForTrustSeries.proc.sql">
      <SubType>Code</SubType>
     </Build>
     <Build Include="Schema Objects\Schemas\dbo\Programmability\Stored Procedures\Procs1\ap_FAILOVER_EVENTQUEUE_AppendQueue.proc.sql">
      <SubType>Code</SubType>
      <SuppressWarnings>4151</SuppressWarnings>
     </Build>
     </ItemGroup>
    </Project>';
    
    SELECT build.query('.')
    FROM @xml.nodes('declare namespace ns="http://schemas.microsoft.com/developer/msbuild/2003";
    							//ns:Build') AS R(build)
    WHERE build.exist('declare namespace ns="http://schemas.microsoft.com/developer/msbuild/2003"; 
    						ns:SuppressWarnings') = 1
    

    Monday, January 17, 2011 2:40 PM

All replies

  • OK, I'm getting a bit nearer. This gets all of the <Build> nodes out. Now what I need to do is get all of the build nodes that contain a <SuppressWarnings> node. Anyone?

    select	_nodes._node.query('.')
    		,_nodes._node.query('./Build/SuppressWarnings')
    from	(
    		select	cast('<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003" DefaultTargets="Build" ToolsVersion="4.0">
     <PropertyGroup>
      <Configuration Condition=" ''$(Configuration)'' == '''' ">Debug</Configuration>
      <Name>dbMergermarket2002</Name>
     </PropertyGroup>
     <PropertyGroup Condition=" ''$(Configuration)'' == ''Release'' ">
      <OutputPath>.\sql\release\</OutputPath>
     </PropertyGroup>
     <!--Import the settings-->
     <Import Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlTasks.targets" />
     <ItemGroup>
      <Folder Include="Scripts" />
     </ItemGroup>
     <ItemGroup>
      <PropertiesFile Include="Properties\Database.sqlsettings" />
     </ItemGroup>
     <ItemGroup>
      <Build Include="Schema Objects\Schemas\dbo\Programmability\Stored Procedures\Procs1\ap_ABS_selectAdminCompanySearchLimited.proc.sql">
       <SubType>Code</SubType>
       <SuppressWarnings>4151</SuppressWarnings>
      </Build>
      <Build Include="Schema Objects\Schemas\dbo\Programmability\Stored Procedures\Procs1\ap_ABS_SelectAllClassDetailsForTrustSeries.proc.sql">
       <SubType>Code</SubType>
      </Build>
      <Build Include="Schema Objects\Schemas\dbo\Programmability\Stored Procedures\Procs1\ap_FAILOVER_EVENTQUEUE_AppendQueue.proc.sql">
       <SubType>Code</SubType>
       <SuppressWarnings>4151</SuppressWarnings>
      </Build>
     </ItemGroup>
    </Project>' as XML).query('/') x
    		)t
    CROSS  APPLY x.nodes('declare namespace ns="http://schemas.microsoft.com/developer/msbuild/2003";
    							//ns:Build') _nodes(_node)
    
    


    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    Monday, January 17, 2011 1:55 PM
  • Maybe something like this:

    DECLARE @xml xml;
    SET @xml = '<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003" DefaultTargets="Build" ToolsVersion="4.0">
     <PropertyGroup>
     <Configuration Condition=" ''$(Configuration)'' == '''' ">Debug</Configuration>
     <Name>dbMergermarket2002</Name>
     </PropertyGroup>
     <PropertyGroup Condition=" ''$(Configuration)'' == ''Release'' ">
     <OutputPath>.\sql\release\</OutputPath>
     </PropertyGroup>
     <!--Import the settings-->
     <Import Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlTasks.targets" />
     <ItemGroup>
     <Folder Include="Scripts" />
     </ItemGroup>
     <ItemGroup>
     <PropertiesFile Include="Properties\Database.sqlsettings" />
     </ItemGroup>
     <ItemGroup>
     <Build Include="Schema Objects\Schemas\dbo\Programmability\Stored Procedures\Procs1\ap_ABS_selectAdminCompanySearchLimited.proc.sql">
      <SubType>Code</SubType>
      <SuppressWarnings>4151</SuppressWarnings>
     </Build>
     <Build Include="Schema Objects\Schemas\dbo\Programmability\Stored Procedures\Procs1\ap_ABS_SelectAllClassDetailsForTrustSeries.proc.sql">
      <SubType>Code</SubType>
     </Build>
     <Build Include="Schema Objects\Schemas\dbo\Programmability\Stored Procedures\Procs1\ap_FAILOVER_EVENTQUEUE_AppendQueue.proc.sql">
      <SubType>Code</SubType>
      <SuppressWarnings>4151</SuppressWarnings>
     </Build>
     </ItemGroup>
    </Project>';
    
    SELECT build.query('.')
    FROM @xml.nodes('declare namespace ns="http://schemas.microsoft.com/developer/msbuild/2003";
    							//ns:Build') AS R(build)
    WHERE build.exist('declare namespace ns="http://schemas.microsoft.com/developer/msbuild/2003"; 
    						ns:SuppressWarnings') = 1
    

    Monday, January 17, 2011 2:40 PM
  • Awesome, thank you fujitsu.

    After a bit of prodding and poking I ended up at this which is what I want:

    DECLARE @xml xml;
    SET @xml = '<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003" DefaultTargets="Build" ToolsVersion="4.0">
     <PropertyGroup>
     <Configuration Condition=" ''$(Configuration)'' == '''' ">Debug</Configuration>
     <Name>dbMergermarket2002</Name>
     </PropertyGroup>
     <PropertyGroup Condition=" ''$(Configuration)'' == ''Release'' ">
     <OutputPath>.\sql\release\</OutputPath>
     </PropertyGroup>
     <!--Import the settings-->
     <Import Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlTasks.targets" />
     <ItemGroup>
     <Folder Include="Scripts" />
     </ItemGroup>
     <ItemGroup>
     <PropertiesFile Include="Properties\Database.sqlsettings" />
     </ItemGroup>
     <ItemGroup>
     <Build Include="Schema Objects\Schemas\dbo\Programmability\Stored Procedures\Procs1\ap_ABS_selectAdminCompanySearchLimited.proc.sql">
     <SubType>Code</SubType>
     <SuppressWarnings>4151</SuppressWarnings>
     </Build>
     <Build Include="Schema Objects\Schemas\dbo\Programmability\Stored Procedures\Procs1\ap_ABS_SelectAllClassDetailsForTrustSeries.proc.sql">
     <SubType>Code</SubType>
     </Build>
     <Build Include="Schema Objects\Schemas\dbo\Programmability\Stored Procedures\Procs1\ap_FAILOVER_EVENTQUEUE_AppendQueue.proc.sql">
     <SubType>Code</SubType>
     <SuppressWarnings>4151</SuppressWarnings>
     </Build>
     </ItemGroup>
    </Project>';
    
    SELECT	build.query('.')
    		,build.value('declare namespace ns="http://schemas.microsoft.com/developer/msbuild/2003"; 
    										ns:SuppressWarnings[1]','nvarchar(100)') AS [SuppressedWarnings]
    		,build.value('declare namespace ns="http://schemas.microsoft.com/developer/msbuild/2003"; 
    										(./@Include)[1]','nvarchar(1000)')
    FROM @xml.nodes('declare namespace ns="http://schemas.microsoft.com/developer/msbuild/2003";
    							//ns:Build') AS R(build)
    WHERE build.exist('declare namespace ns="http://schemas.microsoft.com/developer/msbuild/2003"; 
    						ns:SuppressWarnings') = 1
    
    

     

    thanks again!


    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    Monday, January 17, 2011 3:23 PM
  • Try:

    declare @xml xml = '<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003" DefaultTargets="Build" ToolsVersion="4.0">
     <PropertyGroup>
     <Configuration Condition=" ''$(Configuration)'' == '''' ">Debug</Configuration>
     <Name>dbMergermarket2002</Name>
     </PropertyGroup>
     <PropertyGroup Condition=" ''$(Configuration)'' == ''Release'' ">
     <OutputPath>.\sql\release\</OutputPath>
     </PropertyGroup>
     <!--Import the settings-->
     <Import Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlTasks.targets" />
     <ItemGroup>
     <Folder Include="Scripts" />
     </ItemGroup>
     <ItemGroup>
     <PropertiesFile Include="Properties\Database.sqlsettings" />
     </ItemGroup>
     <ItemGroup>
     <Build Include="Schema Objects\Schemas\dbo\Programmability\Stored Procedures\Procs1\ap_ABS_selectAdminCompanySearchLimited.proc.sql">
      <SubType>Code</SubType>
      <SuppressWarnings>4151</SuppressWarnings>
     </Build>
     <Build Include="Schema Objects\Schemas\dbo\Programmability\Stored Procedures\Procs1\ap_ABS_SelectAllClassDetailsForTrustSeries.proc.sql">
      <SubType>Code</SubType>
     </Build>
     <Build Include="Schema Objects\Schemas\dbo\Programmability\Stored Procedures\Procs1\ap_FAILOVER_EVENTQUEUE_AppendQueue.proc.sql">
      <SubType>Code</SubType>
      <SuppressWarnings>4151</SuppressWarnings>
     </Build>
     </ItemGroup>
    </Project>';
    
    
    
    select @xml.query ('declare default element namespace 
    "http://schemas.microsoft.com/developer/msbuild/2003";
    //Build[SuppressWarnings]')
    
    select x.n.query ('declare default element namespace 
    "http://schemas.microsoft.com/developer/msbuild/2003";.')
    from @xml.nodes ('declare default element namespace 
    "http://schemas.microsoft.com/developer/msbuild/2003";
    //Build[SuppressWarnings]') x (n)

    Russel Loski, MCT
    Monday, January 17, 2011 3:33 PM
  • You can also do that use WITH XMLNAMESPACES, which is more compact:

    ;WITH XMLNAMESPACES( 'http://schemas.microsoft.com/developer/msbuild/2003' AS ns)
    SELECT
    	build.query('.'),
    	build.value('ns:SuppressWarnings[1]','nvarchar(100)') AS [SuppressedWarnings],
    	build.value('@Include','nvarchar(1000)')
    FROM @xml.nodes('//ns:Build[ns:SuppressWarnings]') AS R(build)
    
    Monday, January 17, 2011 3:51 PM
  • You can also do that use WITH XMLNAMESPACES, which is more compact:

    ;WITH
     XMLNAMESPACES( 'http://schemas.microsoft.com/developer/msbuild/2003'
     AS
     ns)
    SELECT
    
    	build.query('.'
    ),
    	build.value('ns:SuppressWarnings[1]'
    ,'nvarchar(100)'
    ) AS
     [SuppressedWarnings],
    	build.value('@Include'
    ,'nvarchar(1000)'
    )
    FROM
     @xml.nodes('//ns:Build[ns:SuppressWarnings]'
    ) AS
     R(build)
    

    Oh, even nicer. Thank you Bob, really cool!

    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    Monday, January 17, 2011 3:55 PM
  • Here's what I finally settled on. Thanks all!!

    DECLARE @xml xml;
    SET @xml = '<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003" DefaultTargets="Build" ToolsVersion="4.0">
     <PropertyGroup>
     <Configuration Condition=" ''$(Configuration)'' == '''' ">Debug</Configuration>
     <Name>dbMergermarket2002</Name>
     </PropertyGroup>
     <PropertyGroup Condition=" ''$(Configuration)'' == ''Release'' ">
     <OutputPath>.\sql\release\</OutputPath>
     </PropertyGroup>
     <!--Import the settings-->
     <Import Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlTasks.targets" />
     <ItemGroup>
     <Folder Include="Scripts" />
     </ItemGroup>
     <ItemGroup>
     <PropertiesFile Include="Properties\Database.sqlsettings" />
     </ItemGroup>
     <ItemGroup>
     <Build Include="Schema Objects\Schemas\dbo\Programmability\Stored Procedures\Procs1\ap_ABS_selectAdminCompanySearchLimited.proc.sql">
     <SubType>Code</SubType>
     <SuppressWarnings>4151</SuppressWarnings>
     </Build>
     <Build Include="Schema Objects\Schemas\dbo\Programmability\Stored Procedures\Procs1\ap_ABS_SelectAllClassDetailsForTrustSeries.proc.sql">
     <SubType>Code</SubType>
     </Build>
     <Build Include="Schema Objects\Schemas\dbo\Programmability\Stored Procedures\Procs1\ap_FAILOVER_EVENTQUEUE_AppendQueue.proc.sql">
     <SubType>Code</SubType>
     <SuppressWarnings>4151</SuppressWarnings>
     </Build>
     </ItemGroup>
    </Project>';
    
    ;WITH XMLNAMESPACES( 'http://schemas.microsoft.com/developer/msbuild/2003' AS ns)
    SELECT	 REVERSE(SUBSTRING(REVERSE(ObjectPath),0,CHARINDEX('\',REVERSE(ObjectPath)))) AS [ObjectName]
    		,[SuppressedWarnings]
    FROM	(
    		SELECT	build.value('ns:SuppressWarnings[1]','nvarchar(100)') AS [SuppressedWarnings]
    		,		build.value('@Include','nvarchar(1000)') AS [ObjectPath]
    
    		FROM	@xml.nodes('//ns:Build[ns:SuppressWarnings]') AS R(build)
    		)q
    


    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    Monday, January 17, 2011 4:00 PM
  • Incidentally, for large project files the method that used .exist() was taking minutes to execute. Bob's method was subsecond. No brainer really :)

    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    Monday, January 17, 2011 4:04 PM