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 ==================================
<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 ==================================
<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.
No comments:
Post a Comment