0

The problem mentioned in the title occurs in using selectSingleNodes. I believe it has to do with xPath of the method. I denoted the problematic lines in the code below with "<===== HERE"

In the line HERE A, I tried xnode.SelectSingleNode("corp_name"). This gave no nodes at all. With "/corp_name", no nodes at all, either. But with the current code where "//corp_name" is used, 10 nodes are found.

With the A line being "//corp_name", if in the line HERE B "corp_name" and "/corp_name" are used, the run time error occurred. So I was forced to use "//corp_name" in the line B, too. But, then the problem of the title occurred. Among 10 nodes, only first node is repeated. This seems logical since the method itself returns the first node. But then, how can I extract all the different nodes? I was hoping that the loop would take care of each node. But apperently it does not, or else... This is something I do not understand in the code which I copied from Internet.

My test option seems depleted to nothing. In fact, this is my first try to use API, XML, AND MSXML2, meaning that there should be something I am using without completely understanding. So, it would be a great help if any of you could point out what seems to be misused.

Please help!

===================

Sub extract_corpname_receptno()

Dim xml_obj As MSXML2.XMLHTTP60 Set xml_obj = New MSXML2.XMLHTTP60

base_url = "https://opendart.fss.or.kr/api/list.xml?"
param_api = "&crtfc_key="
param_api_value = CStr(Worksheets("sheet2").Range("k1").Value)
    
api_url = base_url + _
          param_api + param_api_value

xml_obj.Open bstrMethod:="GET", bstrURL:=api_url
xml_obj.send
Debug.Print "The Request was " + xml_obj.statusText

Debug.Print xml_obj.responseText
    
Dim xDoc As MSXML2.DOMDocument60
Dim xNodes As MSXML2.IXMLDOMNodeList
Dim xnode As MSXML2.IXMLDOMNode

Set xDoc = New MSXML2.DOMDocument60

    xDoc.LoadXML (xml_obj.responseText)
    If Not xDoc.LoadXML(xml_obj.responseText) Then
           MsgBox "load error"
    End If
    
Set xNodes = xDoc.SelectNodes("//corp_name") '<===== HERE A

Debug.Print xNodes.Length

Dim xChlNode As IXMLDOMNodeList
Set xChlNode = xDoc.ChildNodes.Item(1).ChildNodes

For Each xChl In xChlNode
    Debug.Print xChl.BaseName
    Debug.Print xChl.NodeType 
Next

For Each xnode In xNodes
    Debug.Print "----------------------------------"
    'Debug.Print xnode.Text
    Debug.Print xnode.SelectSingleNode("//corp_name").Text '<===== HERE B
    Debug.Print xnode.SelectSingleNode("//rcept_no").Text '<===== HERE C
Next
    
Dim wrksht As Worksheet
Set wrksht = ThisWorkbook.Worksheets("Sheet2")
    
Count = 1
For Each xnode In xNodes

    wrksht.Cells(Count, 1).Value = xnode.SelectSingleNode("//corp_name").Text '<=== HERE B
    wrksht.Cells(Count, 2).Value = xnode.SelectSingleNode("//rcept_no").Text '<=== HERE C

    Count = Count + 1

Next

End Sub

==================

The XML I am trying to parse has the following structure:

 <list>
     <corp_name>AAA</corp_name>
     <rcept_no>111</rcept_no>
 </list>

 <list>
     <corp_name>BBB</corp_name>
     <rcept_no>222</rcept_no>
 </list>

 .....

 <list>
     <corp_name>JJJ</corp_name>
     <rcept_no>ten</rcept_no>
 </list>

===============================

The code above gives only

AAA 111

AAA 111

AAA 111 .... ....

AAA 111

when should give

AAA 111

BBB 222

CCC 333 .... ....

JJJ ten

===================

Pine Paul
  • 1
  • 1
  • Hi Pine Paul, it would be helpful to include the XML you're trying to parse. Looking at your code, you get a list of nodes (xnode) with //corp_name, then loop through list and get the list all over again with //corp_name. In the loop, just use xnode.Text. Or if corp_name contains corp_name elements in the loop use /corp_name not //corp_name. Can't really tell without the XML. – William Walseth Sep 23 '20 at 10:49
  • MSXML2, if I remember right, is very ancient and predates the W3C XPath 1.0 specification, therefore supports its own pre-standardised version of XPath. – Michael Kay Sep 23 '20 at 10:55
  • Thanks all. In response to William, I have added the XML and the result the code produces. Also I rephrased what happened in my previous tests, more in detail. Thanks again. – Pine Paul Sep 24 '20 at 00:14
  • Hi, William, the reason I take out the debug line with xnode.Text is that I need more. As can be seen, the pair of "corp_name" and "rcept_no" are needed, so that the same problem with "rcept_no" should be solved. – Pine Paul Sep 24 '20 at 03:06
  • I stil haven't solved the problem yet. But I am beginning to think that the question to ask is not the one asked, but rather why "corp_name" in the line B gives the run time error? – Pine Paul Sep 25 '20 at 02:41
  • Thanks all. Finally got it right: In line A should be used "//list", in Line B "corp_name". – Pine Paul Sep 26 '20 at 05:33

0 Answers0