XML Editor
Sign up for a WebBoard account Sign Up Keyword Search Search More Options... Options
Chat Rooms Chat Help Help News News Log in to WebBoard Log in Not Logged in
Show tree view Topic
Topic Page 1 2 3 4 5 6 7 8 9 Go to previous topicPrev TopicGo to next topicNext Topic
Postnext
Brad MooreSubject: Transforming XML to XML - grouping
Author: Brad Moore
Date: 29 Nov 2007 12:40 PM
I've been working on this for two days and have now successfully pulled out all my hair... I could use some help.... I am building an Access database that needs to export personnel scheduling information to XML, so it can be imported into another program. I know Access, however, much to my frustration, don't know XML. My output format looks like this....

<qry_DAR_Export>
<LOCATION>6C00: Haldimand County</LOCATION>
<PLATOON>Admin</PLATOON>
<USER_NAME>SMITH, P.G. (Paul)</USER_NAME>
<BADGE_EMP_NO>1234</BADGE_EMP_NO>
<ROSTER_DUTY_CODE>OIC</ROSTER_DUTY_CODE>
<USER_PROFILE_ID>6789</USER_PROFILE_ID>
<YEAR>2008</YEAR>
<SHIFT_x0020_DATE>04/01/08</SHIFT_x0020_DATE>
<ON_DUTY>0815</ON_DUTY>
<OFF_DUTY>1630</OFF_DUTY>
</qry_DAR_Export>
<qry_DAR_Export>
<LOCATION>6C00: Haldimand County</LOCATION>
<PLATOON>Admin</PLATOON>
<USER_NAME>SMITH, P.G. (Paul)</USER_NAME>
<BADGE_EMP_NO>1234</BADGE_EMP_NO>
<ROSTER_DUTY_CODE>OIC</ROSTER_DUTY_CODE>
<USER_PROFILE_ID>6789</USER_PROFILE_ID>
<YEAR>2008</YEAR>
<SHIFT_x0020_DATE>05/01/08</SHIFT_x0020_DATE>
<ABSENCE_REASON_CODE>R</ABSENCE_REASON_CODE>
<ON_DUTY />
<OFF_DUTY />
</qry_DAR_Export>

With one entry for each day of the year. I need to transform it into XML that looks like this....

<USER PLATOON="*Admin*" USER_NAME="*SMITH, P.G. (Paul)*" BADGE_EMP_NO="*1234*" ROSTER_DUTY_CODE="*OIC*" USER_PROFILE_ID="*6789*">
<SCHEDULE>
<SHIFT DATE="*10/11/07*" ABSENCE_REASON_CODE="*R*" ON_DUTY="" OFF_DUTY="" />
<SHIFT DATE="*11/11/07*" ABSENCE_REASON_CODE="*R*" ON_DUTY="" OFF_DUTY="" />
<SHIFT DATE="*12/11/07*" ABSENCE_REASON_CODE="*ST*" ON_DUTY="" OFF_DUTY="" />
<SHIFT DATE="*13/11/07*" ABSENCE_REASON_CODE="" ON_DUTY="*0800*" OFF_DUTY="*1600*" />
<SHIFT DATE="*14/11/07*" ABSENCE_REASON_CODE="" ON_DUTY="*0800*" OFF_DUTY="*1600*" />
<SHIFT DATE="*15/11/07*" ABSENCE_REASON_CODE="" ON_DUTY="*0800*" OFF_DUTY="*1600*" />
<SHIFT DATE="*16/11/07*" ABSENCE_REASON_CODE="" ON_DUTY="*0800*" OFF_DUTY="*1600*" />
<SHIFT DATE="*17/11/07*" ABSENCE_REASON_CODE="*R*" ON_DUTY="" OFF_DUTY="" />
<SHIFT DATE="*18/11/07*" ABSENCE_REASON_CODE="*R*" ON_DUTY="" OFF_DUTY="" />
<SHIFT DATE="*19/11/07*" ABSENCE_REASON_CODE="" ON_DUTY="*0800*" OFF_DUTY="*1600*" />
<SHIFT DATE="*20/11/07*" ABSENCE_REASON_CODE="" ON_DUTY="*0800*" OFF_DUTY="*1600*" />
<SHIFT DATE="*21/11/07*" ABSENCE_REASON_CODE="" ON_DUTY="*0800*" OFF_DUTY="*1600*" />
<SHIFT DATE="*22/11/07*" ABSENCE_REASON_CODE="" ON_DUTY="*0800*" OFF_DUTY="*1600*" />
<SHIFT DATE="*23/11/07*" ABSENCE_REASON_CODE="" ON_DUTY="*0800*" OFF_DUTY="*1600*" />
.
.
.
</SCHEDULE>
</USER>

etc..... Again with one <SHIFT> entry for each day of the year and one <USER> entry for each employee. I can get the transformation to look this way when I export one user from the database, but when I have more than one user, I can't get it to start a new <USER> at the end of the year (i.e - <SHIFT DATE="31/12/07"... for user "SMITH" is immediately followed by <SHIFT DATE="01/01/07".... for user "JONES" with no <USER> tag)....

This is the XSL I have generated with Stylus Studio

<?xml version='1.0' ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:a="http://jus00aas0890/DARReporting/content/admin/roster/RosterSchema.xml">
<xsl:template match="/">
<ROSTER xmlns="http://jus00aas0890/DARReporting/content/admin/roster/RosterSchema.xml">
<xsl:attribute name="LOCATION">
<xsl:value-of select="dataroot/qry_DAR_Export/LOCATION"/>
</xsl:attribute>
<USER>
<xsl:attribute name="PLATOON">
<xsl:value-of select="dataroot/qry_DAR_Export/PLATOON"/>
</xsl:attribute>
<xsl:attribute name="USER_NAME">
<xsl:value-of select="dataroot/qry_DAR_Export/USER_NAME"/>
</xsl:attribute>
<xsl:attribute name="BADGE_EMP_NO">
<xsl:value-of select="dataroot/qry_DAR_Export/BADGE_EMP_NO"/>
</xsl:attribute>
<xsl:attribute name="ROSTER_DUTY_CODE">
<xsl:value-of select="dataroot/qry_DAR_Export/ROSTER_DUTY_CODE"/>
</xsl:attribute>
<xsl:attribute name="USER_PROFILE_ID">
<xsl:value-of select="dataroot/qry_DAR_Export/USER_PROFILE_ID"/>
</xsl:attribute>
<SCHEDULE>
<xsl:for-each select="dataroot/qry_DAR_Export">
<SHIFT>
<xsl:attribute name="DATE">
<xsl:value-of select="SHIFT_x0020_DATE"/>
</xsl:attribute>
<xsl:attribute name="ABSENCE_REASON_CODE">
<xsl:value-of select="ABSENCE_REASON_CODE"/>
</xsl:attribute>
<xsl:attribute name="ON_DUTY">
<xsl:value-of select="ON_DUTY"/>
</xsl:attribute>
<xsl:attribute name="OFF_DUTY">
<xsl:value-of select="OFF_DUTY"/>
</xsl:attribute>
</SHIFT>
</xsl:for-each>
</SCHEDULE>
</USER>
</ROSTER>
</xsl:template>
</xsl:stylesheet>

Any suggestions? I have search the list archives and with every keyword I can think of on the web, to no avail.......
Thanks,
Brad


Posttop
Alberto MassariSubject: Transforming XML to XML - grouping
Author: Alberto Massari
Date: 30 Nov 2007 05:15 AM
Hi Brad,
you need to place the code between <USER> and </USER> inside a loop, that is executed once for every user you have in the exported data.
Have a look at the grouping technique described at http://www.jenitennison.com/xslt/grouping/muenchian.html to see how this can be done.

Hope this helps,
Alberto

 
Topic Page 1 2 3 4 5 6 7 8 9 Go to previous topicPrev TopicGo to next topicNext Topic
Download A Free Trial of Stylus Studio 6 XML Professional Edition Today! Powered by Stylus Studio, the world's leading XML IDE for XML, XSLT, XQuery, XML Schema, DTD, XPath, WSDL, XHTML, SQL/XML, and XML Mapping!  
go

Log In Options

Site Map | Privacy Policy | Terms of Use | Trademarks
Stylus Scoop XML Newsletter:
W3C Member
Stylus Studio® and DataDirect XQuery ™are from DataDirect Technologies, is a registered trademark of Progress Software Corporation, in the U.S. and other countries. © 2004-2016 All Rights Reserved.