×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

XML for Revenue Canada

XML for Revenue Canada

XML for Revenue Canada

(OP)
Hi,

I'm very new to XML and I have to prepare an XML document from Excel spreadsheets for filling T4s according to Revenue Canada's requirement.

http://www.cra-arc.gc.ca/eservices/iref/t4/menu-e.html

Can anybody help me and tell me the steps I should take to prepare that document.

Thanks,

Kaschif

RE: XML for Revenue Canada

Welcome to Tek-Tips.

To do this you will need to use XSLT to translate between the 'native' XML created by Excel (named SpreadsheetML by Microsoft) and the XML demanded by Revenue Canada.  XSLT is designed to perform translations from XML to XML, HTML and text.

There are several tools that can help you build XSLT.  I prefer Stylus Studio.  Others include XML Spy and Cooktop.  Cooktop is free, but as with most 'free' things, it requires a bit more effort on your part to make it work.  Both Stylus Studio and XML Spy have free evaluation periods.  I will tell you the steps using Stylus Studio; YMMV.

Go to the Microsoft web site and download the reference schema for the version of Excel you are using.  These schema describe the XML documents produced by MS Excel.  Stylus Studio can use the Excel schema directly as an input document for building the XSLT transform.  See http://office.microsoft.com/en-us/excel/HA102063961033.aspx as a starting point.

It does not appear that Revenue Canada has an XML schema on its web site for the T4.  Perhaps there is one and I just did not find it.  However, the web site does provide an example T4 XML submission document.  Stylus Studio can use that example document as its output document for building the XSLT transform.

With the input and output document in place, you can use the drag-and-drop XSLT mapper in Stylus Studio to create the mapping from input to output document.  The text file thus produced is an XML document that is your XSLT.

You can then use the XSLT directly in Excel to transform the 'native' SpreadsheetML containing your data into a Revenue Canada T4 XML submission document.

This is the general outline of the steps you will need to take.  Be sure to consult the help files of whatever products you choose to use, as this is nowhere near a detailed specification of the process.  However, if you have problems, please feel free to come back here for help.  As you might discern from other threads, specific questions with details are rather quickly and efficiently answered here.  Vague questions, however, are not our top priority.

Tom Morrison
www.liant.com

RE: XML for Revenue Canada

(OP)
Thanks Tom!!!

RE: XML for Revenue Canada

(OP)
Hi Tom,
I have downloaded the Stylus Studio and XML schema files from Revenue Canada's web site, and already converted my spreadsheets into spreadsheetML. Could you please tell me the steps how to use XSLT mapper in Stylus Studio to create the mapping from input to output document?

Thanks,

Kaschif

RE: XML for Revenue Canada

This is the subject of a Chapter in the Stylus Studio help file, Chapter 5 Creating XSLT Using the XSLT Mapper.

The basic steps are:
  • File, New, XSLT Stylesheet
  • Enter the pathname of your SpreadsheetML document for the Source XML URL
  • Click on 'Set Target Document' (on the right side) and point to Revenue Canada schema.  I think you want layout.xsd
  • Expand the Source Document and Target Document trees
  • Start draggin'-and-droppin'.  Click on an element in your SpreadsheetML layout (displayed in the Source Document tree) and drag across the center to the corresponding element on the Target Document tree.
One special note:  I presume that you will have one Excel row/T4Slip.  In order to get the <xsl:for-each> instruction, you will have to drag from the <Row> (which should be depicted as a repeating element) to the <T4Slip> (which also is depicted as a repeating element).  Search the Stylus Studio help for "Document structure symbols" to see the symbol which depicts repeating elements.

Once you have a map created (or even partially created) you can use the XSLT/Preview Result command to see the output.

A hint:  you will probably want to add an <xsl:output indent="yes"/>  instruction immediately after the <xsl:stylesheet> instruction.  This makes things pretty to look at.

Happy draggin'!   bigsmile

Tom Morrison
www.liant.com

RE: XML for Revenue Canada

(OP)
Thanks again Tom!!!

RE: XML for Revenue Canada

(OP)
Hi Tom,
Thanks for your help, now when I load my SpreadsheetML it doesn't load the contents (for example Column names) in the source tab, it loads generic column names in the source tab. And when I load layout.xsd in the target it loads exactly as described in the Chapter 5 Creating XSLT Using the XSLT Mapper. After loading both files how would I drag and drop those generic columns from source to the actual column schema names in the target tab?

kaschif

http://www.mediafire.com/?6lz4xosgnm9

RE: XML for Revenue Canada

Ok, I tried the XML Mapping feature of Excel, and that didn't work.  So what you must rely on is the fact that the <Cell> elements for each <Row> are output in the same order.  I crafted a dummy worksheet (reduced in size for the purpose of this example):

CODE --> SpreadsheetML Document

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40"
 xmlns:x2="http://schemas.microsoft.com/office/excel/2003/xml">
 <Worksheet ss:Name="Sheet1">
  <Names>
   <NamedRange ss:Name="_FilterDatabase" ss:RefersTo="=Sheet1!R1C4:R4C6"
    ss:Hidden="1"/>
  </Names>
  <Table ss:ExpandedColumnCount="6" ss:ExpandedRowCount="4" x:FullColumns="1"
   x:FullRows="1">
   <Column ss:AutoFitWidth="0" ss:Width="33"/>
   <Column ss:Index="4" ss:AutoFitWidth="0" ss:Width="37.5"/>
   <Column ss:AutoFitWidth="0" ss:Width="36"/>
   <Column ss:AutoFitWidth="0" ss:Width="36.75"/>
   <Row>
    <Cell ss:StyleID="s22"><Data ss:Type="String">Bo</Data></Cell>
    <Cell ss:Index="4" ss:StyleID="s22"><Data ss:Type="Number">1</Data></Cell>
    <Cell ss:StyleID="s22"><Data ss:Type="Number">3</Data></Cell>
    <Cell ss:StyleID="s22"><Data ss:Type="Number">2</Data></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID="s22"><Data ss:Type="String">Sno</Data></Cell>
    <Cell ss:Index="4" ss:StyleID="s22"><Data ss:Type="Number">6</Data></Cell>
    <Cell ss:StyleID="s22"><Data ss:Type="Number">4</Data></Cell>
    <Cell ss:StyleID="s22"><Data ss:Type="Number">5</Data></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID="s22"><Data ss:Type="String">Glo</Data></Cell>
    <Cell ss:Index="4" ss:StyleID="s22"><Data ss:Type="Number">8</Data></Cell>
    <Cell ss:StyleID="s22"><Data ss:Type="Number">7</Data></Cell>
    <Cell ss:StyleID="s22"><Data ss:Type="Number">9</Data></Cell>
   </Row>
  </Table>
 </Worksheet>
</Workbook>

CODE --> XSLT

<?xml version='1.0' ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:a="urn:schemas-microsoft-com:office:spreadsheet" exclude-result-prefixes="a">
<xsl:output indent="yes" />
    <xsl:template match="/">
        <Submission>
            <Return>
                <T4>
                    <xsl:for-each select="a:Workbook/a:Worksheet/a:Table/a:Row">
                        <T4Slip>
                            <EMPE_NM>
                                <gvn_nm>
                                    <xsl:value-of select="a:Cell[1]/a:Data"/>
                                </gvn_nm>
                            </EMPE_NM>
                            <empe_nbr>
                                <xsl:value-of select="a:Cell[2]/a:Data"/>
                            </empe_nbr>
                            <rpp_dpsp_rgst_nbr>
                                <xsl:value-of select="a:Cell[3]/a:Data"/>
                            </rpp_dpsp_rgst_nbr>
                            <ei_xmpt_cd>
                                <xsl:value-of select="a:Cell[4]/a:Data"/>
                            </ei_xmpt_cd>
                        </T4Slip>
                    </xsl:for-each>
                </T4>
            </Return>
        </Submission>
    </xsl:template>
</xsl:stylesheet>

CODE --> Output

<?xml version='1.0' ?>
<Submission>
  <Return>
    <T4>
      <T4Slip>
        <EMPE_NM>
          <gvn_nm>Bo</gvn_nm>
        </EMPE_NM>
        <empe_nbr>1</empe_nbr>
        <rpp_dpsp_rgst_nbr>3</rpp_dpsp_rgst_nbr>
        <ei_xmpt_cd>2</ei_xmpt_cd>
      </T4Slip>
      <T4Slip>
        <EMPE_NM>
          <gvn_nm>Sno</gvn_nm>
        </EMPE_NM>
        <empe_nbr>6</empe_nbr>
        <rpp_dpsp_rgst_nbr>4</rpp_dpsp_rgst_nbr>
        <ei_xmpt_cd>5</ei_xmpt_cd>
      </T4Slip>
      <T4Slip>
        <EMPE_NM>
          <gvn_nm>Glo</gvn_nm>
        </EMPE_NM>
        <empe_nbr>8</empe_nbr>
        <rpp_dpsp_rgst_nbr>7</rpp_dpsp_rgst_nbr>
        <ei_xmpt_cd>9</ei_xmpt_cd>
      </T4Slip>
    </T4>
  </Return>
</Submission>

I did this in the map view by dragging the <Data> element to the T4 element then adding the predicate (e.g. the thing that looks a lot like an array subscript [1] above) to select the correct <Cell> within the row.  The resulting map had four lines originating in the <Data> element on the left (input) tree.

I am guessing that you have a lot of rows, one row/employee.

Sidebar question:  Is the data really originating in an Excel spreadsheet?

Tom Morrison
www.liant.com

RE: XML for Revenue Canada

(OP)
For most of the spreadsheet data is coming from SQL Server, but there are some spreadsheet that have data of their own.

Kaschif

RE: XML for Revenue Canada

Kaschif, did you get something working?

Tom Morrison
www.liant.com

RE: XML for Revenue Canada

I had a bit of time to burn, so I did a small enhancement.  The following XSL expects the column headers (names) to appear in the first row, and the repeated data in the 2nd through nth rows.  The first part of the stylesheet sets variables for the column index values that contain the data of interest.  (Note that the output of the stylesheet has nothing to do with Revenue Canada data structures.  This is merely to demonstrate a technique that is a bit more mnemonic that literal column index values.)

CODE --> XSL

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                              xmlns:a="urn:schemas-microsoft-com:office:spreadsheet"
                              exclude-result-prefixes="a">
  <xsl:output indent="yes"/>
  <xsl:template match="/">
    <xsl:variable name="colName" select="count(a:Workbook/a:Worksheet/a:Table/a:Row[1]/a:Cell/a:Data[text()='Name']/../preceding-sibling::a:Cell) + 1"/>
    <xsl:variable name="colGross" select="count(a:Workbook/a:Worksheet/a:Table/a:Row[1]/a:Cell/a:Data[text()='Gross']/../preceding-sibling::a:Cell) + 1"/>
    <xsl:variable name="colDiscount" select="count(a:Workbook/a:Worksheet/a:Table/a:Row[1]/a:Cell/a:Data[text()='Discount']/../preceding-sibling::a:Cell) + 1"/>
    <xsl:variable name="colNet" select="count(a:Workbook/a:Worksheet/a:Table/a:Row[1]/a:Cell/a:Data[text()='Net']/../preceding-sibling::a:Cell) + 1"/>
    <root>
      <import-from-excel>
        <data-count><xsl:value-of select="count(a:Workbook/a:Worksheet/a:Table/a:Row) - 1"/></data-count>
        <xsl:for-each select="a:Workbook/a:Worksheet/a:Table/a:Row[position() &gt; 1]">
          <data-array>
            <data-name>
              <xsl:value-of select="a:Cell[$colName]/a:Data"/>
            </data-name>
            <data-gross>
              <xsl:value-of select="a:Cell[$colGross]/a:Data"/>
            </data-gross>
            <data-discount>
              <xsl:value-of select="a:Cell[$colDiscount]/a:Data"/>
            </data-discount>
            <data-net>
              <xsl:value-of select="a:Cell[$colNet]/a:Data"/>
            </data-net>
          </data-array>
        </xsl:for-each>
      </import-from-excel>
    </root>
  </xsl:template>
</xsl:stylesheet>

CODE --> Sample XML Spreadsheet output

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <LastAuthor>Tom Morrison</LastAuthor>
  <Created>1996-10-14T23:33:28Z</Created>
  <LastSaved>2008-02-08T19:45:47Z</LastSaved>
  <Version>11.6568</Version>
 </DocumentProperties>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>9300</WindowHeight>
  <WindowWidth>15135</WindowWidth>
  <WindowTopX>120</WindowTopX>
  <WindowTopY>120</WindowTopY>
  <AcceptLabelsInFormulas/>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="s21">
   <NumberFormat ss:Format="Fixed"/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="4" ss:ExpandedRowCount="5" x:FullColumns="1"
   x:FullRows="1">
   <Column ss:AutoFitWidth="0" ss:Width="129.75"/>
   <Column ss:AutoFitWidth="0" ss:Width="68.25"/>
   <Row>
    <Cell><Data ss:Type="String">Name</Data></Cell>
    <Cell><Data ss:Type="String">Gross</Data></Cell>
    <Cell><Data ss:Type="String">Discount</Data></Cell>
    <Cell><Data ss:Type="String">Net</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">A Smith</Data></Cell>
    <Cell><Data ss:Type="Number">1000</Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="Number">0.22</Data></Cell>
    <Cell ss:StyleID="s21" ss:Formula="=ROUND((RC[-2]*(1-RC[-1])),2)"><Data
      ss:Type="Number">780</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">B Smythe</Data></Cell>
    <Cell><Data ss:Type="Number">2000</Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="Number">0.33</Data></Cell>
    <Cell ss:StyleID="s21" ss:Formula="=ROUND((RC[-2]*(1-RC[-1])),2)"><Data
      ss:Type="Number">1340</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">C Williams</Data></Cell>
    <Cell><Data ss:Type="Number">4567</Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="Number">0.11</Data></Cell>
    <Cell ss:StyleID="s21" ss:Formula="=ROUND((RC[-2]*(1-RC[-1])),2)"><Data
      ss:Type="Number">4064.63</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">D Wyte</Data></Cell>
    <Cell><Data ss:Type="Number">9876</Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="Number">0.05</Data></Cell>
    <Cell ss:StyleID="s21" ss:Formula="=ROUND((RC[-2]*(1-RC[-1])),2)"><Data
      ss:Type="Number">9382.2</Data></Cell>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>1</ActiveRow>
     <ActiveCol>3</ActiveCol>
     <RangeSelection>R2C4:R5C4</RangeSelection>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>

CODE --> Output

<root>
  <import-from-excel>
    <data-count>4</data-count>
    <data-array>
      <data-name>A Smith</data-name>
      <data-gross>1000</data-gross>
      <data-discount>0.22</data-discount>
      <data-net>780</data-net>
    </data-array>
    <data-array>
      <data-name>B Smythe</data-name>
      <data-gross>2000</data-gross>
      <data-discount>0.33</data-discount>
      <data-net>1340</data-net>
    </data-array>
    <data-array>
      <data-name>C Williams</data-name>
      <data-gross>4567</data-gross>
      <data-discount>0.11</data-discount>
      <data-net>4064.63</data-net>
    </data-array>
    <data-array>
      <data-name>D Wyte</data-name>
      <data-gross>9876</data-gross>
      <data-discount>0.05</data-discount>
      <data-net>9382.2</data-net>
    </data-array>
  </import-from-excel>
</root>

Tom Morrison
www.liant.com

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close