The XML Is On The Kid

Published Thu, Mar 11 2010 23:08 | Bob Phillips

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 &amp; 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

 

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

 

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.

 

 

 

Filed under: , , , , , , ,

Comments

# XL-Dennis said on March 12, 2010 9:28 AM:

Bob,

Nice to see Your efforts with XML. I believe that the following XML tools can be of interest:

- Notepad++ (free and works great)

- VS 2008 (in case You have it installed)

(BTW, it seems that Your blog is on a WordPress Server. Check out if it exist a plugin for code syntax highlight. Please see my blog).

Kind regards,

Dennis

# Harlan Grove said on March 15, 2010 12:39 PM:

Just a different opinion, but to me it'd be clearer to have the entire SQL unbroken by unnecessary XML clutter, e.g.,

<SQL_code>

SELECT SUM(RD.SalesGoal) AS 'Company Sales Goal', SUM(RD.BonusGoal) AS 'Company Bonus Goal'

FROM refUsers AS RU

WHERE LoginID = prmLoginId;

</SQL_code>

It's possible to automate maintenance of XML files, and it's simpler to change blocks between tags than attributes within tags.

My impression of XML best practice is to put metadata into attributes (e.g., name and type), but to put data into elements.

# Bob Phillips said on March 17, 2010 3:03 AM:

I think I agree with you Harlan, but I did it this way for a very good reason (one you should understand) :). If you recall a post I did on the CustomUI file for the 2007 ribbon I mentioned I edit it in TextPad, so I create a class type for XML in TextPad. In this, I had setup strings with a colour of blue, so by making it an attribute the SQL code is within quotes, so it is a string, so it shows as blue in TextPad.

If I continue to use VS 2008, there is probably no reason to continue this.

# Bob Phillips said on March 17, 2010 1:59 PM:

Hey Dennis,

I didn't reply to your comment on the other post or this one earlier because I couldn't post comments. Can you believe it, I couldn't post to my own blog. It is resolved now, so I can join in.

As you can see I took your advice re XML, and it was much easier than I feared,. The coding was very easy, and whilst the script took a lot of changes, it was all repetitive so I wrote a small script to do that. And the XML is so much tidier than the INI file.

I also loaded the XML file up in VS 2008, I agree that is much nicer. A lot easier than TextPad, evene with my class file.

# XL-Dennis said on March 23, 2010 7:55 AM:

Bob,

Yeah, unable to make comments sounds weird, very weird.

Now that You do some work in VS You may consider to port Yourself more to the VS platform ;)

Kind regards,

Dennis

# Bob Phillips said on March 23, 2010 4:03 PM:

Dennis,

It is wierd, something to do with the skins as I understand, but the blog hosters sorted it out.

VS 2008/2010 is definitely my learning project for 2010. My SQL server, OLAP and so on has improved last year, so next step :-)

# Liquid XML Studio said on September 7, 2010 4:10 AM:

I think this is possibly the best XML Database related post I've seen on the web. It's definitely helped me out, so thank you very much

# Bob Phillips said on September 9, 2010 12:23 PM:

I fear I may be replying to spam, but if not, apologies and thanks Liquid_XML_Studio.

# xml editor said on March 30, 2011 10:15 AM:

many thanks for the post, i was really struggling with this so thanks

# Roy Slater said on July 28, 2011 1:47 PM:

Thanks for the post, it's really helped a lot. Do you know anything about xml to pdf? I've been struggling with that lately. Any tips would be helpful. Thanks.

Leave a Comment

Name:  
Website:
If you can't read this number refresh your screen
Enter the numbers above: