Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

XML IN 20 MINUTES!

By Leon Platt, (first published: 2003/09/26)

XML IN 20 MINUTES!

This article will quickly dive head first right into XML.  I will take an everyday object and attempt to describe it using XML.  Then I will load the XML file into a XML document object model (DOM).  Next, I will demonstrate how to query the XML document using XPath and perform some minor manipulation of the DOM.

All of this functionality will be preformed using a simple Visual basic application and the Microsoft parser version 3.0.  The final objective of this article will be to produce an ActiveX control which will query the pubs SQL Server database and return a list of book titles in XML format.

LETS GET STARTED !

If any of you have ever picked up a book and tried to learn XML on your own you were probably confronted with the same confusing bombardment of information that I was. DTDs, XML Schema, name spaces, XPath, XPointers, XSL , XSLT, DOMs, SAX, SOAP, YIKES I GIVE UP. To make matters worse, most of this material is based on proposed implementations and much of the sample software is buggy and unpredictable. There must be a million ways to implement and use XML, this can all be very complicated. But guess what; XML can be simple too. If we ignore DTDs, XML Schemas, namespaces, etc. 

In my quest to get you up and running with XML quickly, I am going to ignore a good portion of the information you will find in many books on the subject. The first items I will ignore are namespaces and schemas. You may think this is strange since most books start with these subjects, but think of XML as a tool to get a job done, just like a hammer is a tool. To use a hammer, do I need to understand how to build a house? What if all I want to do is hang a picture? XML is the same way, it can be really complex and full of confusing specifications making it versatile enough to use in hundreds if not thousands of applications, but it can also be very simple if  some things are ignored.  In this article, I will concentrate on the XML necessary to solve a specific problem. 

So what exactly is the problem? Well lets suppose I want to describe a simple object such as a cup using XML. Why would I want to use XML to do this? Well, first of all, that's what XML does. XML describes data. In my example; the cup is the data. In real life, the data could be a word document, a spreadsheet, an image, a book, a record from a database, or maybe even a VB or C++ class. Secondly, XML is extensible. XML lets me create as many tags as I want to describe our data and the tags can be defined however I want, as long as I follow some simple rules. Lastly, because it is quickly becoming a universal standard. If there is life on mars, you can bet they will understand my XML file.

What are some of the properties that allow me to describe a cup?

The material its made of
    If the material is transparent or not
the height in inches
the number of ounces it will hold
its contents
    description of any solid objects and the quantity
    description of any liquid substance and the volume
    description of any other substance and the quantity
whether or not it has a lid
 

So what would this look like in XML?

<?xml version="1.0"?>
<CUP>
<MATERIAL transparent="yes">glass</MATERIAL>
<HEIGHT units="inches">6</HEIGHT>
<VOLUME units="ounces">16</VOLUME>
<CONTENTS>
	<SOLID qty="2">ice cube</SOLID>
	<SOLID qty="1">straw</SOLID>
	<LIQUID qty="3" units="ounces">water</LIQUID>
	<OTHER qty="0"/>
</CONTENTS>
<LID>yes</LID>
</CUP>
Note, the first line of the XML file (<?xml version="1.0"?>) is known as a processing command. For now, just know that it needs to be there. The neat thing about the XML file, is that anyone can get a very good idea of what its purpose is just by looking at it. Understand that this is not the only valid XML representation for a cup. If I asked ten people to create a XML file for a cup; given the same properties, they may all very well each produce something different but correct. This could be a problem. Maybe not for us humans, but if a computer is reading the file it would probably be a good idea to give it a file that it knows something about. That's where namespaces and schemas come in to the equation. To put things simple, schemas are used to specify a valid structure for a XML file.

This would be a good time to talk about a few simple XML rules that need to be followed:

XML RULE #1: a valid XML file will conform exactly to its specified schema. However, to make things simple, none of my examples will use schemas. Thus, none of my XML files will be "valid" as far as a parser is concerned. But, guess what, I don't care. I'm not building a house, I'm only hanging a picture. I'll explain more about this later when I get into using the XML DOM.

XML RULE #2: If you are a VB programmer; XML is case sensitive. XML is case sensitive. XML is case sensitive. Write this sentence 1000 times and don't forget it.

XML RULE #3: Tag names are called Elements, and every beginning tag must have an ending tag. This is known as well-formed XML. This is very important because a XML file will not parse and will not load into the DOM unless it is well-formed. Note: if elements do not contain a value and do not contain other elements then the short hand version of the end tag <Element /> may be used instead of the long version <Element></Element>. This can be seen in my example above for the element <OTHER qty="0"/> in the contents section of the cup XML file.

XML RULE #4: Elements can contain attributes and the values of the attributes must be contained in quotes (either single or double).

XML RULE #5: It is ok to re-use attribute names, but make sure your element names are unique throughout the entire file. In the example above, the attribute qty has a different meaning depending on whether it is used in the element <SOLID>,<LIQUID>, or <OTHER>. The meaning of an attribute depends on the context in which it is being used.   Whereas, the meaning of an element always means the same thing no matter where it is found in the file. In our example above the elements <SOLID> and <HEIGHT> always have the same meaning in our document. <SOLID> is always used to describe a solid that is present as part of the cups contents, and <HEIGHT> always describes the height of the cup.

XML RULE #6: There are some special characters in xml that can not be used directly as data because they would interfere with the syntax of tags and attributes. Thus, these characters must be escaped out using the & character and a special code. (& must be replaced with &amp;) (" must be replaced with &quot;) (< must be replaced with &lt;) (> must be replaced with &gt;) and ( ' must be replaced with &apos;) To get around this a special xml processing instruction <![[CDATA[...]]> can be used, where the "...." portion can be any character string that does not include the "]]>" string literal. CDATA sections are a method of including data that contains characters which would otherwise be recognized as markup. CDATA sections may occur anywhere that character data may occur, but they cannot be nested.

Ok, SO WHAT IS THE XML DOM & WHY IS IT NEEDED? 

The XML DOM allows programmers to load into memory a representation of the entire XML file. Once the XML file is loaded into memory, it can then be manipulated using the properties, methods and events of the DOM. This is where the usefulness of XML really shines. The DOM makes it very easy to retrieve and manipulate information in the XML file. I am not going to go over everything that can be done with the DOM, but I will cover some basic items which will help accomplish the goals of this article.  I will take the XML file for a cup that was just created, load the XML file into a DOM so that it can be examined, then do some minor manipulation of the data by querying the DOM.  I will save the major manipulation methods of the DOM for my next article dealing with client-side XML.  Note: although the DOM is great for programmers it does have the drawback of consuming massive amounts of memory and system resources.  It is precisely this reason that you will see references to another method of parsing XML files know as SAX.  My articles will not get into the concepts behind SAX but there is plenty of information available in the XML SDK for those who wish to become experts.

XML DOM – LOADING THE XML FILE

Lets look at an example using Microsoft’s XML parser version 3.0 (msxml3.dll) to see how this works. If you do not have version 3.0 of the parser it can be downloaded from Microsoft. .  The msxml 3.0 release installs the Microsoft XML Parser in side-by-side mode, which means that when you download and install msxml 3.0, it does not replace any previously installed version of the parser, You can also run msxml 3.0 in Replace mode, using xmlinst.exe, which means that application references to previous versions of msxml are remapped to point to the new msxml 3.0 dll. Note, however, that running msxml 3.0 in Replace mode can leave your computer in an unstable state.  For more information about running msxml 3.0 in Replace mode, see the Knowledge Base article Application Errors Occur After You Run Xmlinst.exe on Production Servers.  Suppose I have saved the sample XML cup file as “http://web_server/xml/cup.xml” (local path C:\inetpub\wwwroot\xml\cup.xml) and now I want to load this file up into a XML DOM.  The code below assumes the parser was loaded in default side-by-side mode and uses version-dependent progIDs to specifically specify version 3.0 of the parser.  Click here  for a further explanation of side-by-side mode and version-dependent progIDs.
Visual Basic 6.0 code:  
   (need to set a reference to Microsoft XML, v3.0)
Dim xmlDoc as MSXML2.DOMDocument30
Set xmlDoc = New DOMDocument30
xmlDoc.async = False
xmlDoc.validateOnParse = False
xmlDoc.load ("c:\inetpub\wwwroot\xml\cup.xml")
msgBox xmlDoc.xml
ASP Server-Side VB script code:
Dim xmlDoc
Set xmlDoc = Server.CreateObject("Msxml2.DOMDocument.3.0")
xmlDoc.async = False
xmlDoc.validateOnParse = False
xmlDoc.load  "/xml/cup.xml"
ASP Server-Side Java Script code:
var xmlDoc = Server.CreateObject("Msxml2.DOMDocument.3.0");
xmlDoc.async = false;
xmlDoc.validateOnParse = false;
xmlDoc.load ("/xml/cup.xml");

EXPLANATION OF THE CODE - Lets walk through the VB6 code

Line 1: Dim xmlDoc as MSXML2.DOMDocument30

This first statement requires a reference set to "Microsoft XML, v3.0".  In this line, I am declaring the variable xmlDoc to be a XML document (DOM).  MSXML2 is the library and this is always MSXML2 (you may see older code that uses the library msxml) (also do not be tempted to put MSXML3 for version 3, this has nothing to do with the parser version and it will not work).  DOMDocument30 specifies a XML document object (DOM) conforming to version 3.0.  You may also see code samples where the line may be dim xmlDoc as MSXML2.DOMDocument.  Notice there is no 30 after DOMDocument, this is the version independent way to set up a XML document.  The version independent method will use the currently registered parser, whatever version it may be.  The problem with the independent method is that the default registered version of the parser will vary from machine to machine.  If you want to use the independent method, you better make sure your code is generic enough to work with any version of the parser or it may break.  You can not assume that every user will have the version parser you are expecting.  The advantage to using the independent method is that when a new version of the parser comes out the application will support it automatically without recompiling.

Line 2:  Set xmlDoc = new DOMDocument30

This line actually initializes the variable xmlDoc to a new XML document (DOM) version 3.0.

Line 3:  xmlDoc.async = False

XML files can be loaded either synchronously or asynchronously.  If xmlDoc.async=False then the entire XML file will be loaded before control is given back to the calling program.  If xmlDoc.async=True then the load function will immediately return control to the calling program even though the XML file may not be entirely loaded.

Line 4:  xmlDoc.validateOnParse = False

When loaded the parser can be instructed to validate the XML file against a schema (validateOnParse=True) or we can ignore schemas and validation entirely by setting (validateOnParse = False).

Line 5: xmlDoc.load ("C:\inetpub\wwwroot\xml\cup.xml")

This line calls the load method of the DOMDocument.  There are two versions of the load method.  The one I am calling here loads a file into the DOM and it must be passed a valid path and filename.  The second version of the load method is used to load a xml string into a DOM and it must be passed a well-formed xml string.  The string version of the load method would be called using xmlDoc.loadXML("well-formed xml string").  I will demonstrate this later.

Line 6: MsgBox xmlDoc.xml

This line will display the xml for the entire document.  The results should be the same as the original xml file.

EXAMINING THE XML DOM

Create a new standard.exe project in visual basic.  Insert the sample code into the forms load method.  Make sure you create a reference to "Microsoft XML v3.0".  To do this click Project-->References, then scroll through the list until you find it.  Note: the version 3 of the parser must be installed or you will not find the reference in your list.  Set a breakpoint on the last line of the code.  The line that reads msgbox(xmlDoc.xml).  Run the application in debug mode.  When the breakpoint is hit and the application stops; bring up the locals window and examine the DOM.  You can learn a lot about the DOM by walking through it in the locals window.  The locals window should look similar to the figure on the next page.  Some interesting properties of  the DOM are:

The XML DOM always contains two top level nodes:

        Item1 is the root element of the document ( ignore this node )

        Item2 is actually the first element of the document (remember this)

nodeName or baseName - this can be used to find the name of an Element or Attribute

nodeType - use this to retrieve the type of the current node. 

nodeValue - use this to get the data value of the node 

childNodes - is a collection of children nodes that exist in the tree at a level directly bellow the current node.  Child nodes can be element nodes, text nodes or CDATA nodes.  There are also other types of nodes that I will not discuss, but you can find a good description of them in the XML SDK. 

attributes - is a collection of attribute nodes for the current element.  

length - use this to find the number of nodes in the tree at a level directly bellow the current node.

xml - this property exists on all nodes and can be used to get the xml string representing the current position in the document.  The xml string starts with the current node and traverses down the rest of the tree.  This is a very useful property.  Play around with it and see what you get.

EXAMINING THE XML DOM - A successful load

EXAMINING THE XML DOM - Element nodes

Element nodes can contain other element child nodes, attribute child nodes, text child nodes, or CDATA child nodes.  From the figure bellow the following information can be obtained about the current node "SOLID" :

nodeType - The current nodes type = NODE_ELEMENT  - the current node is an element.

nodeName or baseName or tagName - The current nodes (Elements) name is  = SOLID

It's parent element is CONTENTS which has 4 children

You can't see it from the figure bellow but SOLID has one childNode, which is of type text. (see text nodes on a following page)

text - "ice cube" this is a short cut method of obtaining the current nodes value without actually navigating to the child text node.

EXAMINING THE XML DOM - Attribute nodes

Attribute nodes can contain only text child nodes or CDATA child nodes.  From the figure bellow the following information can be obtained about the current node "qty" :

nodeType - The current nodes type = NODE_ATTRIBUTE  - the current node is an attribute.

nodeName or baseName - The current nodes (Attributes) name is  = qty

You can't see it from the figure bellow, but qty has one childNode, which is of type text. (see text nodes on a following page)

text or value  - "2" this is a short cut method of obtaining the current nodes value without actually navigating to the child text node.

EXAMINING THE XML DOM - Text nodes and CDATA nodes

Text and CDATA nodes do not contain child nodes. Text nodes contain parsed text data for its parent node.  CDATA nodes contain unparsed text data for its parent node. CDATA nodes are created when the CDATA processing instruction is wrapped around data in the xml file.  This is useful for cases when the data contains special characters.  The CDATA processing tag tells the parser not to parse the data and to accept all characters within the tag as data.  CDATA sections are especially useful when trying to embed code in an xml file.  From the figure bellow the following information can be obtained about the current text node.

NodeType - The current nodes type = NODE_TEXT  - the current node is an text data.

nodeName - The current nodes (text) name is  = #text - all text nodes are named #text

data or text or value - "2" - this is the current nodes data

EXAMINING THE XML DOM - Errors loading the document

The parseError section of the DOM can be beneficial to tracking down problems with loading the xml document.  If I remove the ending tag from OTHER in our sample file and run the program again I get the following results.  The first piece of helpful information is that our nextSibling is Nothing.  Also, if you looked at childNodes you would find the length equal to zero.  Both these things indicate that the xml document did not load.  To find out why it did not load I open up the parseError node to retrieve all the specific error information.

XML DOM – QUERYING THE XML DOCUMENT

Ok, so I have shown how to get an xml file into the DOM, but what do I do with it now?  Well, one of the most common things you will want to do is query the xml document.  To accomplish this you could traverse the document until you find what you are looking for, but most likely you will use one of two methods of the DOMDocument or current node (context node).  The two methods used to query for nodes in our previous example would be xmlDoc.SelectSingleNode(patternString) to return one node and xmlDoc.SelectNodes(patternString) to return a list of nodes.  The patternString parameter that is passed into these methods consists of a query.  This patterString can be formatted in one of two ways.  Either as a XSL query or as a XPath query.  The XPath version is the newer and more preferred method of querying xml documents.  The format of the patternString must be set before any queries are run using the two indicated methods, otherwise the default XSL format will be used.  To set the format of the patternString you will use the setProperty("SelectionLanguage", "format").  To change the queries in our example to use a XPath patternString, I will issue the following command:  setProperty("SelectionLanguage","XPath").  In my opinion, XPath is probably one of the most important XML technologies to learn.  I will introduce some simple XPath queries, but I highly recommend doing some independent research in this area.  A good starting place would be the Microsoft XML SDK.  I have also come across some pretty good tutorials on various web sites.  Another avenue of exploration would be to write a simple VB application to allow you to enter XPath queries and have the results displayed.  You can probably find a freeware application to do this but XPath is still fairly new and may not be entirely supported by every application that you find.

USING XPATH TO QUERY THE DOM 

Lets add some additional code to the end of our previous example to return us just the contents of our cup:

Dim objNode As IXMLDOMNode
Dim objListOfNodes As IXMLDOMNodeList
xmlDoc.setProperty "SelectionLanguage", "XPath"
MsgBox "Your cup contains the following items"
Set objListOfNodes = xmlDoc.selectNodes("//CONTENTS/*")
For Each objNode In objListOfNodes
    MsgBox objNode.Text
Next

Run the program and and see what you get.  Notice that you should get four message boxes telling indicating what is in the cup.  The last message box should be blank because the Element "OTHER" does not contain text.  Lets fix the query to return all the contents of the cup where the qty>0.  Change the line of code that does the query to the following:

Set objListOfNodes = xmlDoc.selectNodes("//CONTENTS/*[@qty>0]")

COOL!  Now lets add one more query to find out if our cup has a lid or not. Add the following code to the end of the previous code:

Set objNode = xmlDoc.selectSingleNode("/CUP/LID")
if objNode.text="yes" then
    MsgBox "We have a lid"
else
    MsgBox "No lid on this cup"
end if

Lets go over the code line by line:

Line 1:  Dim objNode As IXMLDOMNode

This line defines the variable objNode as a variable of type xml document node.  It is important to realize that a XML document node is an object.  It is not a value.  It consists of itself as well as all of its attributes and childNodes.  In this manner you can prune a entire branch off of a tree by selecting the correct node.

Line 2:  Dim objListOfNodes As IXMLDOMNodeList

This line defines the variable objListOfNodes as a variable of type xml document node list (a group of nodes).

Line 3:  xmlDoc.setProperty "SelectionLanguage", "XPath"

This line sets our patternString format to XPath.

Line 4:  MsgBox "Your cup contains the following items:"
Line 5:  Set objListOfNodes = xmlDoc.selectNodes("//CONTENTS/*[@qty>0]")
 

This line runs a XPath query that will return a group of nodes and store them in the variable objListOfNodes.  The query breaks down like this:

 //CONTENTS  - get all the CONTENTS elements in the xml document.  This retrieves one element node from our sample XML file (<CONTENTS>).  Note:  // is short for in the entire xml document.

/* - from the list of CONTENTS element nodes, get all ( * - short for all ) the child elements.  This narrows our results down to four element nodes (<SOLID><SOLID><LIQUID><OTHER>).  These four nodes fall directly under the CONTENTS node.

[@qty>0] - for each child element test its qty attribute (@ - short for attribute) to see if it is greater than 0.  If it is keep the element node otherwise discard it.  Anything inside of [ ] in an XPath query gets resolved to true or false.  If the results are true then the node remains.  If the results are false, then the node is discarded.  This narrows the query results down to three element nodes (<SOLID><SOLID><LIQUID>).  

Line 6-8:  For Each objNode In objListOfNodes / MsgBox objNode.Text / Next

These lines will display the value of each element node that matched the query. ( "ice cube" , "straw" , "water" ) 

Line 9:  Set objNode = xmlDoc.selectSingleNode("/CUP/LID")

This line queries for all the LID elements that exist directly under CUP elements that exist directly under the ROOT (when a query starts with / it means start at the root).  This works just like a directory path, thus the name XPath.  In the sample XML file the query will return the LID element which contains a value of "yes".  The important thing to note here is that I am forcing the query to begin at the ROOT of the XML document.  Queries will not always start with the ROOT, normally queries will start at the current node (context node).  In the example this is irrelevant because my context node (xmlDoc) is the ROOT of the XML document (this is not always the case).

Line 10-15:  if objNode.text="yes" then / MsgBox "We have a lid" /
else /  MsgBox "No lid on this cup" /end if

This line will display the message "We have a lid" because the text property of the LID element is "yes".

CONVERTING ADO TO XML

Now that you understand the basics of XML, lets create an activeX control that will convert an ADO record set to XML.  My goal is to run a query against the titles table in the pubs database and return the titles in XML format.  I will use the results from the activeX control in my next article "client-side xml".  Simple you say, ADO has a method to save as XML, right?  Well yes, but if I let ADO create the XML I will get a version of XML that is nasty to work with.  ADO will produce a XML file with namespaces and I am trying to ignore these for now.  Secondly, ADO will produce a XML file that is in attribute form.  In other words each record is an element (z:row) and each field is an attribute:

<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
	xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
	xmlns:rs='urn:schemas-microsoft-com:rowset'
	xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
	<s:ElementType name='row' content='eltOnly'>
		<s:AttributeType name='title_id' rs:number='1' rs:writeunknown='true'>
			<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='6' rs:maybenull='false'/>
		</s:AttributeType>
		<s:AttributeType name='title' rs:number='2' rs:writeunknown='true'>
			<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='80' rs:maybenull='false'/>
		</s:AttributeType>
		<s:AttributeType name='type' rs:number='3' rs:writeunknown='true'>
			<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='12' rs:fixedlength='true' rs:maybenull='false'/>
		</s:AttributeType>
		<s:AttributeType name='price' rs:number='4' rs:nullable='true' rs:writeunknown='true'>
			<s:datatype dt:type='number' rs:dbtype='currency' dt:maxLength='8' rs:precision='19' rs:fixedlength='true'/>
		</s:AttributeType>
		<s:AttributeType name='ytd_sales' rs:number='5' rs:nullable='true' rs:writeunknown='true'>
			<s:datatype dt:type='int' dt:maxLength='4' rs:precision='10' rs:fixedlength='true'/>
		</s:AttributeType>
		<s:AttributeType name='notes' rs:number='6' rs:nullable='true' rs:writeunknown='true'>
			<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='200'/>
		</s:AttributeType>
		<s:AttributeType name='pubdate' rs:number='7' rs:writeunknown='true'>
			<s:datatype dt:type='dateTime' rs:dbtype='timestamp' dt:maxLength='16' rs:scale='3' rs:precision='23' rs:fixedlength='true'
			 rs:maybenull='false'/>
		</s:AttributeType>
		<s:extends type='rs:rowbase'/>
	</s:ElementType>
</s:Schema>
<rs:data>
	<z:row title_id='BU1032' title='The Busy Executive's Database Guide' type='business    ' price='19.99'
		 ytd_sales='4095' notes='An overview of available database systems with emphasis on common business applications. Illustrated.'
		 pubdate='1991-06-12T00:00:00'/>
	<z:row title_id='BU1111' title='Cooking with Computers: Surreptitious Balance Sheets' type='business    ' price='11.95'
		 ytd_sales='3876' notes='Helpful hints on how to use your electronic resources to the best advantage.' pubdate='1991-06-09T00:00:00'/>
</rs:data>
</xml>

I would like to produce a XML file that is in element form, where each record will be wrapped in a <BOOK> tag and each field will be an element under the <BOOK> tag.  The syntax of my xml string will be as follows:

<TITLES>
<BOOK>data from table
    <FIELD prettyname="Book identification number" tablename="titles" columnname="title_id" datatype="number" filter="">data from table</FIELD>
    <FIELD prettyname="Title of the book" tablename="titles" columnname="title" datatype="text" filter="">data from table</FIELD>
    <FIELD prettyname="Type of book" tablename="titles" columnname="type" datatype="text" filter="">data from table</FIELD>
    <FIELD prettyname="Price of the book" tablename="titles" columnname="price" datatype="number" filter="">data from table</FIELD>
    <FIELD prettyname="Year todate sales" tablename="titles" columnname="ytd_sales" datatype= "number" filter= "">datafrom table</FIELD>
    <FIELD  prettyname="Datepublished" tablename= "titles" columnname="pubdate" datatype="date" filter= "">datafromtable</FIELD>
 </BOOK>
</TITLES>

By the way, what I have just accomplished is to create a schema for my xml string.  Now if I wanted to validate the structure of XML against the schema, all I would need to do is convert the schema into a valid format.  Either DTD or XDR syntax.  Notice that I have added several attributes to each <FIELD> element.  One reason for this is that this information may be useful on the client.  The prettyname can be used as labels for the data.  The datatype attribute could be used for client side data validation.  But to be honest, the real reason these attributes exist is that they have a very special purpose in a XSL template file that I regularly use for building "SQL where clauses".  Maybe I will publish a short article demonstrating this soon.  The template is very usefull and pretty cool.   When the xml structure is applied to the actual data from the titles table the results will look like the sample bellow:

<TITLES>
     <BOOK>The Busy Executive's Database Guide
          <FIELD prettyname="Title Identification Number" tablename="titles" gcolumnname="title_id" datatype="number" gfilter="">BU1032</FIELD>
	  <FIELD prettyname="Title of the Book" tablename="titles" gcolumnname="title" datatype="text" gfilter="">The Busy Executive's Database Guide</FIELD>
	  <FIELD prettyname="Type of Book" tablename="titles" gcolumnname="type" datatype="text" gfilter="">business    </FIELD>
	  <FIELD prettyname="Price of the Book" tablename="titles" gcolumnname="price" datatype="number" gfilter="">19.99</FIELD>
	  <FIELD prettyname="Year to date sales" tablename="titles" gcolumnname="ytd_sales" datatype="number" gfilter="">4095</FIELD>
	  <FIELD prettyname="Notes about the book" tablename="titles" gcolumnname="notes" datatype="memo" gfilter="">An overview of available database systems with emphasis on common business applications. Illustrated.</FIELD>
	  <FIELD prettyname="Date Published" tablename="titles" gcolumnname="pubdate" datatype="date" gfilter="">6/12/1991</FIELD>
     </BOOK>
     <BOOK>Cooking with Computers: Surreptitious Balance Sheets
          <FIELD prettyname="Title Identification Number" tablename="titles" gcolumnname="title_id" datatype="number" gfilter="">BU1111</FIELD>
	  <FIELD prettyname="Title of the Book" tablename="titles" gcolumnname="title" datatype="text" gfilter="">Cooking with Computers: Surreptitious Balance Sheets</FIELD>
	  <FIELD prettyname="Type of Book" tablename="titles" gcolumnname="type" datatype="text" gfilter="">business    </FIELD>
	  <FIELD prettyname="Price of the Book" tablename="titles" gcolumnname="price" datatype="number" gfilter="">11.95</FIELD>
	  <FIELD prettyname="Year to date sales" tablename="titles" gcolumnname="ytd_sales" datatype="number" gfilter="">3876</FIELD>
	  <FIELD prettyname="Notes about the book" tablename="titles" gcolumnname="notes" datatype="memo" gfilter="">Helpful hints on how to use your electronic resources to the best advantage.</FIELD>
	  <FIELD prettyname="Date Published" tablename="titles" gcolumnname="pubdate" datatype="date" gfilter="">6/9/1991</FIELD>
     </BOOK>
</TITLES>

Now I have something that I can work with!  The XML is very basic and straight forward.  Please come back for Article 2 in this series where I will demonstrate how easy it is to use this XML on the client ...

Listing 1 - CUP.XML

<?xml version="1.0"?>
<CUP>
<MATERIAL transparent="yes">glass</MATERIAL>
<HEIGHT units="inches">6</HEIGHT>
<VOLUME units="ounces">16</VOLUME>
<CONTENTS>
	<SOLID qty="2">ice cube</SOLID>
	<SOLID qty="1">straw</SOLID>
	<LIQUID qty="3" units="ounces">water</LIQUID>
	<OTHER qty="0"/>
</CONTENTS>
<LID>yes</LID>
</CUP>

Listing 2 - Load Cup.xml into a DOM

Dim xmlDoc As MSXML2.DOMDocument30
Set xmlDoc = New DOMDocument30
xmlDoc.async = False
xmlDoc.validateOnParse = False
xmlDoc.Load ("c:\inetpub\wwwroot\xml\cup.xml")
MsgBox xmlDoc.xml
Dim objNode As IXMLDOMNode
Dim objListOfNodes As IXMLDOMNodeList
xmlDoc.setProperty "SelectionLanguage", "XPath"
MsgBox "Your cup contains the following items"
Set objListOfNodes = xmlDoc.selectNodes("//CONTENTS/*[@qty>0]")
For Each objNode In objListOfNodes
    MsgBox objNode.Text
Next
Set objNode = xmlDoc.selectSingleNode("/CUP/LID")
If objNode.Text = "yes" Then
    MsgBox "We have a lid"
Else
    MsgBox "No lid on this cup"
End If

Listing 3 - ActiveX Control: ADO to XML (WebClass.dll)(xmlControl.cls)

Option Explicit

'Declare Database variables
Private m_dbConnection As New ADODB.Connection
Private m_dbCommand As ADODB.Command
Private m_adoRs As ADODB.Recordset
Private m_adoErrors As ADODB.Errors
Private m_adoErr As Error
Public nCommandTimeOut As Variant
Public nConnectionTimeOut As Variant
Public strConnect As Variant
Public strAppName As String
Public strLogPath As String
Public strDatabase As String
Public strUser As String
Public strPassword As String
Public strServer As String
Public strVersion As String
Public lMSADO As Boolean
'Private Global Variables
Private gnErrNum As Variant
Private gstrErrDesc As Variant
Private gstrErrSrc As Variant
Private gstrDB As String
Private gstrADOError As String
Private Const adLeonNoRecordset As Integer = 129
Private gtableName(6) As String
Private gcolumnName(6) As String
Private gprettyName(6) As String
Private gdatatype(6) As String
Private gfilter(6) As String

Private Function OpenDatabase()

If Len(strConnect) = 0 Then  'set up defaults if they were not passed
    If Len(strDatabase) = 0 Then
        strDatabase = "pubs"
    End If
    
    If nConnectionTimeOut = 0 Then
        nConnectionTimeOut = 600
    End If
    
    If nCommandTimeOut = 0 Then
        nCommandTimeOut = 600
    End If
    
    If Len(strAppName) = 0 Then
        strAppName = "xmlControl"
    End If
    
    If Len(strUser) = 0 Then
        strUser = "sa"
    End If
    
    If Len(strPassword) = 0 Then
        strPassword = ""
    End If
    
        strConnect = "Provider=SQLOLEDB.1; " & _
           "Application Name=" & strAppName & _
           "; Data Source=" & strServer & "; Initial Catalog=" & strDatabase & "; " & _
           " User ID=" & strUser & "; Password=" & strPassword & ";"
End If

 'connect to SQL Server and open the database
On Error GoTo SQLErr  'turn on error handler
With m_dbConnection
    .ConnectionTimeout = nConnectionTimeOut
    .CommandTimeout = nCommandTimeOut
    .Open strConnect  'open the database using the connection string
End With
On Error GoTo 0  'turn off error handler

OpenDatabase = True  'database opened successfully

Exit Function

SQLErr:
Call logerror("OPEN")
OpenDatabase = False

End Function

Private Function BuildSQLwhere(tmpWhere) As String

 'This is for the future

End Function

Public Function GetTitlesXML(Optional xmlWhere As Variant) As String

Dim whereClause As String
Dim strSQL As String

Call OpenDatabase  'open the pubs database

If IsMissing(xmlWhere) Then  'a query was not passed in
    whereClause = ""
Else
    whereClause = BuildSQLwhere(xmlWhere)  'convert the query to valid sql
End If

 'initialize the sql statement that will query for the titles
strSQL = "select title_id,title,type,price,ytd_sales,notes,pubdate from titles " & whereClause

Call NewRecordSet  'create a record set
	 'Set the cursorlocation
    m_adoRs.CursorLocation = adUseClient
     'open the recordset
    m_adoRs.Open strSQL, m_dbConnection, adOpenForwardOnly, adLockReadOnly, adCmdText

 'disconnect the recordset
Set m_adoRs.ActiveConnection = Nothing

On Error GoTo 0  'turn off error handler
    
 'Close the database to free the connection
Call CloseDatabase

If m_adoRs.EOF Then
    GetTitlesXML = ""  'query did not return any titles
Else
    If lMSADO Then
        GetTitlesXML =  msado(m_adoRs)  'convert the recordset to Microsoftado-->xml
    Else
        GetTitlesXML = ADOtoXML(m_adoRs, True)  'convert the ado recordset to custom xml
    End If
End If

 'Close the recordset
Call CloseRecordset
    
Exit Function

SQLErr:
    Call logerror(strSQL)

End Function

Private Function ADOtoXML(tmprs As ADODB.Recordset, tmpMP As Boolean) As String

Dim adoFields As ADODB.Fields 'set up a collectio to hold the fields
Dim adoField As ADODB.Field  'used to retrieve each field from the collection
Dim xmlDoc As msxml2.DOMDocument30
Dim tmpLine As String  'holds the xml representation of each book
Dim tmpXML As String  'used to concatenate each line of xml
Dim i As Integer

If tmprs.EOF Then  'no titles returned by query
    ADOtoXML = ""
    Exit Function
Else
    Set adoFields = tmprs.Fields  'create the collection of fields
End If

tmpXML = "<TITLES>"  'all books will be wrapped in a <TITLES> tag

Do Until tmprs.EOF  'loop through each title in the recordset
    i = 0  ' i is an index to the ado field its initialized to 0 so that first field will be field(0)
    tmpLine = "<BOOK>" & tmprs("title") & vbCrLf
    For Each adoField In adoFields   'loop through all the fields
         'build the xml <FIELD> tag and its attributes for the current field
        tmpLine = tmpLine & "<FIELD " 
        tmpLine = tmpLine & "prettyname=""" & gprettyName(i) & """ "
        tmpLine = tmpLine & "tablename=""" & gtableName(i) & """ gcolumnname=""" & adoField.Name & """ "
        tmpLine = tmpLine & "datatype=""" & gdatatype(i) & """ gfilter="""""
        tmpLine = tmpLine & ">" & adoField.Value
        tmpLine = tmpLine & "</FIELD>" & vbCrLf
        i = i + 1  'point index to next field
    Next
    tmpXML = tmpXML & tmpLine & "</BOOK>" & vbCrLf  'end the book tag after last field
    tmprs.MoveNext  'next title
Loop
Set adoField = Nothing  'destroy the field object
Set adoFields = Nothing  'destroy the fields collection

tmpXML= tmpXML & "<?xml version="1.0"?></TITLES>" & vbCrLf  'end the string with the ending </TITLES> tag
'at this point I could just return this string back 
'load the xml string into a DOM just toshow the loadxml method
'also this will test to make sure the string is well-formed
Set xmlDoc = New msxml2.DOMDocument30  'create a xmlDOM 
xmlDoc.async = False  'wait for document  to load
xmlDoc.validateOnParse = False  'do not validate  against a schema
xmlDoc.loadXML(tmpXML)  'load the string  into the DOM
On Error Resume Next  'if  the file bellow  does not exist I will get an error  when I try to kill  it
Kill("c:\temp\custom.xml")   'erase the file if it exists 
On Error GoTo 0  'seterror handler to abort on error 
xmlDoc.save ("c:\temp\custom.xml")  'save the xml to a file
ADOtoXML=xmlDoc.xml  'return the xml string
Set xmlDoc=Nothing  'destroy the xml DOM

End Function 

Private Function msado(tmprs As ADODB.Recordset) As String 

Dim xmlDoc As msxml2.DOMDocument30
On Error Resume Next  'if the file bellow does not exist I will get an error when I try to kill it
Kill ("c:\temp\msado.xml")  'erase the file if it  exists
On Error  GoTo 0  'set error handler to abort on error
tmprs.save "c:\temp\msado.xml", adPersistXML  'save the xml to a file
Set xmlDoc = New msxml2.DOMDocument30  'create a xml DOM
xmlDoc.async = False  'wait for document to load
xmlDoc.validateOnParse = False  'do not validate  against a schema
xmlDoc.Load ("C:\temp\msado.xml") 'load the file into the DOM
msado =   xmlDoc.xml  'return the xml string
Set xmlDoc = Nothing   'destroy the xml DOM

End Function 

Private SubCloseRecordset()      
     
 'Close recordset objects and dereference them m_adoRs.Close Set
m_adoRs =Nothing  

End Sub 

Private Sub NewRecordSet() 

Set m_adoRs= Nothing  'Close recordset objects and dereference them
Set m_adoRs=New ADODB.Recordset

End Sub 

Private Sub CloseDatabase()               

 'Close database  objects and dereference them
m_dbConnection.Close
Set m_dbConnection =Nothing 

End Sub 

Private Sub logerror(errSQL As String)        
       
Dim hFile As Integer
Dim expFile As String
 
On Error GoTo 0
gnErrNum = Err.Number 
gstrErrDesc =Err.Description 
gstrErrSrc = Err.Source Set
m_adoErrors = m_dbConnection.Errors 

For Each m_adoErr In m_adoErrors
	gstrADOError =   m_adoErr.Description & "," & CStr(m_adoErr.NativeError)
	_ & "," & CStr(m_adoErr.Number) & "," &
	m_adoErr.Source  _ & "," & CStr(m_adoErr.SQLState)
Next 

hFile =FreeFile
If Len(strLogPath) = 0 Then 
    strLogPath = "C:\temp\" 
End If 
expFile = strLogPath & strAppName & ".err" 
Open expFile For Append As  #hFile
    
Print #hFile,"**********************************" 
Print #hFile, Now() 
Print#hFile, "**********************************" 
Print #hFile,"Subroutine: " & tmpPro
Print #hFile, "Error Number:"  & gnErrNum  
Print#hFile,  "Error Description: "  & gstrErrDesc 
Print #hFile, "Error Source:"  & gstrErrSrc 
Print #hFile, "Ado error String: " & gstrADOError 
Print #hFile, "Bad SQL: " & errSQL 
Close #hFile
End Sub 

 Private Sub Class_Initialize() 
strVersion = "xmlControl Version 1.1"     
 'title_id,title,type,price,ytd_sales,notes,pubdate
gtableName(0) = "titles"
gcolumnName(0) = "title_id"
gprettyName(0) = "Title Identification Number"
gdatatype(0) = "number"
gfilter(0) = ""
gtableName(1) = "titles"
gcolumnName(1) = "title"
gprettyName(1) = "Title of the Book"
gdatatype(1) = "text"
gfilter(1) = ""
gtableName(2) = "titles"
gcolumnName(2) = "type"
gprettyName(2) = "Type of Book"
gdatatype(2) = "text"
gfilter(2) = ""
gtableName(3) = "titles"
gcolumnName(3) = "price"
gprettyName(3) = "Price of the Book"
gdatatype(3) = "number"
gfilter(3) = ""
gtableName(4) = "titles"
gcolumnName(4) = "ytd_sales"
gprettyName(4) = "Year to date sales"
gdatatype(4) = "number"
gfilter(4) = ""
gtableName(5) = "titles"
gcolumnName(5) = "notes"
gprettyName(5) = "Notes about the book"
gdatatype(5) = "memo"
gfilter(5) = ""
gtableName(6) = "titles"
gcolumnName(6) = "pubdate"
gprettyName(6) = "Date Published"
gdatatype(6) = "date"
gfilter(6) = ""

End Sub

Listing 4 - VB application to test WebClass


Private Sub Command1_Click()

Dim objWC As xmlControl
Dim xml As String
Set objWC = New xmlControl
objWC.strDatabase = "pubs"
objWC.strServer = "ltweb"
objWC.strUser = "sa"
objWC.strPassword = ""
objWC.lMSADO = Option2.Value
objWC.strAppName = "Article1"
Text1.Text = objWC.getTitlesXML

End Sub

Listing 5 - Active server page to test WebClass

<%@ Language=VBScript %>
<%
 'Use the WebClass ActiveX control to return xml to the browser
 '
 'before the page will opperate the WebClass.dll must be registered on the web server
 'The microsoft xml parser version 3.0 (msxml3.dll) must be registered also
 'download msxml3.exe and install
 '
set objWC = Server.CreateObject("WebClass.xmlControl")
objWC.strDatabase =  "pubs"
objWC.strServer = "ltweb"  'replace ltweb with your sql server name
objWC.strUser ="sa"  'replace sa with your sql user name
objWC.strPassword=""  'enter your sql password here
objWC.strAppName="Article1"
objWC.lMSADO=false  'true will return microsoft ado-->xml
 'false will return custom xml
 '
Response.ContentType="text/xml"  'tell browser to expect xml
Response.write objWC.getTitlesXML  'get the xml and display it
 '
set objWC=nothing  'destroy the object
%>

Download the source code for this article

Total article views: 184177 | Views in the last 30 days: 11
 
Related Articles
FORUM

SQL Bulk Load - Nested Elements

Loading Nested Elements via SQLXMLBULKLOAD

SCRIPT

Generating combinations 'm elements from n-element set'

This procedure generates a dataset with combinations of elements_to_select taken from number_of_valu...

FORUM

first element in sublist

first element in sublist

FORUM

Multiple Namespace query common elements

With multiple namespaces associated to a single xml typed column - need to query common elements sha...

FORUM

Remote SQL Version Query Problem

T-SQL version query Problem

Tags
asp    
basics    
programming    
visual basic 6    
xml    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones