Hi guys hope all are doing good. I want to create one sub root node in my xml file like,
<CapitalJobsList xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<JobAds> -- element to create
<JobAd>
<AdvertiserDetails>
<AdvertiserId>718508549</AdvertiserId>
<AdvertiserName>ABC</AdvertiserName>
</AdvertiserDetails>
<ConsultantDetails>
<ContactName>Naga Divakar</ContactName>
<ContactPhone>6239 7755</ContactPhone>
<ContactEmail>[email protected]</ContactEmail>
<ContactFax>12345678912</ContactFax>
</ConsultantDetails>
<JobAdDetails>
<DateEntered>2009-10-03T21:09:35.500</DateEntered>
<AdvertiserJobRef>83754865</AdvertiserJobRef>
<Title>IT Operations Manager</Title>
<DescriptionShort>Large scale/exciting projects Mentor and manage o...</DescriptionShort>
<Description>Large scale/exciting projects Mentor and manage others Management/technical mix This is a fantastic opportunity to join a high profile client who is active across both the commercial and Government domain. As the IT Operations Manager you will be responsible for leading and mentoring a small team of Infrastructure Engineers to ensure the availability and performance of the IT infrastructure. You w</Description>
<SalaryMin>0.00</SalaryMin>
<SalaryMax>0.00</SalaryMax>
<WorkType xsi:nil="true" />
<Location>) as [JobAd/JobAdDetails/Bullets],
isnull(Job</Location>
<PostCode>2600</PostCode>
<ClosingDate>2009-11-01T00:00:00</ClosingDate>
<Keywords xsi:nil="true" />
<ApplyEmail xsi:nil="true" />
<ApplyURL>http://jobview.careerone.com.au/GetJob.aspx?JobID=83754865</ApplyURL>
</JobAdDetails>
<JobAdOptions>
<BlindPost xsi:nil="true" />
<AdFormatType xsi:nil="true" />
<AdTemplateName xsi:nil="true" />
<ShowContactDetails xsi:nil="true" />
<ShowSalary xsi:nil="true" />
<HasVideo xsi:nil="true" />
<ResumeRequired>1</ResumeRequired>
<ResidentsOnly>0</ResidentsOnly>
</JobAdOptions>
<CategoryList>
<Category xsi:nil="true" />
</CategoryList>
<RegionsList>
<Region>ACT</Region>
</RegionsList>
<LevelsList>
<Level xsi:nil="true" />
</LevelsList>
</JobAd>
<JobAd>
<AdvertiserDetails>
<AdvertiserId>718508549</AdvertiserId>
<AdvertiserName>ABC</AdvertiserName>
</AdvertiserDetails>
<ConsultantDetails>
<ContactName>Naga Divakar</ContactName>
<ContactPhone>6239 7755</ContactPhone>
<ContactEmail>[email protected]</ContactEmail>
<ContactFax>12345678912</ContactFax>
</ConsultantDetails>
<JobAdDetails>
<DateEntered>2009-10-03T21:09:35.530</DateEntered>
<AdvertiserJobRef>83731488</AdvertiserJobRef>
<Title>SAP Developers Required in Canberra - 12 month contract</Title>
<DescriptionShort>My client, a large government department in Canbe...</DescriptionShort>
<Description>My client, a large government department in Canberra, seeks two SAP Developers for 12 month ongoing contracts. Two SAP Developers Required Expert level ABAP programming skills Large SAP landscape - SAP R/3, SAP Web, SAP BI, SAP ITS My client, a large government department in Canberra, seeks two SAP Developers for 12 month ongoing contracts. My client is a large government department in Canberra, a</Description>
<SalaryMin>0.00</SalaryMin>
<SalaryMax>0.00</SalaryMax>
<WorkType xsi:nil="true" />
<Location>) as [JobAd/JobAdDetails/Bullets],
isnull(Job</Location>
<PostCode>2600</PostCode>
<ClosingDate>2009-11-01T00:00:00</ClosingDate>
<Keywords xsi:nil="true" />
<ApplyEmail xsi:nil="true" />
<ApplyURL>http://jobview.careerone.com.au/GetJob.aspx?JobID=83731488</ApplyURL>
</JobAdDetails>
<JobAdOptions>
<BlindPost xsi:nil="true" />
<AdFormatType xsi:nil="true" />
<AdTemplateName xsi:nil="true" />
<ShowContactDetails xsi:nil="true" />
<ShowSalary xsi:nil="true" />
<HasVideo xsi:nil="true" />
<ResumeRequired>1</ResumeRequired>
<ResidentsOnly>0</ResidentsOnly>
</JobAdOptions>
<CategoryList>
<Category xsi:nil="true" />
</CategoryList>
<RegionsList>
<Region>ACT</Region>
</RegionsList>
<LevelsList>
<Level xsi:nil="true" />
</LevelsList>
</JobAd>
</JobAds>
</CapitalJobsList>
I have used the sql query for xml path like:
select
r.advid as [JobAd/AdvertiserDetails/AdvertiserId],
CompanyName as [JobAd/AdvertiserDetails/AdvertiserName],
firstname +'' ''+ lastname as [JobAd/ConsultantDetails/ContactName],
WorkPhone as [JobAd/ConsultantDetails/ContactPhone],
AdvEmail as [JobAd/ConsultantDetails/ContactEmail],
FaxNo as [JobAd/ConsultantDetails/ContactFax],
Job_CreatedDate as [JobAd/JobAdDetails/DateEntered],
Job_Id as [JobAd/JobAdDetails/AdvertiserJobRef],
Job_Title as [JobAd/JobAdDetails/Title],
substring(Job_Description,0,50)+''...'' as [JobAd/JobAdDetails/DescriptionShort],
Job_Description as [JobAd/JobAdDetails/Description],
CONVERT(DECIMAL(10,2),MinSalary) as [JobAd/JobAdDetails/SalaryMin],
CONVERT(DECIMAL(10,2),MaxSalary) as [JobAd/JobAdDetails/SalaryMax],
Job_Type as [JobAd/JobAdDetails/WorkType],
isnull(Job_Bullets,'') as [JobAd/JobAdDetails/Bullets],
isnull(Job_Location,'') as [JobAd/JobAdDetails/Location],
Job_PostCode as [JobAd/JobAdDetails/PostCode],
Job_ExpireDate as [JobAd/JobAdDetails/ClosingDate],
Job_Keywords as [JobAd/JobAdDetails/Keywords],
ApplyEmail as [JobAd/JobAdDetails/ApplyEmail],
Job_BrandURL+Job_Id as [JobAd/JobAdDetails/ApplyURL],
BlindPost as [JobAd/JobAdOptions/BlindPost],
AdFormatType as [JobAd/JobAdOptions/AdFormatType],
AdTemplateName as [JobAd/JobAdOptions/AdTemplateName],
ShowContactDetails as [JobAd/JobAdOptions/ShowContactDetails],
ShowSalary as [JobAd/JobAdOptions/ShowSalary],
HasVideo as [JobAd/JobAdOptions/HasVideo],
ResumeRequired as [JobAd/JobAdOptions/ResumeRequired],
ResidentsOnly as [JobAd/JobAdOptions/ResidentsOnly],
Job_Category as [JobAd/CategoryList/Category],
Job_Location_State as [JobAd/RegionsList/Region],
[Level] as [JobAd/LevelsList/Level]
from DR_Adv_Registration r, DR_CareerOne_ACTJobs j
where r.Advid = j.Advid and job_location_city like(''%'+''+ @City +''+'%'') and job_location_state in('''+ @State +''') and job_status=1 for xml path(''''), Root(''CapitalJobsList''),ELEMENTS XSINIL
So, suggest me how to get the sub root node. Thanks in advance