Sign in
 
HomeLibraryLearnDownloadsTroubleshootingCommunityForums
SQL Server Developer Center >
> 
> 
Parsing a Visual Studio project file

답변됨 Parsing a Visual Studio project file

  • Monday, January 17, 2011 1:35 PM
     
      Has Code
    Sign In to Vote
    0

    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
     

All Replies

  • Monday, January 17, 2011 1:55 PM
     
      Has Code
    Sign In to Vote
    0

    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 2:40 PM
     
     Answered Has Code
    Sign In to Vote
    1

    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 3:23 PM
     
      Has Code
    Sign In to Vote
    0

    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:33 PM
     
      Has Code
    Sign In to Vote
    1

    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:51 PM
    Answerer
     
     Answered Has Code
    Sign In to Vote
    2

    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:55 PM
     
      Has Code
    Sign In to Vote
    0

    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 4:00 PM
     
      Has Code
    Sign In to Vote
    0
    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:04 PM
     
     
    Sign In to Vote
    0
    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