Read XML data

[John.Woody]

  <?xml version="1.0" ?> 
- <compactdiscs>
- <compactdisc>
  <artist type="individual">Frank Sinatra</artist> 
  <title numberoftracks="4">In The Wee Small Hours</title>

The above is a sample of code from an xml file. I have code to read the tags <compactdisc>
<artist>
and the data Frank Sinatra

What I need to do is to read the info within the artist tag type="individual"

I'm sure this can be done but am struggling to find out how. All help is appreciated.

[John.Woody]

Bump Anyone Please

[KernelK]

What is the code/method you are using to read the tags already? Are you simply taking the file as large string and parsing it yourself? Or are you using one of Access's libraries to accomplish this, ie the Microsoft XML library? I have a similar issue that I have kept on the backburner because of the limited info out there. Here are some links to resources that I have been perusing to accomplish the XML parsing. Hope they help you some. Note (Most of the examples I have found are written in VB, but the same principles can usually be appiled to Access VBA).

The first link here probobly contains the most useful information for your situation.

http://msdn.microsoft.com/library/de...l/beginner.asp
http://support.microsoft.com/kb/q271772/
http://msdn.microsoft.com/office/pro...dc_acxmlom.asp
http://msdn.microsoft.com/office/pro...tml/sa03k8.asp

[John.Woody]

Thanks KernelK I'll have a look through these, I am using the code in your top link to read the tags.. no Node_Elements but had completely overlooked their mention of Node_Attribute. I'll work through this and let you know how I get on.

[KernelK]

Have you had any success in reading the attribute information? I have finally been forced (by myself) to work on the xml portion of my application. And after sitting around and studying the DOM I think I've got parsing the xml information down. You would want to reference the attributes collection which is a part of the node object in the DOM. Let me know if you havn't gotten through it yet.

[John.Woody]

Kernelk

No I havn't succeeded yet, there again I havn't had the time to do as much with it as I would have liked but if you have anything to share I would be most grateful.

John

[KernelK]

Here is how you would reference the attribute of the artist node in the xml document:

<?xml version="1.0" ?> 
<compactdiscs>
	<compactdisc>
		<artist type="individual">Frank Sinatra</artist> 
		<title numberoftracks="4">In The Wee Small Hours</title>
	</compactdisc>
</compactdiscs>

Public Sub GetAttr()
    'Be sure to set a reference to Microsoft XML, I have version 5, but I believe a minimum of version 3 will be necessary
    'Instantiate our varibales
    Dim oxmldoc As DOMDocument30
    Dim oNode As IXMLDOMNode
    Set oxmldoc = New DOMDocument30
    
    'Load the xml file, I choose to load a local file in this example as it takes less code than an http request
    oxmldoc.Load ("temp.xml")
    
    'This will set our oNode variable to the root node of the xml file
    Set oNode = oxmldoc.selectSingleNode("compactdiscs")
    
    'From the root node, this is how you reference the attribute that you want for the first compactdisc entity
    Debug.Print oNode.childNodes(0).childNodes(0).Attributes(0).nodeTypedValue
    
End Sub

Basically, once you have a primary reference to one of the nodes (I use the root node), you can reference the sub or child nodes through the childnodes collection (it takes numerical arguments starting base 0). As you can see, every node has a childnodes collection, so to traverse down the nodes, you just reference the next collection!

The information you were trying to get at is a part of the attributes collection for the artist node, so we had to go down two nodes from the root, and then pull the info from the attributes collection. You can check out the structure of the oNode object by putting a breakpoint at the debug.print line, and then open up your locals window and expand your oNode object. You can then check out the childnodes collections and the values to see how to get what you need.

[John.Woody]

Thanks KernelK

I'll work my way through this. It looks very interesting. I'll see how I can apply it.

Thanks again for your help

John

[John.Woody]

I found the above code worked to an extent but won't read everything in a more complex document. I have played about with the code from the MSDN library and got the following to read the whole document. It still needs refining but it gives me the data output I need.

The xml File

<?xml version="1.0"?>

<mycomputer>

   <pc type="Laptop" brand="Toshiba" model="Tecra 8000">

      <type type1="Laptop1" brand1="Toshiba1" model1="Tecra 80001">Laptop</type>

      <brand>Toshiba</brand>

      <model>Tecra 8000</model>

      <processor>300 Mhz Pentium II</processor>

      <ram>128 MB</ram>

      <drives>

         <drive>9 MB Hard Disk</drive>

         <drive>1.4 MB Floppy Disk</drive>

         <drive>CD-ROM</drive>

      </drives>

      <display>14 inch active matrix LCD panel</display>

      <modem>Toshiba internal V.90</modem>

      <network>Xircom Cardbus Ethernet II 10/100</network>

   </pc>

   <case brand="USL" color="black" fabric="vinyl"/>

</mycomputer>

Then the following code in a module

Public Sub LoadDocument()
Dim xdoc As DOMDocument30
Dim xmlDoc As String

xmlDoc = "Yourpath\MyPc.xml"

Set xdoc = New DOMDocument30
xdoc.validateOnParse = False
If xdoc.Load(xmlDoc) Then
   ' The document loaded successfully.
   ' Now do something intersting.
    DisplayNode xdoc.childNodes, 0, xdoc

Else
   ' The document failed to load.
   Dim strErrText As String
   Dim xPE As MSXML2.IXMLDOMParseError
   ' Obtain the ParseError object
   Set xPE = xdoc.parseError
   With xPE
      strErrText = "Your XML Document failed to load" & _
        "due the following error." & vbCrLf & _
        "Error #: " & .errorCode & ": " & xPE.reason & _
        "Line #: " & .Line & vbCrLf & _
        "Line Position: " & .linepos & vbCrLf & _
        "Position In File: " & .filepos & vbCrLf & _
        "Source Text: " & .srcText & vbCrLf & _
        "Document URL: " & .url
    End With

    MsgBox strErrText, vbExclamation
End If

Set xPE = Nothing

End Sub

Public Sub DisplayNode(ByRef Nodes As MSXML2.IXMLDOMNodeList, _
   ByVal Indent As Integer, xdoc As DOMDocument30)
   
Dim xNode As IXMLDOMNode
Dim oNode As IXMLDOMNode
Dim xattr As IXMLDOMAttribute
Dim xattr1  As IXMLDOMAttribute
Dim xElem As IXMLDOMElement
Dim strSQL As String
Dim strSQL1 As String

   Indent = Indent + 1
    
On Error Resume Next

   For Each xNode In Nodes
      
      Debug.Print Space$(Indent) & xNode.parentNode.nodeName & _
            ":" & xNode.nodeValue & ":" & xNode.nodeTypedValue & _
            ":" & xNode.nodeType & ":";
      
        For Each xattr In xNode.parentNode.Attributes
        strSQL1 = strSQL1 & ": " & xattr.BaseName
        strSQL = strSQL & ": " & xattr.Value
        Next xattr
        
        For Each xattr1 In xNode.Attributes
        strSQL1 = strSQL1 & ": " & xattr1.BaseName
        strSQL = strSQL & ": " & xattr1.Value
        Next xattr1
    

    If xNode.hasChildNodes Then
        DisplayNode xNode.childNodes, Indent, xdoc
      
    End If
    Next xNode
   
End Sub

If anyone refines or improves on this please let me know.
Getting there

[KernelK]

Code looks good. I'd almost given up on hearing from you! Looks like your making decent progress with it too. My XML parsing has been put on the back burner once again however as a new slew of bugs with my current project just cropped up. I am still amazed that with the huge adoption of XML in data transfer that there is so little information of using it within Access.