Friday, December 18, 2009

Forray into xsl

So, this isn't going to be very pretty, but I just wanted to document it somewhere, and it certainly may not be the best way to go about it.  I just don't have a lot of XML experience so I'm happy I got the job done pretty quickly with some research.  I'll add more explanation when I have time.

I created an Access DB that I needed to convert to XML.  Here's the DB diagram.





Here's what the test data looked like after exporting the DB to xml:

<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlns:od="urn:schemas-microsoft-com:officedata" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="arb_UploadingCompany.xsd" generated="2009-12-18T15:22:05">
<arb:UploadingCompany>
<UploadingCompanyID>1</UploadingCompanyID>
<arb:Trucks>
<TrucksID>1</TrucksID>
<UploadingCompanyID>1</UploadingCompanyID>
<arb:Truck>
<TruckID>1</TruckID>
<TrucksID>1</TrucksID>
<arb:RequestForLabel>true</arb:RequestForLabel>
<arb:Compliance>
<ComplianceID>1</ComplianceID>
<TruckID>1</TruckID>
<arb:ModelYr2005-2006>Y</arb:ModelYr2005-2006>
</arb:Compliance>
<arb:ComplianceStatus>
<ComplianceStatusID>1</ComplianceStatusID>
<TruckID>1</TruckID>
<arb:Status>something</arb:Status>
<arb:ThroughDate>something</arb:ThroughDate>
</arb:ComplianceStatus>
<arb:Engine>
<EngineID>1</EngineID>
<TruckID>1</TruckID>
<arb:MfrName>Cummins</arb:MfrName>
<arb:FamilyName/>
<arb:ModelYear>2007</arb:ModelYear>
</arb:Engine>
<arb:SystemMessage>
<SystemMessageID>1</SystemMessageID>
<TruckID>1</TruckID>
<arb:Code>1234561</arb:Code>
<arb:Message>something</arb:Message>
</arb:SystemMessage>
<arb:Vehicle>
<VehicleID>1</VehicleID>
<TruckID>1</TruckID>
<arb:MfrName>Freightliner</arb:MfrName>
<arb:ModelYear>2007</arb:ModelYear>
<arb:VIN>12345123451234512</arb:VIN>
<arb:License>
<LicenseID>1</LicenseID>
<VehicleID>1</VehicleID>
<arb:PlateNum>123456</arb:PlateNum>
<arb:LicenseState-US>Oregon</arb:LicenseState-US>
</arb:License>
</arb:Vehicle>
</arb:Truck>
<arb:Truck>
<TruckID>2</TruckID>
<TrucksID>1</TrucksID>
<arb:RequestForLabel>true</arb:RequestForLabel>
<arb:Compliance>
<ComplianceID>2</ComplianceID>
<TruckID>2</TruckID>
<arb:RegistrationOnly>Y</arb:RegistrationOnly>
</arb:Compliance>
<arb:Engine>
<EngineID>2</EngineID>
<TruckID>2</TruckID>
<arb:MfrName>Cummins</arb:MfrName>
<arb:FamilyName>SomeFamily</arb:FamilyName>
<arb:ModelYear>2006</arb:ModelYear>
</arb:Engine>
<arb:Vehicle>
<VehicleID>2</VehicleID>
<TruckID>2</TruckID>
<arb:MfrName>Freightliner</arb:MfrName>
<arb:ModelYear>2006</arb:ModelYear>
<arb:VIN>12345123451234512</arb:VIN>
<arb:License>
<LicenseID>2</LicenseID>
<VehicleID>2</VehicleID>
<arb:PlateNum>123456</arb:PlateNum>
<arb:LicenseState-US>Alabama</arb:LicenseState-US>
</arb:License>
</arb:Vehicle>
</arb:Truck>
</arb:Trucks>
<arb:CompanyProfile>
<CompanyProfileID>1</CompanyProfileID>
<UploadingCompanyID>1</UploadingCompanyID>
<arb:CompanyName>Con-way</arb:CompanyName>
<arb:Address>
<AddressID>1</AddressID>
<CompanyProfileID>1</CompanyProfileID>
<arb:US-Address>
<US-AddressID>1</US-AddressID>
<AddressID>1</AddressID>
<arb:Line1>xxxxxx</arb:Line1>
<arb:City>Portland</arb:City>
<arb:State>Oregon</arb:State>
<arb:Zip>97202</arb:Zip>
</arb:US-Address>
</arb:Address>
<arb:Contact>
<ContactID>1</ContactID>
<CompanyProfileID>1</CompanyProfileID>
<arb:FirstName>Kevin</arb:FirstName>
<arb:LastName>Ellis</arb:LastName>
<arb:Email>ellis.kevin@xxxxxx.com</arb:Email>
</arb:Contact>
<arb:Phone>
<PhoneID>1</PhoneID>
<CompanyProfileID>1</CompanyProfileID>
<arb:US-Phone>123-456-7890</arb:US-Phone>
</arb:Phone>
<arb:TaxId>
<TaxIdID>1</TaxIdID>
<CompanyProfileID>1</CompanyProfileID>
<arb:USTaxId>12-3456789</arb:USTaxId>
</arb:TaxId>
</arb:CompanyProfile>
</arb:UploadingCompany>
</dataroot>


This is what the data needed to look like to send it to the vendor:

<?xml version="1.0" encoding="utf-8"?>
<arb:ARBER arb:SchemaVersion="1.7" xmlns:arb="http://www.arb.ca.gov/arber/batch/arber_schema_v1.7" xsi:schemaLocation="http://www.arb.ca.gov/arber/batch/arber_schema_v1.7 file:///C:/Users/sysadmin/Desktop/Arber/Arber/arber_schema_v1.7.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<arb:UploadingCompany>
<arb:CompanyProfile>
<arb:CompanyName>Con-way</arb:CompanyName>
<arb:TaxId>
<arb:USTaxId>12-3456789</arb:USTaxId>
</arb:TaxId>
<arb:Phone>
<arb:US-Phone>123-456-7890</arb:US-Phone>
</arb:Phone>
<arb:Address>
<arb:US-Address>
<arb:Line1>xxx</arb:Line1>
<arb:City>Portland</arb:City>
<arb:State>Oregon</arb:State>
<arb:Zip>97202</arb:Zip>
</arb:US-Address>
</arb:Address>
<arb:Contact>
<arb:FirstName>Kevin</arb:FirstName>
<arb:LastName>Ellis</arb:LastName>
<arb:Email>ellis.kevin@xxxxxx.com</arb:Email>
</arb:Contact>
</arb:CompanyProfile>
<arb:Trucks>
<arb:Truck>
<arb:Vehicle>
<arb:MfrName>Freightliner</arb:MfrName>
<arb:ModelYear>2007</arb:ModelYear>
<arb:VIN>12345123451234512</arb:VIN>
<arb:License>
<arb:PlateNum>123456</arb:PlateNum>
<arb:LicenseState-US>Oregon</arb:LicenseState-US>
</arb:License>
</arb:Vehicle>
<arb:Engine>
<arb:MfrName>Cummins</arb:MfrName>
<arb:FamilyName/>
<arb:ModelYear>2007</arb:ModelYear>
</arb:Engine>
<arb:Compliance>
<arb:ModelYr2005-2006>Y</arb:ModelYr2005-2006>
</arb:Compliance>
<arb:RequestForLabel>true</arb:RequestForLabel>
</arb:Truck>
<arb:Truck>
<arb:Vehicle>
<arb:MfrName>Freightliner</arb:MfrName>
<arb:ModelYear>2006</arb:ModelYear>
<arb:VIN>12345123451234512</arb:VIN>
<arb:License>
<arb:PlateNum>123456</arb:PlateNum>
<arb:LicenseState-US>Alabama</arb:LicenseState-US>
</arb:License>
</arb:Vehicle>
<arb:Engine>
<arb:MfrName>Cummins</arb:MfrName>
<arb:FamilyName>SomeFamily</arb:FamilyName>
<arb:ModelYear>2006</arb:ModelYear>
</arb:Engine>
<arb:Compliance>
<arb:RegistrationOnly>Y</arb:RegistrationOnly>
</arb:Compliance>
<arb:RequestForLabel>true</arb:RequestForLabel>
</arb:Truck>
</arb:Trucks>
</arb:UploadingCompany>
</arb:ARBER>



Here's the xsl I needed to write to get it there:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<arb:ARBER arb:SchemaVersion="1.7" xmlns:arb="http://www.arb.ca.gov/arber/batch/arber_schema_v1.7">
<arb:UploadingCompany>
<xsl:for-each select="dataroot/arb:UploadingCompany/arb:CompanyProfile">
<arb:CompanyProfile>
<xsl:copy-of select="arb:CompanyName"/>
<xsl:for-each select="arb:TaxId">
<arb:TaxId>
<xsl:copy-of select="arb:USTaxId"/>
</arb:TaxId>
</xsl:for-each>
<xsl:for-each select="arb:Phone">
<arb:Phone>
<xsl:copy-of select="arb:US-Phone"/>
</arb:Phone>
</xsl:for-each>
<xsl:for-each select="arb:Address">
<arb:Address>
<xsl:for-each select="arb:US-Address">
<arb:US-Address>
<xsl:copy-of select="arb:Line1"/>
<xsl:copy-of select="arb:City"/>
<xsl:copy-of select="arb:State"/>
<xsl:copy-of select="arb:Zip"/>
</arb:US-Address>
</xsl:for-each>
</arb:Address>
</xsl:for-each>
<xsl:for-each select="arb:Contact">
<arb:Contact>
<xsl:copy-of select="arb:FirstName"/>
<xsl:copy-of select="arb:LastName"/>
<xsl:copy-of select="arb:Title"/>
<xsl:copy-of select="arb:Email"/>
</arb:Contact>
</xsl:for-each>
</arb:CompanyProfile>
</xsl:for-each>
<xsl:for-each select="dataroot/arb:UploadingCompany/arb:Trucks">
<arb:Trucks>
<xsl:for-each select="arb:Truck">
<arb:Truck>
<xsl:for-each select="arb:Vehicle">
<arb:Vehicle>
<xsl:copy-of select="arb:MfrName"/>
<xsl:copy-of select="arb:ModelYear"/>
<xsl:copy-of select="arb:VIN"/>
<xsl:for-each select="arb:License">
<arb:License>
<xsl:copy-of select="arb:PlateNum"/>
<xsl:copy-of select="arb:LicenseState-US"/>
</arb:License>
</xsl:for-each>
</arb:Vehicle>
</xsl:for-each>
<xsl:for-each select="arb:Engine">
<arb:Engine>
<xsl:copy-of select="arb:MfrName"/>
<xsl:copy-of select="arb:FamilyName"/>
<xsl:copy-of select="arb:Model"/>
<xsl:copy-of select="arb:ModelYear"/>
</arb:Engine>
</xsl:for-each>
<xsl:for-each select="arb:Compliance">
<arb:Compliance>
<xsl:if test="arb:RegistrationOnly">
<xsl:copy-of select="arb:RegistrationOnly"/>
</xsl:if>
<xsl:if test="arb:ModelYr2004">
<xsl:copy-of select="arb:ModelYr2004"/>
</xsl:if>
<xsl:if test="arb:ModelYr2005-2006">
<xsl:copy-of select="arb:ModelYr2005-2006"/>
</xsl:if>
<xsl:if test="arb:ModelYr2007orAbove">
<xsl:copy-of select="arb:ModelYr2007orAbove"/>
</xsl:if>
</arb:Compliance>
</xsl:for-each>
<xsl:copy-of select="arb:RequestForLabel"/>
</arb:Truck>
</xsl:for-each>
</arb:Trucks>
</xsl:for-each>
</arb:UploadingCompany>
</arb:ARBER>
</xsl:template>
</xsl:stylesheet>

1 comment:

Issam said...

Hi kev
I happened to bounce on your blog while searching for ways to Implement OpenSaml in java...
Your blog - http://kevnls.blogspot.com/2009/07/processing-saml-in-java-using-opensaml.html gave me valuable insight... Sadly I have still not been able to figure out...
Please let me know if there is any way I can contact you.
my email - issamsiddique@gmail.com