HandsOn Applying a Stylesheet to an Ado Xml Document and Importing It to Access

1. Enter the following procedure below the procedure code you created in Hands-On 29-13.

Sub ApplyStyleSheetAndImport()

Dim myXMLDoc As New MSXML2.D0MDocument50 Dim myXSLDoc As New MSXML2.D0MDocument50 Dim newXMLDoc As New MSXML2.D0MDocument50

myXMLDoc.async = False

If myXMLDoc.Load("C:\Learn_XML\Products_AttribCentric.xml") Then myXSLDoc.Load "C:\Learn_XML\AttribToElem.xsl"

' apply the transformation

If Not myXSLDoc Is Nothing Then myXMLDoc.transformNodeToObject myXSLDoc, newXMLDoc

' save the output in a new file newXMLDoc.Save "C:\Learn_XML\Products_Converted.xml"

' import to Access

Application.ImportXML "C:\Learn_XML\Products_Converted.xml" End If End If

' cleanup

Set myXMLDoc = Nothing Set myXSLDoc = Nothing Set newXMLDoc = Nothing End Sub

The above procedure begins by loading both the Products_AttribCentric.xml file (created in Hands-On 29-13) and the AttribToElem.xsl stylesheet (created in Hands-On 29-14) into the DOMDocument object. Next, the stylesheet is applied to the source file by using the transformNodeToObject method. This method is applied to a node in the source XML document's tree and takes two arguments. The first argument is a stylesheet in the form of a DOMDocument node. The second argument is another DOMDocument node that will hold the result of the transformation. Next, the result of the transformation is saved to a file (Products_Converted.xml) and the file is imported to Access using the ImportXML method, which was introduced earlier in this chapter.

2. Run the ApplyStyleSheetAndlmport procedure.

3. Open the Products_Converted.xml file located in the Learn_XML folder. Notice that the Products_Converted.xml file content is now element-centric XML (see Figure 29-24).

4. In the Database window, locate and open the table named Product.

The Product table was created by the ImportXML method in the ApplyStyleSheetAndImport procedure.

Taking Your VBA Programming Skills to the Web

C:\Learnr.XML^Products. Converted.wnl - Microsoft Internet Ex... | -

File Edt View Favorites Tools help

sr

Qs«k - Q ■ 0 g)

Address C^Learn.XNL^Products.Corwerted.xml v ¿J Go

Lints "

<?xml vers ion=" 1.0" encoding=,UTF-8" ?>

- <Products xmlns:rs="urn:schemâs-microsoft-com:rowset"

xmlns: z="#RowsetSchema">

- <Product>

< ProductID> 1 </ProductID>

< Pro due tNa me> Cha i</Produ c t Nam e >

< Supp lierID> 1 </Sup p li e rt D>

< Category ID> 1 </Ca te g ory ID >

<QuantityPerUnit>l0 boxes x 20

bags </Quan tit y PerUni t >

< Uni t P ri ce> 18</Un i tPrice>

< Uni t slnS t o c k >39 </Uni t s InS t o ck>

< Uni tsO nOrde r> 0 </Uni t s OnO rd e r>

< ReorderLe vel >10 </ReorderLe vel >

<Discontinued>False</Discontirtued>

</Product>

- <Product>

< Pr o due tl D>2 </Produc tlD>

< Pro ductNa rne> Cha n g</Produ c t Nam e >

< SupplierlD> 1 </Sup p lie rlD>

<Catego ry ID> 1 </C a t e g ory î D >

< Quanti tyPerUnit>24 - 12 oz

b ott le s </Qu a n l ityPerUn it >

< Uni tP ri ce> 19 </Un itPrice>

<UnitslnStock>l7</Unitslr>Stock>

< Uni t sO nO rde r> 40</U n itsOn Order>

< ReorderLe vel >25 </ReorderLe vel>

< Di sc o n t inu e d >Fa Ise </Di s c ontirtu e d>

</Product>

V

Dene ^ My Corrvuter

Figure 29-24: This element-centric XML file is a result of applying a stylesheet to the attribute-centric ADO recordset that was saved to an XML file.

The original XML file is shown in Figure 29-23, and the stylesheet used in the transformation can be found in Hands-On 29-14.

0 0

Post a comment