XML for Revenue Canada

[Kaschif]

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

 

[k5tm]

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

http://www.liant.com

 

[Kaschif]

Thanks Tom!!!

 

[Kaschif]

BTW, schema files are located at

http://www.cra-arc.gc.ca/eservices/magmedia/programmers/xml-e.html

 

[Kaschif]

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

 

[k5tm]

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:[ul][li]File, New, XSLT Stylesheet[/li]
[li]Enter the pathname of your SpreadsheetML document for the Source XML URL[/li]
[li]Click on 'Set Target Document' (on the right side) and point to Revenue Canada schema. I think you want layout.xsd[/li]
[li]Expand the Source Document and Target Document trees[/li]
[li]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.[/li][/ul]

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 <xslutput indent="yes"/> instruction immediately after the <xsl:stylesheet> instruction. This makes things pretty to look at.

Happy draggin'!

Tom Morrison

http://www.liant.com

 

[Kaschif]

Thanks again Tom!!!

 

[Kaschif]

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

 

[k5tm]

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):

<?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="[URL unfurl="true"]http://www.w3.org/TR/REC-html40"[/URL]
 xmlns:x2="[URL unfurl="true"]http://schemas.microsoft.com/office/excel/2003/xml">[/URL]
 <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>

<?xml version='1.0' ?>
<xsl:stylesheet version="1.0" xmlns:xsl="[URL unfurl="true"]http://www.w3.org/1999/XSL/Transform"[/URL] 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[COLOR=white blue][1][/color]/a:Data"/>
								</gvn_nm>
							</EMPE_NM>
							<empe_nbr>
								<xsl:value-of select="a:Cell[COLOR=white blue][2][/color]/a:Data"/>
							</empe_nbr>
							<rpp_dpsp_rgst_nbr>
								<xsl:value-of select="a:Cell[COLOR=white blue][3][/color]/a:Data"/>
							</rpp_dpsp_rgst_nbr>
							<ei_xmpt_cd>
								<xsl:value-of select="a:Cell[COLOR=white blue][4][/color]/a:Data"/>
							</ei_xmpt_cd>
						</T4Slip>
					</xsl:for-each>
				</T4>
			</Return>
		</Submission>
	</xsl:template>
</xsl:stylesheet>

<?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 [COLOR=white blue][1][/color] 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

http://www.liant.com

 

[Kaschif]

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

Kaschif

 

[k5tm]

Kaschif, did you get something working?

Tom Morrison

http://www.liant.com

 

[k5tm]

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

[small]<xsl:stylesheet version="1.0" xmlns:xsl="[URL unfurl="true"]http://www.w3.org/1999/XSL/Transform"[/URL] 
                              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>[/small]

[small]<?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="[URL unfurl="true"]http://www.w3.org/TR/REC-html40">[/URL]
 <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>[/small]

[small]<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>[/small]

Tom Morrison

http://www.liant.com