Tutorial :How to insert sub root node in xml file



Question:

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>vndivakar@yahoo.com</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>vndivakar@yahoo.com</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


Solution:1

I think that you need to cast your initial query to xml type and then apply for xml path('') again


select cast((  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 jwhere r.Advid = j.Advid              and job_location_city like('%'+'+ @City +'+'%')              and job_location_state in (''+ @State +'')              and job_status = 1   for xml path('')) as xml) as JobAds for xml path(''), root('CapitalJobsList'), elements xsinil  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »