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.

 

Selecting XML Structure from relational DB

We have started our discussion to select XML from beging to implementation XML in Sql server. http://shamas-saeed.blogspot.com/2012/05/schema-xml-memoir-definition-to.html

In previous version we have seen what is the major difference between typed vs untyped XML. http://shamas-saeed.blogspot.com/2012/06/typed-xml-vs-untyped-xml.html

Now we have understanding of XML and its schema. Our next step is to see how can we prepare XML structure from tables. As we have seen that xml used to implement for complex structures like student information history, patient history in hospital management, logical comparison of properties in Property based database and CV detail of employees on any CV site. To understand our flow I have selected a structure shown in following diagram

From the attached diagram we can define above structure as Advertiser create campaigns with some specific criteria like CTR,GEN etc. User also set its search criteria that includes parameter values against campaign types. This data used once searched for future search optimization. final result stores in Campaign search. 

Now I want to change its structure as there is an expensive data in user criteria table against each user and each criteria type. Then against each criteria type there are multiple campaign search results. I have changed its structure as follows

I have created a new table as CacheTree and create CampaignSearch XML column to store search results and UserCriteria table to store User search criteria that campare results with campaigns table for optimization.

To implement this My XML will be like this

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

<User CampaignType="1">
  <Level Id="1" Enabled="true">
    <Criteria Type="CTR" TypeId="1" FunctionId="1">
      <Value>%(59)%</Value>
    </Criteria>
  </Level>
  <Level Id="2" Enabled="true" />  
</User>
=========================  User Criteria XML  ===================================


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

<Campaigns>
  <C CId="754" AId="9" />
  <C CId="757" AId="9" />
</Campaigns>
=========================  Campaign Search XML  ===================================

In above structure CID is CampaignID and AId is AdvertiserID. there is no need to store UserID as when any user apply any search format we compare that format with User criteria XML and if any match found we will return its Campaign Search XML instead of applying search queries on Campaign table.

Next: I have created XML structure but now i need to create its schema and I don't know how to create that. In our next post we will see how easily we can generate schema of any structure we have selected.