Slide 1Slide 2Slide 3Slide 4Slide 5Slide 6Slide 7Slide 8Slide 9Slide 10Slide 11Slide 12Slide 13Slide 14Slide 15Slide 16Slide 17Slide 18XML ImportingCS 8630 Database Administration, Sean MullinsExtracting Data From XML:A Module for TurboRep, Inc.By Sean MullinsXMLXML ImportingCS 8630 Database Administration, Sean MullinsTurboRep is an all encompassing software solution that incorporates all facets of job tracking and management. Specifically designed for HVAC sales agencies, TurboRep began sales in 2000 and has customers across the United States.The software, written with Visual Basic 6, utilizes a SQL database to manage the more than seventy individual tables required to handle TurboRep. The software also offers over fifty unique reports that track everything from job cost analysis to employee tax payments to date.TurboRep Back GroundXML ImportingCS 8630 Database Administration, Sean MullinsCurrent customers of TurboRep that use vendor software to place orders are required to double their work load by entering the order into both the vendor software and TurboReps’ in effort to track the job. This may seem like a trivial amount of work but it is not when you consider the average order requires thirty or more line items, not including line item notes. Currently there is no means to efficiently move the data from vendor software to TurboRep. The vendor software conveniently stores the order data locally to the users machine in the form of an XML document. The next side displays a sample of such a document.TurboRep ProblemXML ImportingCS 8630 Database Administration, Sean MullinsXML ImportingCS 8630 Database Administration, Sean MullinsThe Plan• Research a means on which to connect to an XML document using VB6.• Read through the document extracting only desired data.• Saving the data to the SQL database once complete.• Make the operation as simple as a push of a button for the user.XML ImportingCS 8630 Database Administration, Sean MullinsConnecting to an XML Document: DOMDocument • DOMDocument allows the XML document to be viewed in a tree structure, i.e. parent/child nodes.• Create, set and load a DOMDocument variable.DIM xmlDoc As DOMDocumentSET xmlDoc = New DOMDocumnetxmlDoc.Load c:/my/path/xmlDocument.xml• Once connected to the document, I can attain any attribute value or count of any parent or child node.fanCount = xmlDoc.firstChild.childNodes(i).childNodes.length FanModel = xmlDoc.firstChild.childNodes(Fan).childNodes(i).Attributes(18).nodeValueXML ImportingCS 8630 Database Administration, Sean MullinsProcess continued: • After a successful connection, several methods are used that loop through the parts and accessories stored in the XML document that extract appropriate data, i.e. product code, description, line item notes, quantity ordered and unit costs.•The next slide shows a piece of sample code I created that attains the location of the location of two possible product types and the number of each product.XML ImportingCS 8630 Database Administration, Sean MullinsDim xmlDoc As DOMDocumentSet xmlDoc = New DOMDocumentxmlDoc.Load docselection kidcount = xmlDoc.firstChild.childNodes.length For i = 0 To kidcount - 1 child = xmlDoc.firstChild.childNodes(i).nodeName Select Case child Case "Fans" fanCount = xmlDoc.firstChild.childNodes(i).childNodes.length fanParentValue = i thereareFans = True Case "Parts" partCount = xmlDoc.firstChild.childNodes(i).childNodes.length partParentValue = i thereareParts = True Case Else unknownChild = True End Select Next i•The next slides display the module in action as outlined in the users guide created for the module.XML ImportingCS 8630 Database Administration, Sean MullinsFrom the main menu choose the vender button located to the left.XML ImportingCS 8630 Database Administration, Sean MullinsFrom the vendor menu choose the “Purchase Orders” option.XML ImportingCS 8630 Database Administration, Sean MullinsFrom here choose the “New” option to create a new purchase order.XML ImportingCS 8630 Database Administration, Sean MullinsFrom here enter a vendor, “Penn Ventilation” for example.XML ImportingCS 8630 Database Administration, Sean MullinsNext click the “Import PO” button to choose a document.XML ImportingCS 8630 Database Administration, Sean MullinsNext choose an the appropriate XML file to be imported, “TestData” in this example.XML ImportingCS 8630 Database Administration, Sean MullinsNext save the changes to complete the import, the disk icon.XML ImportingCS 8630 Database Administration, Sean MullinsImport Complete!XML ImportingCS 8630 Database Administration, Sean MullinsLooking back on the project I would do a few things differently. •Firstly rather than statically code the node locations of the product attributes I would implement a more dynamic means of searching by using attribute names rather than the location. This would prevent any loss of use in the event of the XML structure changing. • I would have liked to add a function that would add the new products to the products table rather than generating the generic code and using it. Hindsight:XML ImportingCS 8630 Database Administration, Sean MullinsThe