Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601

    Unanswered: Help w/reducing redundencies in XML output

    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.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  2. #2
    Join Date
    Sep 2011
    Location
    Greenville, SC USA
    Posts
    34

    RE: Help w/reducing redundencies in XML output

    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

  3. #3
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    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.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •