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.