How to insert sub root node in xml file

Posted by pravakar on Stack Overflow See other posts from Stack Overflow or by pravakar
Published on 2009-11-02T09:47:23Z Indexed on 2010/06/12 7:02 UTC
Read the original article Hit count: 295

Filed under:
|

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

© Stack Overflow or respective owner

Related posts about sql-server

Related posts about Xml