Still strugling with XML and DOMDocuments

J

JackKaptijn

Registered User.
Local time
Today, 18:01
Joined
Dec 10, 2012
Messages
38
For weeks I am trying to read an XML file which needs to be processed in an Access database. I can not find the proper way to:

- find a specific node
- iterate from there

The XML file is an international bank file:

Code: 
<?xml version="1.0" encoding="utf-8"?>
<Document xmlns="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02" xmlns:xsi="[URL]http://www.w3.org/2001/XMLSchema-instance[/URL]">
[INDENT]
<GrpHdr>
[INDENT]
<MsgId>001-2013-09-19-18:12:17</MsgId>
<CreDtTm>2013-08-23T18:12:17</CreDtTm>
<NbOfTxs>3</NbOfTxs>
<CtrlSum>140.00</CtrlSum>
<InitgPty>
<Nm>DEMO</Nm>
</InitgPty>
[/INDENT]
</GrpHdr>
<PmtInf>
[INDENT]
<PmtInfId>001-9-1</PmtInfId>
<PmtMtd>DD</PmtMtd>
<BtchBookg>false</BtchBookg>
<NbOfTxs>2</NbOfTxs>
<CtrlSum>95.00</CtrlSum>
<CdtrAcct>
[INDENT]
<Id>
<IBAN>NL52RABO0376585323</IBAN>
</Id>
[/INDENT]
</CdtrAcct>
[/INDENT]
</PmtInf>

<PmtInf>
[INDENT]
<PmtInfId>001-9-2</PmtInfId>
<PmtMtd>DD</PmtMtd>
<BtchBookg>false</BtchBookg>
<NbOfTxs>1</NbOfTxs>
<CtrlSum>45.00</CtrlSum>
<CdtrAcct>
[INDENT]
<Id>
<IBAN>NL52RABO0376585323</IBAN>
</Id>
[/INDENT]
</CdtrAcct>
[/INDENT]
</PmtInf>
[/INDENT]
<Document>
I started quit simple, just wanted to read to node MsgId.
This is the code I use:

Code: 
Private Sub ReadXML_Click()
Dim aDoc As DOMDocument60
Dim aNode As IXMLDOMNode

    Set aDoc = New MSXML2.DOMDocument60
    aDoc.async = False
    aDoc.Load DLookup("C:\Sample.XML")
    
    Set aNode = aDoc.selectSingleNode("//MsgId")

    If aNode Is Nothing Then
        MsgBox  "No nodes selected."
    Else
        MsgBox = aNode.Text
    End If
    Set aDoc = Nothing
Exit Sub

Whatever I try: //MsgId or MsgId the results is "No nodes".
Can someone please help me how to read to XML.

Besides reading one node, the next challenge will be iterating the PmtInf node. There can be 1 in the XML file but also 50.

Thanks in advance.....
Jack
 
I would look into XSLT transformations to parse data out of xml.
 
i would have thought the node was just <msgid> not \\msgid

out of interest, is there only one message per file?
 
OK

I had a look, and the file is being reported as not a valid xml file (cutting and pasting your text into notepad)

i see EXACTLY this

Code: 
<?xml version="1.0" encoding="utf-8"?>
<Document xmlns="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02" xmlns:xsi="[URL]http://www.w3.org/2001/XMLSchema-instance[/URL]">
<GrpHdr>
<MsgId>001-2013-09-19-18:12:17</MsgId>
<CreDtTm>2013-08-23T18:12:17</CreDtTm>
<NbOfTxs>3</NbOfTxs>
<CtrlSum>140.00</CtrlSum>
<InitgPty>
<Nm>DEMO</Nm>
</InitgPty>
</GrpHdr>
<PmtInf>
<PmtInfId>001-9-1</PmtInfId>
<PmtMtd>DD</PmtMtd>
<BtchBookg>false</BtchBookg>
<NbOfTxs>2</NbOfTxs>
<CtrlSum>95.00</CtrlSum>
<CdtrAcct>
<Id>
<IBAN>NL52RABO0376585323</IBAN>
</Id>
</CdtrAcct>
</PmtInf>
<PmtInf>
<PmtInfId>001-9-2</PmtInfId>
<PmtMtd>DD</PmtMtd>
<BtchBookg>false</BtchBookg>
<NbOfTxs>1</NbOfTxs>
<CtrlSum>45.00</CtrlSum>
<CdtrAcct>
<Id>
<IBAN>NL52RABO0376585323</IBAN>
</Id>
</CdtrAcct>
</PmtInf>
<Document>

the file you posted will not open in a xml viewer
the final node should be </Document> not <Document>. (the tag pairs are case sensitive)

try this snippet also. Test whether the file IS a valid xml file before doing any processing at all.

Code: 
    If adoc.parseError <> 0 Then
        Call MsgBox("Sorry: The file is NOT a valid xml file. " & vbCrLf & _
            "Error: " & adoc.parseError)
        Exit Sub
    End If
 
Last edited:
Thanks for your replies. Still not a step further towards the solution...
The XML I included was a part of the Original. The complete one is surely valid. I included it.


The XML file has a header <GrpHdr> and then <PmtInf> several times. The header has information about a bankstatement. The <PmtInf> are the individual transactions. Which I need to consolidate in the financial administration....

Anyone I tip?
 

Attachments

OK - here's a recursive procedure that examines the whole xml file, and finds the node <msgid>

I have just modified this from something I did a long while ago, and I am not sure now of the significance of the "leaf node" test, testing for #text. This code might not be quite accurate in this procedure, but hopefully it will help you progress your work

anyway, this procedure finds every node, including <msgid>.

I wasn't able to get .selectSingleNode working either, and online help talks about setting a namespace - so I suspect the usage you are trying is not quite correct.

Note that I am using ms xml 6, which just uses domdocument, rather than domdocument60 which you had.

Code: 
Option Compare Database
Option Explicit

Private Sub ReadXML_Click()
Dim aDoc As DOMDocument
Dim aNode As IXMLDOMNode
Dim fname As String
    Set aDoc = New DOMDocument
    aDoc.async = False
    fname = CurrentProject.Path & "\" & "sddcore.xml"
 
    If aDoc.parseError <> 0 Then
        Call MsgBox("Sorry: The file is NOT a valid xml file. " & vbCrLf & _
            "Error: " & aDoc.parseError)
        Exit Sub
    End If
    
    aDoc.Load fname
    If aDoc.hasChildNodes Then
        For Each aNode In aDoc.childNodes
            ParseMe aNode
        Next
    Else
        MsgBox ("no child nodes")
    End If
    Exit Sub
End Sub
    
Sub ParseMe(oNode As IXMLDOMNode)
Dim newnode As IXMLDOMNode
Dim leafnode As Boolean
Dim leaftext As String
Dim s As String
Dim nodecount As Long
Dim recid As Long
    
    nodecount = 0
    If oNode.hasChildNodes Then
        For Each newnode In oNode.childNodes
            
            If newnode.nodename = "#text" Then
                'not sure of the significance of this now.
                leafnode = True
                'MsgBox ("leafNode: " & newnode.nodename & ":  " & newnode.Text)
            Else
                nodecount = nodecount + 1
                If nodecount = 1 Then
                    leafnode = False
                    'uncomment this to see every node
                    If newnode.nodename = "msgid" Then
                        MsgBox ("SubNode: " & newnode.nodename & ":  " & newnode.Text)
                    End If
                End If
                
                Call ParseMe(newnode)
            End If
        Next
    End If
    Exit Sub
End Sub
 
A bit further with XML and DOMDocuments

With the help of Gemma I am a bit further.
I can iterate a tag! But a second iteration is not working.

The XML file is included. Renamed to TXT.

This is the code:
Code: 
Private Sub NameS_Click()
    Dim objDoc As DOMDocument60
    Dim objNodeList As IXMLDOMNodeList
    Dim objSubNodeList As IXMLDOMNodeList
    Dim objNode As IXMLDOMNode
    Dim objNode2 As IXMLDOMNode
    Dim objSubNode As IXMLDOMNode
    Set objDoc = New MSXML2.DOMDocument60
    objDoc.async = False
    objDoc.validateOnParse = False
    objDoc.resolveExternals = False
    objDoc.SetProperty "SelectionLanguage", "XPath"
    objDoc.SetProperty "SelectionNamespaces", "xmlns:myNS='urn:iso:std:iso:20022:tech:xsd:pain.008.001.02'"
    '<Document xmlns="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02" xmlns:xsi="[URL]http://www.w3.org/2001/XMLSchema-instance[/URL]">
    strXMLPath = "SDDCore.XML"
    strResult = ""
    If Not objDoc.Load(strXMLPath) Then
        Debug.Print "Load XML failed: " & objDoc.parseError.reason
        Exit Sub
    End If
    Set objNodeList = objDoc.selectNodes("//myNS:PmtInf")
    strResult = strResult & "Number of PmtInf: " & objNodeList.length & vbCrLf
    For Each objNode In objNodeList
        strResult = strResult & "Nodelist 1 : " & objNode.nodeName & vbCrLf
        strN = Array("NbOfTxs", "CtrlSum", "ReqdColltnDt", "IBAN")
        For i = 0 To 2
            Set objSubNode = objNode.selectSingleNode("myNS:" & strN(i))
            strResult = strResult & objSubNode.nodeName & " : " & objSubNode.Text & vbCrLf
        Next i
    
        Set objSubNode = objNode.selectSingleNode("//myNS:IBAN")
        strResult = strResult & objSubNode.nodeName & " : " & objSubNode.Text & vbCrLf
        Set objSubNodeList = objNode.selectNodes("myNS:DrctDbtTxInf")
        strResult = strResult & "Aantal DrctDbtTxInf: " & objSubNodeList.length & vbCrLf
        For Each objNode2 In objSubNodeList
            strResult = strResult & "Nodelist 2 : " & objNode2.nodeName & vbCrLf
            strN = Array("EndToEndId", "InstdAmt", "Nm", "IBAN", "Ref")
            For j = 0 To 4
                Set objSubNode2 = objNode2.selectSingleNode("//myNS:" & strN(j))
                strResult = strResult & objSubNode2.nodeName & " : " & objSubNode2.Text & vbCrLf
            Next j
        Next objNode2
    Next objNode
    MsgBox strResult
End Sub

In the second iteration:
Set objSubNode2 = objNode2.selectSingleNode("//myNS:" & strN(j))
it returns always the results form the first "DrctDbtTxInf" tag....

What is the mistake I am making?????

Please help!

Jack
 

Attachments

JackKaptijn said:
For weeks I am trying to read an XML file which needs to be processed in an Access database. I can not find the proper way to:

- find a specific node
- iterate from there

The XML file is an international bank file:

Code: 
<?xml version="1.0" encoding="utf-8"?>
<Document xmlns="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02" xmlns:xsi="[URL]http://www.w3.org/2001/XMLSchema-instance[/URL]">
[INDENT]
<GrpHdr>
[INDENT]
<MsgId>001-2013-09-19-18:12:17</MsgId>
<CreDtTm>2013-08-23T18:12:17</CreDtTm>
<NbOfTxs>3</NbOfTxs>
<CtrlSum>140.00</CtrlSum>
<InitgPty>
<Nm>DEMO</Nm>
</InitgPty>
[/INDENT]
</GrpHdr>
<PmtInf>
[INDENT]
<PmtInfId>001-9-1</PmtInfId>
<PmtMtd>DD</PmtMtd>
<BtchBookg>false</BtchBookg>
<NbOfTxs>2</NbOfTxs>
<CtrlSum>95.00</CtrlSum>
<CdtrAcct>
[INDENT]
<Id>
<IBAN>NL52RABO0376585323</IBAN>
</Id>
[/INDENT]
</CdtrAcct>
[/INDENT]
</PmtInf>

<PmtInf>
[INDENT]
<PmtInfId>001-9-2</PmtInfId>
<PmtMtd>DD</PmtMtd>
<BtchBookg>false</BtchBookg>
<NbOfTxs>1</NbOfTxs>
<CtrlSum>45.00</CtrlSum>
<CdtrAcct>
[INDENT]
<Id>
<IBAN>NL52RABO0376585323</IBAN>
</Id>
[/INDENT]
</CdtrAcct>
[/INDENT]
</PmtInf>
[/INDENT]
<Document>
I started quit simple, just wanted to read to node MsgId.
This is the code I use:

Code: 
Private Sub ReadXML_Click()
Dim aDoc As DOMDocument60
Dim aNode As IXMLDOMNode

    Set aDoc = New MSXML2.DOMDocument60
    aDoc.async = False
    aDoc.Load DLookup("C:\Sample.XML")
    
    Set aNode = aDoc.selectSingleNode("//MsgId")

    If aNode Is Nothing Then
        MsgBox  "No nodes selected."
    Else
        MsgBox = aNode.Text
    End If
    Set aDoc = Nothing
Exit Sub

Whatever I try: //MsgId or MsgId the results is "No nodes".
Can someone please help me how to read to XML.

Besides reading one node, the next challenge will be iterating the PmtInf node. There can be 1 in the XML file but also 50.

Thanks in advance.....
Jack
Click to expand...

Hi..
here some examples..hope you will find it usefull...

Sub LearnAboutNodes()
Dim xmlDoc As MSXML2.DOMDocument60
Dim xmlNode As MSXML2.IXMLDOMNode

Set xmlDoc = New MSXML2.DOMDocument60
xmlDoc.async = False
xmlDoc.Load ("c:\Apteka\zl6733.xml")

If xmlDoc.hasChildNodes Then
Debug.Print "Number of child Nodes: " & xmlDoc.childNodes.length
For Each xmlNode In xmlDoc.childNodes
Debug.Print "Node name:" & xmlNode.nodeName
Debug.Print vbTab & "Type: " & xmlNode.nodeTypeString & "(" & xmlNode.nodeType & ")"
Debug.Print vbTab & "Text: " & xmlNode.Text
Next xmlNode
End If
Set xmlDoc = Nothing


End Sub

and another ....

Sub IterateThruElements()

Dim xmlDoc As MSXML2.DOMDocument60
Dim xmlNode As MSXML2.IXMLDOMNode
Dim xmlNodeList As MSXML2.IXMLDOMNodeList
Dim myNode As MSXML2.IXMLDOMNode

Set xmlDoc = New MSXML2.DOMDocument60
xmlDoc.async = False
'req.Open "GET", "https://api.zdravstvo.gov.mk/rest/prescriptions/recent/ic67456", False

xmlDoc.Load ("c:\apteka\insurance_all.xml")
'xmlDoc.Load ("https://api.zdravstvo.gov.mk/rest/prescriptions/KG33111")
'https://api.zdravstvo.gov.mk/rest/insurance_types/all
'xmlDoc.Load ("https://api.zdravstvo.gov.mk/rest/prescriptions/KG33111")
Set xmlNodeList = xmlDoc.getElementsByTagName("*")
For Each xmlNode In xmlNodeList
For Each myNode In xmlNode.childNodes
If myNode.nodeType = NODE_TEXT Then
Debug.Print xmlNode.nodeName & "=" & xmlNode.Text
End If
Next myNode
Next xmlNode
Set xmlDoc = Nothing

End Sub


and another...


Sub SelectNodesByCriteria()

Dim xmlDoc As MSXML2.DOMDocument60
Dim xmlNodeList As MSXML2.IXMLDOMNodeList
Dim myNode As MSXML2.IXMLDOMNode

Set xmlDoc = New MSXML2.DOMDocument60
xmlDoc.async = False
xmlDoc.Load ("c:\Apteka\nl30543.xml")
Set xmlNodeList = xmlDoc.selectNodes("//fund_code")
If Not (xmlNodeList Is Nothing) Then
For Each myNode In xmlNodeList
'DoCmd.RunSQL "INSERT INTO tblXML_LEKOVI (MESTO) VALUES myNode.Text"
'DoCmd.RunSQL "INSERT INTO tblXML_LEKOVI (mesto) VALUES (" & myNode.Text & ")"
Debug.Print myNode.Text
Next myNode
End If
Set xmlDoc = Nothing

End Sub

and another...

Sub SelectSingleNode()
'go odbira prvata zapisana vrednost vo node
Dim xmlDoc As MSXML2.DOMDocument60
Dim xmlSingleNode As MSXML2.IXMLDOMNode

Set xmlDoc = New MSXML2.DOMDocument60

xmlDoc.async = False
xmlDoc.Load ("c:\Apteka\oz10763.xml")
Set xmlSingleNode = xmlDoc.SelectSingleNode("//message_mk")
If xmlSingleNode Is Nothing Then
Debug.Print "Nema odbrano node"
Else
Debug.Print xmlSingleNode.Text
End If
Set xmlDoc = Nothing


End Sub
 
irade92,
Could you zip the xml files that you have identified so we can execute the samples?
We do not have access to your c:/ drive.

Thanks.
 
Thanks.
Unfortunately the text fields (Slovenian?) don't print, and I didn't try to translate the text fields.

The xml oz10763.xml was not in the zip.
 
jdraw said:
Thanks.
Unfortunately the text fields (Slovenian?) don't print, and I didn't try to translate the text fields.

The xml oz10763.xml was not in the zip.
Click to expand...

Please send me your XML file and tell me what do you want to read..I will try to help you....My files are modified during my testing and some of them where only response posted to server...the language was Macedonian.. I am sorry for this

Regards
 
I don't have the issue with xml, JackKaptijn is the original poster.
I was interested in your sample functions and files.
I like to record/save samples of different issues/solutions.

Yes Google said Macedonian at one point and Slovenian when I tried to translate something else. Not an issue, I could put in any text for the sample.
 
You must log in or register to reply here.

Similar threads

J
Problem with DOMDocuments
Replies
1
Views
1,107
spikepl
spikepl
J
Problem finding a specific tags in XML
Replies
0
Views
1,669
JackKaptijn
J

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom