Creating schema of XML structure

In previous post we have seen how can we suggest XML structure from a Rational db desgin and how it understand db structrue with XML column in table. http://shamas-saeed.blogspot.com/2012/06/selecting-xml-structure-from-relational.html

 

I have created XML structure successfully but do not know how to create its schema that is required to bind it with XML data type in SQL Server. It is very easy to create schema from XML structure. Lets consider both XML that we have pasted in our last post and follow these steps to create its schema.

 

Open Visual Studio from your system and create a new file, it will pop up following window to confirm which type of file you want to create.

 

 

 

Select XML Schema as file type and new file is created with some sample code. Delete that code and paste your XML in that file as shown below. From tool panel you must select XML Editor to show XML Properties tab.

 

 

 

 

Now click on create schema option from top left cornor of XML Editor pannel. It will create XML schema for your XML file.

 

 

 

Following are the schema's for both XML examples we have discussed in previous posts.

 

========================= User Criteria XML Structure ==================================

<?xml version="1.0" encoding="utf-8"?>

<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">

  <xs:element name="User">

    <xs:complexType>

      <xs:sequence>

        <xs:element maxOccurs="unbounded" name="Level">

          <xs:complexType>

            <xs:sequence minOccurs="0">

              <xs:element name="Criteria">

                <xs:complexType>

                  <xs:sequence>

                    <xs:element name="Value" type="xs:string" />

                  </xs:sequence>

                  <xs:attribute name="Type" type="xs:string" use="required" />

                  <xs:attribute name="TypeId" type="xs:unsignedByte" use="required" />

                  <xs:attribute name="FunctionId" type="xs:unsignedByte" use="required" />

                </xs:complexType>

              </xs:element>

            </xs:sequence>

            <xs:attribute name="Id" type="xs:unsignedByte" use="required" />

            <xs:attribute name="Enabled" type="xs:boolean" use="required" />

          </xs:complexType>

        </xs:element>

      </xs:sequence>

      <xs:attribute name="CampaignType" type="xs:unsignedByte" use="required" />

    </xs:complexType>

  </xs:element>

</xs:schema>

========================= User Criteria XML Structure ===================================

and
========================= Campaign Search XML Structure ==================================

<?xml version="1.0" encoding="utf-8"?>

<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">

  <xs:element name="Campaigns">

    <xs:complexType>

      <xs:sequence>

        <xs:element maxOccurs="unbounded" name="C">

          <xs:complexType>

            <xs:attribute name="CId" type="xs:unsignedShort" use="required" />

            <xs:attribute name="AId" type="xs:unsignedByte" use="required" />

          </xs:complexType>

        </xs:element>

      </xs:sequence>

    </xs:complexType>

  </xs:element>

</xs:schema>

========================= Campaign Search XML Structure ===================================

 

Note: Now we have done our basics to use XML in database. We have selected our XML structure and generate its schema as well. In next post we will discuss how to bind this xml structure with SQL Server 2008 database using SSMS.