Support for *.xsd files as Build Includes

[DanielLoth]

Hello.

I'm just reaching out to ask if the following is supported:

<Build Include="Example.xsd">
  <SubType>NotInBuild</SubType>
  <RelationalSchema>dbo</RelationalSchema>
  <XMLSchemaCollectionName>Example</XMLSchemaCollectionName>
</Build>

[jmezach]

@DanielLoth We don't do anything with .xsd files specifically so I don't expect that to work. What would be your expectation of the output here?

[ErikEJ]

@DanielLoth You can use SQL based definition, but we do not process .xsd files currently.

CREATE XML SCHEMA COLLECTION [dbo].[XMLSchemaCollection2]
	AS N'[SomeXmlSchema]'

  <ItemGroup>
    <Build Include="Table1.sql" />
    <Build Include="XMLSchemaCollection1.xsd">
      <RelationalSchema>dbo</RelationalSchema>
      <XMLSchemaCollectionName>XMLSchemaCollection1</XMLSchemaCollectionName>
      <AnsiNulls>Off</AnsiNulls>
    </Build>
    <Build Include="XMLSchemaCollection2.sql" />
  </ItemGroup>

Sample model fragments from classic .sqlproj:

		<Element Type="SqlXmlSchemaCollection" Name="[dbo].[XMLSchemaCollection1]">
			<Property Name="SchemaExpression">
				<Value><![CDATA[N'<?xml version="1.0" encoding="utf-16"?>
<xs:schema id="XMLSchemaCollection1"
    targetNamespace="http://tempuri.org/XMLSchemaCollection1.xsd"
    elementFormDefault="qualified"
    xmlns="http://tempuri.org/XMLSchemaCollection1.xsd"
    xmlns:mstns="http://tempuri.org/XMLSchemaCollection1.xsd"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
>
</xs:schema>
']]></Value>
			</Property>
			<Relationship Name="Schema">
				<Entry>
					<References ExternalSource="BuiltIns" Name="[dbo]" />
				</Entry>
			</Relationship>
		</Element>
		<Element Type="SqlXmlSchemaCollection" Name="[dbo].[XMLSchemaCollection2]">
			<Property Name="SchemaExpression">
				<Value><![CDATA[N'[SomeXmlSchema]']]></Value>
			</Property>
			<Relationship Name="Schema">
				<Entry>
					<References ExternalSource="BuiltIns" Name="[dbo]" />
				</Entry>
			</Relationship>
		</Element>

[DanielLoth]

@DanielLoth We don't do anything with .xsd files specifically so I don't expect that to work. What would be your expectation of the output here?

Hi @jmezach,

I'd basically want to have the behaviour match the traditional SqlProject build. I'm not on my work computer as I write this, but my understanding is that the schema defined in the XSD file in question governs the XML content of at least one XML column in the database.

I will take a closer look at it tomorrow when I'm back on the right computer.

@ErikEJ thanks. I'll take a look at that tomorrow too. Ideally, I feel it'd be good to be able to transition to the new SDK format without having to change anything about the other files in the project. But colleagues might be receptive to the idea.

[ErikEJ]

@DanielLoth Agree that feature parity is a good goal, use .sql was just a workaround to get you unblocked.

[DanielLoth]

Just writing back to say that the workaround - using a T-SQL based definition of the XSD - worked in my case, and I was able to get a clean database project build using dotnet.exe as a result.

I've merely presented the working dotnet build as a proof-of-concept at this point and haven't discussed next steps with my colleagues. I think in the first instance I'll need to get the proof-of-concept branch into a state whereby the existing build continues to work (and that could be achieved by using the T-SQL based XSD definition and removing the *.xsd file).

Out of curiosity though, would supporting *.xsd files be something you'd conceivably accomplish in the near term if you were aspiring for feature parity?

[ErikEJ]

We are open to pull requests but please keep in mind we maintain this project for free in our own time.

Are you or your company a GitHub sponsor?

[DanielLoth]

We are open to pull requests but please keep in mind we maintain this project for free in our own time.

Are you or your company a GitHub sponsor?

Hi @ErikEJ - that is completely fair. To the best of my knowledge, my company is not a sponsor of any open-source projects. I'm not either in a personal capacity.

Given what you've said about accepting pull requests, I might try to find some time to look myself. I've had relatively little practice with more complicated/substantial msbuild stuff but have some working knowledge.

[jmezach]

I did some digging into DacFx, since most of what this SDK does is using DacFx API's to build the .dacpac file. By the looks of it there is no readily available API to add an .xsd file to the model. My guess is that the old SQL Server Database projects did some kind of transformation in MSBuild to turn the .xsd file into a .sql script and then added that to the model.

@ErikEJ Do you happen to no if this is supported in Microsoft.Build.Sql?

[DanielLoth]

Hi @jmezach,

Just a heads-up that I spoke with the principal engineer in my area today about the merits of getting our database build working using dotnet.exe (and so, cross-platform), and he encouraged me towards other endeavours.

In saying that, I tend to agree. Once @ErikEJ suggested the workaround, it was literally a copy-paste of the XSD file content into the example T-SQL fragment he provided to get it building as desired.

If you have on-hand a FileInfo object referring to an XSD file within your DacpacTool project, then you could:

Read the content of the file into memory as text, take that text and directly interpolate it into a string like so:

var fragment = $"CREATE XML SCHEMA COLLECTION [{SchemaName}}].[{ObjectName}] AS N'{XsdText}';"

And then parse that into a TSqlObject with TSql130Parser (or whichever version is appropriate given the compatibility level the project is targetting) and then add that TSqlObject to the TSqlModel.

[ErikEJ]

@jmezach I was unable to find an API for this, I also assume that the legacy build process creates a SQL statement indicating the schema, name and xsd file contents as metioned by @DanielLoth above.

I do not know if Microsoft.Build.Sql supports .xsd files, (doubt it) - but maybe @dzsquared knows?

[dzsquared]

This behavior is an example of a happy accident -with Microsoft.Build.Sql designed to be a superset of the original SQL projects, even totally undocumented behavior just works. I'm not familiar enough with MSBuild.Sdk.SqlProj enough to know if this would work out of the box here as well, but it may not be strarting from scratch since this behavior is part of the SqlBuild target.

This entry in the Microsoft.Build.Sql project file:

  <ItemGroup>
      <Build Include="OrderSchema.xsd">
      <RelationalSchema>dbo</RelationalSchema>
      <XMLSchemaCollectionName>OrderSchemaTSQL</XMLSchemaCollectionName>
    </Build>
  </ItemGroup>

Causes the associated T-SQL code for the OrderSchema.xsd file (schema collection) to be generated during an early project build step and placed into the obj folder. Then this table definition works, for example:

CREATE TABLE dbo.Orders (
    Id INT PRIMARY KEY,
    OrderData XML(dbo.OrderSchemaTSQL) 
);

Noting that there's something buggy in the generation code that's placing the SQL in "obj" on my non-Windows machine, some flaw in path concatenation or platform handling.

[ErikEJ]

@dzsquared Thanks for the detailed reply. We do not process .xsd files, but great insights into the sqlbuild targets - so basically create a .sql file from the xsd file combined with the name and schema from the project file.

[ErikEJ]

@jmezach Maybe we could just add a section to the readme with the workaround steps confirmed by @DanielLoth above?

[jmezach]

@ErikEJ Yeah, that could be a start at least. Perhaps somebody wants to contribute an actual implementation of this later. It is not something that we are actively using, so I don't think it's going to be high on our TODO list.