Custom Project Applying a Stylesheet to an XML Data File with the TransformXML Method

This custom project uses the copy of the Northwind sample database you created in Hands-On 29-8.

Part 1: Creating a Custom Stylesheet for Transforming an XML Source File into Another XML Data File

1. Open Notepad and enter the statements as shown below. <?xml version="L0"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:output method="xml" indent="yes"/> <xsl:template match="/"> <dataroot>

<xsl:for-each select="//Employees"> <Extensions> <LastName>

<xsl:value-of select="LastName" /> </LastName> <FirstName>

<xsl:value-of select="FirstName" /> </FirstName> <Extension>

<xsl:value-of select="Extension" /> </Extension> </Extensions> </xsl:for-each> </dataroot> </xsl:template> </xsl:stylesheet>

2. Save the file as Extensions.xsl in the Learn_XML folder. You must type the .xsl file extension to ensure that the file is not saved as text.

3. Close Notepad.

Take a look at the stylesheet shown above and notice that we have asked the XSL processor to produce the output in XML format:

<xsl:output method="xml" indent="yes"/>

Next, we used the following instruction:

<xsl:template match="/">

This instruction defines a template for the entire document. The special pattern "/" in the match attribute tells the XSL processor that this is a template for the document root.

Taking Your VBA Programming Skills to the Web

Because each XML document must have a root node, we proceeded to define <dataroot> as the document root. You can use any name you want for this purpose.

Next, we told the XSL processor to get all the Employees nodes from the source XML data file:

<xsl:for-each select="//Employees">

The first forward slash in the above instruction represents the XML document root. This is the same as:

<xsl:for-each select="dataroot/Employees">

Next, we proceed to extract data from the required nodes. We are only interested in three columns from the source XML data file: FirstName, LastName, and Extension. We create the necessary elements using the <xsl:value-of> tag with the select attribute specifying the element name:

<LastName>

<xsl:value-of select="LastName" /> </LastName> <FirstName>

<xsl:value-of select="FirstName" /> </FirstName> <Extension>

<xsl:value-of select="Extension" /> </Extension>

We tell the XSL processor to place the defined elements under the <Extensions> node. When importing the resulting XML file to Access, Access will create an Extensions table with three columns: LastName, FirstName, and Extension. You can use any name you want when specifying the container node for your elements.

To finish off the stylesheet, we must write the necessary closing tags:

</xsl:for-each> </dataroot> </xsl:template> </xsl:stylesheet>

Now that you've got the stylesheet for our transformation, you can write a VBA procedure to export the source data and perform the transformation.

Part 2: Writing a VBA Procedure to Export and Transform Data

1. Ensure that the copy of the Northwind sample database you created in Hands-On 29-8 is open.

2. In the Database window, choose Tools | Macro | Visual Basic Editor.

3. In the Visual Basic Editor window, choose Insert | Module to add a standard module to the current VBA project.

4. In the module's Code window, enter the Transform_Employees procedure as shown below.

Part V

Sub Transform_Employees()

' use the ExportXML method to create a source XML data file

Application.ExportXML ObjectType:=acExportTable, _ DataSource:="Employees", _ DataTarget:="C:\Learn_XML\InternalContacts.xml"

MsgBox "The export operation completed successfully."

' use the TransformXML method to apply the stylesheet that transforms ' the source XML data file into another XML data file

Application.TransformXML DataSource:="C:\Learn_XML\InternalContacts.xml", _ TransformSource:="C:\Learn_XML\Extensions.xsl", _ OutputTarget:="C:\Learn_XML\EmpExtensions.xml", _ WellFormedXMLOutput:=False

MsgBox "The transform operation completed successfully." End Sub

5. Run the Transform_Employees procedure.

The first part of the above procedure exports the Employees table from the Northwind database to an XML file named InternalContacts.xml. The second part of this procedure applies the Extensions.xsl stylesheet prepared in Part 1 of this custom project to the InternalContacts.xml data file. The resulting XML document after the transformation is named EmpExtensions.xml. A portion of this file is shown in Figure 29-18.

Figure 29-18: Partial contents of the EmpExtensions.xml file.

il C:\Learn_XMIAEmpExtensions,xml - Microsoft Int..

101

File Edit View Fever ites Toots Help

Qe«* - Q - g [¿] Search

Favorites

ftddiw. C:\Learn_XML\EmpE5<tensions, Krnl

a

Go I Inks "

<?xml version=u 1.0" ?>

A

- <dataroot>

- <Extensions>

<LastName>Dovolio</LastName>

<FirstNa me> Nan cy </F irs t Nam e >

<Extensi on>5467</Ext ensi o n>

</Exter»sions>

- <Extensions>

<LastName> F ul le r</La s t Nam e >

<FirstNa rne> An d re w </FirstNa me>

<Extensi on>3457</Ex t ensi o n>

</Extensions>

- <Extensions>

<Las tName> Le ve rli ng</La s t N am e >

<FirstNa me> J a net </F irst Narne>

<Extension>3355</Extension>

</Extensions>

- <Extensions>

<LastName> P eacockc/La s t N am e >

<FirstNa me> Ma rg aret </F irs t Nam e >

<Extension>5176</Extension>

</Extensions>

- <Extensions>

<LastName> Buchanan </LastN a rne>

llfi'l

-j My Computer

After transforming our source XML data file into another XML document, you can bring it into Access with the File | Get External Data | Import command.

Taking Your VBA Programming Skills to the Web

Part 3: Importing the Transformed XML Data File to Access

1. In the Database window, choose File | Get External Data | Import.

2. In the Import dialog box select the EmpExtensions.xml file in the Learn_XML folder and click the Import button. Access displays the Import XML dialog box shown in Figure 29-19.

Figure 29-19: When you import the EmpExtensions.xml file, Access creates a new table named Extensions.

3. In the Import XML dialog box, click OK to perform the import.

4. Click OK to clear the confirmation message.

5. In the Database window, notice the appearance of the Extensions table. Open the Extensions table to view the following contents:

Extensions

LastName

FirstName

Extension

Davolio

Nancy

5467

Fuller

Andrew

3457

Leverling

Janet

3355

Peacock

Margaret

5176

Buchanan

Steven

3453

Suyama

Michael

428

King

Robert

465

Callahan

Laura

2344

Dodsworth

Anne

452

6. Close the Extensions table.

A nice thing about XSLT transformations is that you can apply different stylesheets to the same XML data file to create and view the resulting document in different formats.

For example, let's assume that in the Extensions table you'd like to combine the LastName and FirstName columns into one column and sort the data by last name. You could create and apply the following Extensions_ SortByEmp.xsl stylesheet to the InternalContacts.xml file to get the desired XML ouput:

Part V

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:output method="xml" indent="yes"/> <xsl:template match="/"> <dataroot>

<xsl:apply-templates select="dataroot/Employees"> <xsl:sort select="LastName" order="ascending" /> </xsl:apply-templates> </dataroot> </xsl:template>

<xsl:template match="//Employees"> <Extensions> <FullName> <xsl:value-of select="LastName" /> <xsl:text> </xsl:text> <xsl:value-of select="FirstName" /> </FullName> <Extension>

<xsl:value-of select="Extension" /> </Extension> </Extensions> </xsl:template>

</xsl:stylesheet>

The above stylesheet uses the <xsl:apply-templates> tag to tell the XSL processor to select the child elements of the dataroot/Employees node, and for each child element, find in the stylesheet the matching template rule and process it:

<xsl:apply-templates select="dataroot/Employees">

<xsl:sort select="LastName" order="ascending" /> </xsl:apply-templates>

The <xsl:sort> tag specifies how the resulting XML document should be sorted. The select attribute of this tag is set to "LastName", indicating that the file should be sorted by the LastName element. The order attribute defines the sort order as ascending.

Next, in this stylesheet you can see the template rule that begins with the <xsl:template> tag. Its match attribute specifies which nodes in the document tree the template rule should process:

<xsl:template match="//Employees">

The "//Employees" expression in the match attribute is equivalent to "dataroot/Employees".

Next, you need to define the document node in the output file as Extensions, and proceed to define its child elements as FullName and Extension:

<Extensions> <FullName> <xsl:value-of select="LastName" /> <xsl:text>, </xsl:text> <xsl:value-of select="FirstName" />

Taking Your VBA Programming Skills to the Web

</FullName> <Extension>

<xsl:value-of select="Extension" /> </Extension> </Extensions>

Because the FullName element should include the last name of the employee followed by a space and the first name, you can obtain the values of these fields with the <xsl:value-of> tag and use the <xsl:text> </xsl:text> tag pair to output a comma followed by a space between the last name and first name. Since there is nothing special about the Extension element, you can simply use the <xsl:value-of> tag to obtain this element's value.

Finally, complete the template and the stylesheet with the required closing tags:

</Extensions> </xsl:template>

</xsl:stylesheet>

To apply the above stylesheet to the source XML file, you could write the following VBA procedure:

Sub Transform_ContactsSort()

' use the ExportXML method to create a source XML data file Application.ExportXML ObjectType:=acExportTable, _ DataSource:="Employees", _ DataTarget:="C:\Learn_XML\InternalContacts.xml"

MsgBox "The export operation completed successfully."

' use the TransformXML method to apply the stylesheet that transforms ' the source XML data file into another XML data file

Application.TransformXML DataSource:="C:\Learn_XML\InternalContacts.xml", _ TransformSource:="C:\Learn_XML\Extensions_SortByEmp.xsl", _ OutputTarget:="C:\Learn_XML\EmpExtensions.xml", _ WellFormedXMLOutput: = False

MsgBox "The transform operation completed successfully." End Sub

After importing the EmpExtensions.xml file to Access, you should see the Extensions1 table in the Database window. When opened, this table displays a sorted list of employees with their extensions:

Extensions!

FullName

Extension

Buchanan, Steven

3453

Callahan, Laura

2344

Davolio, Nancy

5467

Dodsworth, Anne

452

Fuller, Andrew

3457

King, Robert

Extensions!

FullName

Extension

Leverling, Janet

3355

Peacock, Margaret

5176

Suyama, Michael

428

0 0

Post a comment