Help w/reducing redundencies in XML output

PracticalProgram · #1 (**permalink**) 02-09-12, 13:12

Having difficulty achieving an end-result in transforming the results of a rowset query into XML.

If there is someone out there who could help me our, I'd appreciate it.

Here is simplified test code that displays my problem:

Code:

declare @TimesheetHdrs table (EmpID int,EntryYear smallint,EntryPeriod tinyint,AdminNotes varchar(max),UserNotes varchar(max))
declare @TimesheetDtls table (EmpID int,EntryYear smallint,EntryPeriod tinyint,ProjCode varchar(25),ActCode varchar(25),ExpendCode varchar(10),EntryDate date,EntryQty decimal(7,2))
declare @Projects table (ProjCode varchar(25),ProjName varchar(200))
declare @Activities table (ProjCode varchar(25),ActCode varchar(25),ActName varchar(200))
declare @Expenditures table (ProjCode varchar(25),ExpendCode varchar(25),ExpendName varchar(200))

insert into @TimesheetHdrs values(1,2012,1,'These are the admin notes','These are the user notes')
insert into @TimesheetDtls values(1,2012,1,'TestProject','000103020200302302322','1','1/3/2012',4.5)
insert into @TimesheetDtls values(1,2012,1,'TestProject','000103020200302302322','2','1/3/2012',0.5)
insert into @TimesheetDtls values(1,2012,1,'TestProject','000103020200302302322','1','1/4/2012',6)
insert into @Projects values('TestProject','The really big project for our best customer')
insert into @Activities values('TestProject','000103020200302302322','Demolish the 55th story of the main tower')
insert into @Expenditures values('TestProject','1','Regular Hours')
insert into @Expenditures values('TestProject','2','Overtime Hours')

select  tTH.EmpID "timesheet/@empid"
        ,tTH.EntryYear "timesheet/@entryyear"
        ,tTH.EntryPeriod "timesheet/@entryPeriod"
        ,tTH.AdminNotes "timesheet/@adminnotes"
        ,tTH.UserNotes "timesheet/@empnotes"
        ,tTD.ProjCode "timesheet/project/@projnum"
        ,tP.ProjName "timesheet/project/@projname"
        ,tTD.ActCode "timesheet/project/activity/@actcode"
        ,tA.ActName "timesheet/project/activity/@actname"
        ,tTD.ExpendCode "timesheet/project/activity/expenditure/@expcode"
        ,tE.ExpendName "timesheet/project/activity/expenditure/@expname"
        ,tTD.EntryDate "timesheet/project/activity/expenditure/entry/@entrydate"
        ,tTD.EntryQty "timesheet/project/activity/expenditure/entry/@quantity"
from    @TimesheetHdrs tTH
inner
join	@TimesheetDtls tTD on
            tTD.EmpID=tTH.EmpID
            and tTD.EntryYear=tTH.EntryYear
            and tTD.EntryPeriod=tTH.EntryPeriod
inner
join    @Projects tP on
            tP.ProjCode=tTD.ProjCode
inner
join    @Activities tA on
            tA.ProjCode=tTD.ProjCode
            and tA.ActCode=tTD.ActCode
inner
join    @Expenditures tE on
            tE.ProjCode=tTD.ProjCode
            and tE.ExpendCode=tTD.ExpendCode
order
by      tTD.ProjCode
        ,tTD.ActCode
        ,tTD.EntryDate
        ,tTD.ExpendCode
for xml PATH(''), ROOT

The result of the above code is the following:

Code:

<root>
  <timesheet empid="1" entryyear="2012" entryPeriod="1" adminnotes="These are the admin notes" empnotes="These are the user notes">
    <project projnum="TestProject" projname="The really big project for our best customer">
      <activity actcode="000103020200302302322" actname="Demolish the 55th story of the main tower">
        <expenditure expcode="1" expname="Regular Hours">
          <entry entrydate="2012-01-03" quantity="4.50" /> 
        </expenditure>
      </activity>
    </project>
  </timesheet>
  <timesheet empid="1" entryyear="2012" entryPeriod="1" adminnotes="These are the admin notes" empnotes="These are the user notes">
    <project projnum="TestProject" projname="The really big project for our best customer">
      <activity actcode="000103020200302302322" actname="Demolish the 55th story of the main tower">
        <expenditure expcode="2" expname="Overtime Hours">
          <entry entrydate="2012-01-03" quantity="0.50" /> 
        </expenditure>
      </activity>
    </project>
  </timesheet>
  <timesheet empid="1" entryyear="2012" entryPeriod="1" adminnotes="These are the admin notes" empnotes="These are the user notes">
    <project projnum="TestProject" projname="The really big project for our best customer">
      <activity actcode="000103020200302302322" actname="Demolish the 55th story of the main tower">
        <expenditure expcode="1" expname="Regular Hours">
          <entry entrydate="2012-01-04" quantity="6.00" /> 
        </expenditure>
      </activity>
    </project>
  </timesheet>
</root>

Notice how there is a tremendous amount of redudency in the XML. I was hoping to come up with an XML result of the following, which transmits the same data, without the redundencies.

Code:

<root>
  <timesheet empid="1" entryyear="2012" entryPeriod="1" adminnotes="These are the admin notes" empnotes="These are the user notes">
    <project projnum="TestProject" projname="The really big project for our best customer">
      <activity actcode="000103020200302302322" actname="Demolish the 55th story of the main tower">
        <expenditure expcode="1" expname="Regular Hours">
          <entry entrydate="2012-01-03" quantity="4.50" /> 
          <entry entrydate="2012-01-04" quantity="6.00" /> 
        </expenditure>
        <expenditure expcode="2" expname="Overtime Hours">
          <entry entrydate="2012-01-03" quantity="0.50" /> 
        </expenditure>
      </activity>
    </project>
  </timesheet>
</root>

Where am I going wrong?

Thanks.

homerow · #2 (**permalink**) 02-11-12, 23:17

Try the following, making any adjustments as needed:

Code:

select
  tTH.EmpID "@empid"
 ,tTH.EntryYear "@entryyear"
 ,tTH.EntryPeriod "@entryPeriod"
 ,tTH.AdminNotes "@adminnotes"
 ,tTH.UserNotes "@empnotes"
 ,(select tP.ProjCode "@projnum" ,tP.ProjName "@projname"
    ,(select tA.ActCode "@actcode" ,tA.ActName "@actname"
       ,(select tE.ExpendCode "@expcode" ,tE.ExpendName "@expname"
          ,(select tTD.EntryDate "@entrydate" ,tTD.EntryQty "@quantity"
            from @TimesheetDtls tTD
            where tTD.EmpID = tTH.EmpID
              and tTD.EntryYear = tTH.EntryYear
              and tTD.EntryPeriod = tTH.EntryPeriod
              and tTD.ProjCode = tP.ProjCode
              and tTD.ActCode = tA.ActCode
              and tTD.ExpendCode = tE.ExpendCode
            order by tTD.EntryDate
            for xml path('entry') ,type
           )
         from
           (select distinct ExpendCode from @TimesheetDtls
            where EmpID = tTH.EmpID and EntryYear = tTH.EntryYear and EntryPeriod = tTH.EntryPeriod and ProjCode = tP.ProjCode and ActCode = tA.ActCode
           ) tdE ,@Expenditures tE
         where tE.ProjCode = tP.ProjCode and tE.ExpendCode = tdE.ExpendCode
         order by tE.ExpendCode
         for xml path('expenditure') ,type
        )
      from
        (select distinct ActCode from @TimesheetDtls
         where EmpID = tTH.EmpID and EntryYear = tTH.EntryYear and EntryPeriod = tTH.EntryPeriod and ProjCode = tP.ProjCode
        ) tdA ,@Activities tA
      where tA.ProjCode = tP.ProjCode and tA.ActCode = tdA.ActCode
      order by tA.ActCode
      for xml path('activity') ,type
     )
   from
     (select distinct ProjCode from @TimesheetDtls
      where EmpID = tTH.EmpID and EntryYear = tTH.EntryYear and EntryPeriod = tTH.EntryPeriod
     ) tdP ,@Projects tP
   where tP.ProjCode = tdP.ProjCode
   order by tP.ProjCode
   for xml path('project') ,type
  )
from @TimesheetHdrs tTH
order by tTH.EmpID
for xml path('timesheet') ,root('root') ,type

PracticalProgram · #3 (**permalink**) 02-12-12, 10:28

Thanks homerow.

I was experimenting on Friday and was able to come up with the same result using the EXPLICIT form of the FOR XML, but I think I am going to move back to your PATH example as it seems less contrived.

But bot the EXPLICIT and the PATH form appear to require multiple passes again the originating table. In other words, in my example, the TimesheetDtls table is the focus of four of the nested SELECT statements. If the TimesheetDtls table had 50 million records in it, and I'm taking four passes at it, that does not sound optimum to me.

My solution to this dilemma is to first put the small number of TimesheetDtls records into a table variable and then have the multiple, nested SELECT statements act against that small set of records.

Again, thanks.

Internet Services

Web Development

Digital Marketing

Consumer Tech