Wednesday, 6 January 2021

How to create XSD schema from MSSQL Database table

 You'll need Microsoft SQL Server Management Studio to run the below query. 

Select the database where you want to run the query against. 

Open a new query window and run the below command 

    DECLARE @SanjeebXsdSchema xml

SET @SanjeebXsdSchema = (SELECT top 1 * FROM [dbo].[BOM_DW_Extract] FOR XML AUTO, ELEMENTS, XMLSCHEMA('sanjeebXsdSchema'))

SELECT @SanjeebXsdSchema


Result:

<xsd:schema xmlns:schema="sanjeebXsdSchema" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" targetNamespace="sanjeebXsdSchema" 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="dbo.BOM_DW_Extract">

    <xsd:complexType>

      <xsd:sequence>

        <xsd:element name="CompanyCode" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="50" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="ParentPart" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="50" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="ComponentPart" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="50" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="ComponentSequence" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="50" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="ComponentQuantity" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="50" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="Reference" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="20" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="BomType" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="20" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="StartDate" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="50" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="EndDate" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="50" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

        <xsd:element name="ModifiedDate" minOccurs="0">

          <xsd:simpleType>

            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">

              <xsd:maxLength value="50" />

            </xsd:restriction>

          </xsd:simpleType>

        </xsd:element>

      </xsd:sequence>

    </xsd:complexType>

  </xsd:element>

</xsd:schema>


No comments:

Post a Comment

Raspberry pi: how to find raspberry pi model details

In the raspberry pi console type:    pinout output: ,--------------------------------. | oooooooooooooooooooo J8 +==== | 1oooooooooooooo...