Assigning Dynamic Properties to an XML Bulk Post

1. Bulk Post ASP VB Script
 
'<% 
 
Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString = "provider=SQLOLEDB.1;data source=COMPUTER1\SQLEXPRESS;database=database1;uidXXXXX=;pwd=XXXXX"
objBL.KeepIdentity = False
objBL.ForceTableLock = True
objBL.Transaction = True
objBL.ErrorLogFile = "C:\Inetpub\wwwroot\website\log.log"
objBL.Execute "C:\Inetpub\wwwroot\website\xml_upload\transform.xml", "C:\Inetpub\wwwroot\website\XML\company_account\job_import.xml"
Set objBL = Nothing
 
%>'
 
2. XML Data Sheet Example
 
<ROOT>
  <JobAd>
        
	<CompanyID>1</CompanyID>
	<CompanyName>TestCompany.com</CompanyName>
	<PositionTitle>Bulk Import Test Job</PositionTitle>
	<JobDescription>Currently, Test Company are seeking for qualified candidates to join their United Kingdom team as Chief Financial Officer. This opportunity is a Permanent, Contract position to be based at London, United Kingdom.</JobDescription>
	<Responsibilities>Test job only</Responsibilities>
	<CandRequirement>The ideal candidate should fulfill the following requirements: At least 5 years of working experience in a related field Possess a Diploma and above from a recognized institution Fluency in English will be essential</CandRequirement>
	<Industry>IT</Industry>
	<Country>United Kingdom</Country>
	<JobAdStatus>Active</JobAdStatus>
	<PostingDate>9/11/2008 2:48:26 AM</PostingDate>
	<ApplicationDeadline>5/3/2009 12:00:00 AM</ApplicationDeadline>
	<CreatedDate>9/11/2008 2:48:26 AM</CreatedDate>
	<CompanyIntro>1111</CompanyIntro>
        <OpenApplication>1</OpenApplication>
	<OpenApplicationEmailAdd>support@xpandedmedia.com</OpenApplicationEmailAdd>
	<EmploymentType>Permanent</EmploymentType>
	<JobLocation>London</JobLocation>
	<MaxYrsExp>12</MaxYrsExp>
	<MinYrsExp>2</MinYrsExp>
	<MinEducation>Degree</MinEducation>
	<FieldofStudy>Computer Science</FieldofStudy>
	<Language1>English</Language1>
	<Language2>French</Language2>
  </JobAd>
</ROOT>
 
3. Finally the transformation code, which we shall call transform.xml
 
<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data" 
        xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"  
        xmlns:sql="urn:schemas-microsoft-com:xml-sql" > 
 
   <ElementType name="JobAdID" dt:type="int" />
   <ElementType name="CompanyID" dt:type="int" />
   <ElementType name="CompanyName" dt:type="string" />
   <ElementType name="PositionTitle" dt:type="string" />
   <ElementType name="JobDescription" dt:type="string" />
   <ElementType name="Responsibilities" dt:type="string" />
   <ElementType name="CandRequirement" dt:type="string" />
   <ElementType name="Industry" dt:type="string" />
   <ElementType name="Country" dt:type="string" />
   <ElementType name="JobAdStatus" dt:type="string" />
   <ElementType name="PostingDate" dt:type="string" />
   <ElementType name="ApplicationDeadline" dt:type="string" />
   <ElementType name="CreatedDate" dt:type="string" />
   <ElementType name="CompanyIntro" dt:type="string" />
   <ElementType name="OpenApplication" dt:type="string" />
   <ElementType name="OpenApplicationEmailAdd" dt:type="string" />
   <ElementType name="EmploymentType" dt:type="string" />
   <ElementType name="JobLocation" dt:type="string" />
   <ElementType name="MaxYrsExp" dt:type="string" />
   <ElementType name="MinYrsExp" dt:type="string" />
   <ElementType name="MinEducation" dt:type="string" />
   <ElementType name="FieldofStudy" dt:type="string" />
   <ElementType name="Language1" dt:type="string" />
   <ElementType name="Language2" dt:type="string" />
 
   <ElementType name="ROOT" sql:is-constant="1">
      <element type="JobAd" />
   </ElementType>
 
          <ElementType name="JobAd"  sql:relation="tblmJobAds">
          <element type="JobAdID"  sql:field="JobAdID" />
          <element type="CompanyID" sql:field="CompanyID" />
          <element type="CompanyName"        sql:field="CompanyName" />
	  <element type="PositionTitle"        sql:field="PositionTitle" />
	  <element type="JobDescription"        sql:field="JobDescription" />
	  <element type="Responsibilities"        sql:field="Responsibilities" />
	  <element type="CandRequirement"        sql:field="CandRequirement" />
	  <element type="Industry"        sql:field="Industry" />
	  <element type="Country"        sql:field="Country" />
	  <element type="JobAdStatus"        sql:field="JobAdStatus" />
	  <element type="PostingDate"        sql:field="PostingDate" />
	  <element type="ApplicationDeadline"        sql:field="ApplicationDeadline" />
          <element type="CreatedDate"        sql:field="CreatedDate" />
	  <element type="OpenApplication"        sql:field="OpenApplication" />
	  <element type="EmploymentType"        sql:field="EmploymentType" />
	  <element type="JobLocation"        sql:field="JobLocation" />
	  <element type="MaxYrsExp"        sql:field="MaxYrsExp" />
	  <element type="MinYrsExp"        sql:field="MinYrsExp" />
	  <element type="MinEducation"        sql:field="MinEducation" />
	  <element type="FieldofStudy"        sql:field="FieldofStudy" />
	  <element type="Language1"        sql:field="Language1" />
	  <element type="Language2"        sql:field="Language2" />
   </ElementType>
 
</Schema>
                                  

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108: