Databases
StackOverflow2013
Postsdbo
POReturn all nodes from XML file, even when they are empty
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POReturn all nodes from XML file, even when they are empty
    primarykey
    data
    text
    <p>I have an XML file retrieved via WSDL in Access 2010 via VBA. The XML file is sitting in this variable</p> <pre><code>Dim xmlDoc As New DOMDocument60 </code></pre> <p>The part of the XML I'm interested in looks like the below and basically just reiterates itself for every <code>UserBean</code>. A <code>UserBean</code> is basically a user account in a system.</p> <pre><code>&lt;UserBean xsi:type="ns1:UserBean"&gt; &lt;primaryKey xsi:type="xsd:string"&gt;49084&lt;/primaryKey&gt; &lt;updateIndex xsi:type="xsd:int"&gt;14&lt;/updateIndex&gt; &lt;deleted xsi:type="xsd:boolean"&gt;false&lt;/deleted&gt; &lt;loginID xsi:type="xsd:string"&gt;61420313556&lt;/loginID&gt; &lt;name xsi:type="xsd:string"&gt;Andrew Mills&lt;/name&gt; &lt;teams xsi:type="soapenc:Array" soapenc:arrayType="xsd:string[1]"&gt; &lt;string xsi:type="xsd:string"&gt;Maintenance&lt;/string&gt; &lt;/teams&gt; &lt;timezone xsi:type="xsd:string"&gt;Australia/Brisbane&lt;/timezone&gt; &lt;userTypePK xsi:type="xsd:string"&gt;3776&lt;/userTypePK&gt; &lt;description xsi:type="xsd:string"/&gt; &lt;emailAddress xsi:type="xsd:string"/&gt; &lt;phoneNumber xsi:type="xsd:string"/&gt; &lt;faxNumber xsi:type="xsd:string"/&gt; &lt;pagerNumber xsi:type="xsd:string"/&gt; &lt;mobileNumber xsi:type="xsd:string"&gt;61420313556&lt;/mobileNumber&gt; &lt;securityQuestion xsi:type="xsd:string"&gt;__INVALID&lt;/securityQuestion&gt; &lt;securityAnswer xsi:type="xsd:string"/&gt; &lt;synchronisation xsi:type="soapenc:Array" soapenc:arrayType="ns2:SynchronisationBean[0]" xmlns:ns2="http://soap2.nads.econz.co.nz"/&gt; &lt;/UserBean&gt; </code></pre> <p>The problem is that not every field is mandatory to be filled in.<br> Therefore some nodes have no data<br> Using the MSXML2 library in VBA only returns nodes if there is actually text in it. Therefore the code below will return a variable amount of nodes depending on what each userbean contains. For example some users dont have a mobileNumber populated. </p> <pre><code>Set nodes xmlDoc.selectNodes("//UserBean") For Each node in nodes debug.print node.text next node </code></pre> <p>The above code returns a long string that has all of the values of all of the child nodes in it (with respect to the <code>Userbean</code> node), but only the ones that have text. I'm trying to get this into a table in Access and if some of the nodes are missing some of the time, I've got no way of tracking it... or do I? </p> <p>How do I return ALL nodes whether they are populated or not<br> OR<br> How do I identify the name of the node that has the text so I know where to put the value into the table in Access?</p> <p><em><strong>UPDATE</em></strong><br> Further to the below comments, what i'm after is a list of Userbeans, which are in themselves a list...so in fact i'm after a list of lists...so with that in mind I tried the below but it failed at the first <code>For Each</code> loop. Obviously that type of loop can't handle using lists as a counter for another list. Is there a way around this? </p> <pre><code>Dim node As MSXML2.IXMLDOMNode Dim userbeans As MSXML2.IXMLDOMNodeList Dim userbean As MSXML2.IXMLDOMNodeList Set userbeans = xmlDoc.selectNodes("//UserBean") For Each userbean In userbeans '**Type mismatch error here** For Each node In userbean Debug.Print node.nodeName &amp; ":" &amp; node.Text Next node Next userbean </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USzoonosis
    1. USzoonosis
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POReturn all nodes from XML file, even when they are empty
      1. This table or related slice is empty.
      1. VTUpMod
    1. COMaybe: `debug.print node.nodename & "=" & node.text`
      singulars
      1. POReturn all nodes from XML file, even when they are empty
      1. USTim Williams
    2. COThat's close @Tim Williams. The result of your code is `UserBean=4908414false614203Andrew....etc` so the fields aren't getting broken up. The `UserBean` is really a list of nodes, but here its being treated as one node.
      singulars
      1. POReturn all nodes from XML file, even when they are empty
      1. USzoonosis
    3. COIf at all possible, spin up an SQL server engine -- it has FAR better XML capacities than Access. FWIW.
      singulars
      1. POReturn all nodes from XML file, even when they are empty
      1. USDougM
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload