The XML Is On The Kid

Bob Phillips — Thu, 11 Mar 2010 23:08:00 GMT

I recently wrote about a database tool that I have built to facilitate adding stored procedures to databases, SP Builder.

XL-Dennis made a couple of comments, one of which was with regards to the medium used for the script file. He talked about XML files, and whilst I had used an INI file when I originally built it I had thought about using XML but dismissed it at the time as my XML skills and knowledge were minimal to put it mildly (oddly, I have been aware of and using XML for over 12 years now, mainly as a consumer of such, but I still feel it promises far more than it delivers as a technology).

XL-Dennis’ comment gave me the prod I needed to actually do something about this, so I set about converting my tool to using XML files.

This is the structure of the XML I designed, using the same data as my previous posts.

<?xml version="1.0" encoding="utf-8"?>
<database type="Access"
         path="C:\MyDatabases\"
         name=”MyDB.mdb">

   <category name="Get">

       <procedure name="spGetCompanyGoals">

           <SQL code="SELECT SUM(RD.SalesGoal) AS 'Company Sales Goal', " />
           <SQL code="      SUM(RD.BonusGoal) AS 'Company Bonus Goal' " />
           <SQL code="FROM refUsers AS RU " />
           <SQL code=”WHERE LoginID = prmLoginId;" />
       </procedure>

       … more procedures
   </category>

   <category name="Delete">

        <procedure name="spDeleteSalesType">

           <parameter name="prmUpdatedBy"
                      type="VarChar (50)" />

           <parameter name="prmSalesTypeID"
                      type="Integer" />

           <SQL code="UPDATE  refSalesTypes               " />
           <SQL code="SET     Deleted = TRUE,             " />
           <SQL code="        UpdatedBy = prmUpdatedBy,   " />
           <SQL code="        UpdatedOn = Now>             " />
           <SQL code="WHERE   SalesTypeID = prmSalesTypeID;" />
       </procedure>

       … more procedures

       … more categories
   </category>
</database>

There are some distinct advantages to the XML to my mind. These are:

it is more readable
there is less meta-information, such as the various counts, because the XML parser will provide all of that to the code
the XML parser provides a level of validatio
tools such as Altova XMLSpy provide a much better IDE for creating and updating these files than a text editor, as well as validation

There are some disadvantages of course, but the only one that really irks me is having to use & for & and so on. I understand why I have to, but it still rankles a tad.

In the XML design, there were a couple of decisions to be made, around the elements and attributes. Before I embarked upon the design I read the chapter on XML in the second edition of the indispensable Professional Excel Development, and whilst it suggested encapsulating the data in elements rather than attributes I chose to use attributes as I felt it was more readable that way, attributes still need to be within a parent element which loses clarity. In my mind, this

is far simpler than this

           <parameter>
               <name>prmSalesTypeID</name>
               <type>Integer</type>
           </parameter>

Changing the addin was trivial once I had worked out the code for reading the XML file, updating the INI file to my XML format took far more time. The XML version ofthe addin can de downloaded here.

SP Builder is supplied as an Excel 2003 addin, or XLA file, and adds a menu option to the Tools menu with three options, Build SPs, SP Builder List and About.

As you many know, when Excel 2003 workbooks that create commandbars are opened in Excel 2007 or 2010 the menus are accessed via the Addins tab. This is not a satisfactory solution, so the addin tests the Excel version, and if it is 2003 or earlier it builds the commandbars, it is 2007 or 2010 it loads a ‘light’ 2007/2010 addin that adds a group to the Developer tab.

This way, we have a single addin that runs in any version of Excel.

One thing that I found was that I could not insert comments in my file, the parser failed when I had comments. I used what I believe is the correct format for comments, that is

<!—
‘-----------------------
' Check Stored Procedures
'-----------------------
-->

but it only worked when I completely stripped out the comments.

I also created an XSD file for the XML, which I used to play with reading it into Excel, but I see no real use for Excel in this process other than hosting the code, so that is going nowhere. Is there any other use I can use the XSD for? This also points at the next step, take Excel out of the process completely and create a standalone VB application; that would make a nice candidate for me to develop some more .Net skills.

Excel Do, Dynamic Does : VBA, 2010

The XML Is On The Kid