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/m enu-e.html
Can anybody help me and tell me the steps I should take to prepare that document.
Thanks,
Kaschif
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
Can anybody help me and tell me the steps I should take to prepare that document.
Thanks,
Kaschif
RE: XML for Revenue Canada
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://of
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
RE: XML for Revenue Canada
http:/
RE: XML for Revenue Canada
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
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'!
Tom Morrison
www.liant.com
RE: XML for Revenue Canada
RE: XML for Revenue Canada
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
CODE --> SpreadsheetML Document
<?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
<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
<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
Kaschif
RE: XML for Revenue Canada
Tom Morrison
www.liant.com
RE: XML for Revenue Canada
CODE --> XSL
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() > 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
<?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
<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