Select *  FROM dbo.aspnet_applications  FOR XML AUTO, XMLSCHEMA

The xsd generated from sql using the following query(aspnet_applications is the table name) will have the sqltypes imported in the schema. If we use the xsd command line tool to generate a cs class the xsd will through the exception

“Error: Error generating classes for schema ‘aspnet_applications’.
  – The datatype ‘http://schemas.microsoft.com/sqlserver/2004/sqltypes:nvarchar’
is missing.”

If we look at the xsd generated by sql the namespace will point to the sqltypes.xsd uri which xsd.exe could not resolve.

—————–XSD Generated By SQL ————–

<xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified">
  <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
  <xsd:element name="AETSMiMgmtDev.dbo.aspnet_Applications">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="ApplicationName">
          <xsd:simpleType>
            <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreNonSpace IgnoreKanaType IgnoreWidth">
              <xsd:maxLength value="256" />
            </xsd:restriction>
          </xsd:simpleType>
        </xsd:element>
        <xsd:element name="LoweredApplicationName">
          <xsd:simpleType>
            <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreNonSpace IgnoreKanaType IgnoreWidth">
              <xsd:maxLength value="256" />
            </xsd:restriction>
          </xsd:simpleType>
        </xsd:element>
        <xsd:element name="ApplicationId" type="sqltypes:uniqueidentifier" />
        <xsd:element name="Description" minOccurs="0">
          <xsd:simpleType>
            <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreNonSpace IgnoreKanaType IgnoreWidth">
              <xsd:maxLength value="256" />
            </xsd:restriction>
          </xsd:simpleType>
        </xsd:element>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

———————END——————

To solve this issue i modified the xsd to include the xsd in the local directory. I navigated to the URL “http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd” , downloaded the full xsd and saved the file as sqltypes.xsd

After saving the sqltypes in a seperate xsd, i modified the original aspnet_applications.xsd like below

—————–Modified XSD to work for xsd.exe————

<xsd:schema targetNamespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" xmlns="Subscribers"
            xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"
            elementFormDefault="qualified">
  <xsd:include schemaLocation="sqltypes.xsd" />
  <xsd:element name="aspnet_Applications">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="ApplicationName">
          <xsd:simpleType>
            <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreNonSpace IgnoreKanaType IgnoreWidth">
              <xsd:maxLength value="256" />
            </xsd:restriction>
          </xsd:simpleType>
        </xsd:element>
        <xsd:element name="LoweredApplicationName">
          <xsd:simpleType>
            <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreNonSpace IgnoreKanaType IgnoreWidth">
              <xsd:maxLength value="256" />
            </xsd:restriction>
          </xsd:simpleType>
        </xsd:element>
        <xsd:element name="ApplicationId" type="sqltypes:uniqueidentifier" />
        <xsd:element name="Description" minOccurs="0">
          <xsd:simpleType>
            <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreNonSpace IgnoreKanaType IgnoreWidth">
              <xsd:maxLength value="256" />
            </xsd:restriction>
          </xsd:simpleType>
        </xsd:element>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

——————————END————-

The modified tag is in red bold color. Remember the downloaded sqltypes.xsd is in the same directory as the aspnet_applications.xsd
Now if we run xsd.exe aspnet_applications.xsd /c

the class generation will be successful