Import XML

F

Fooddeveloper

New member
Local time
Today, 10:49
Joined
Aug 11, 2022
Messages
9
Hello,

I use Access 2016 and try to import a XML-file with the command
Code: 
Application.ImportXML DataSource:="N8E1853 - REPORT - Genetic lifestyle analysis.xml", ImportOptions:=acStructureAndData
.

The file starts with
Code: 
<?xml version="1.0" encoding="utf-8"?>
<book>
<section>

but I get an error message "Runtimeerror '-2146697203 (800c000d)': The specified protokoll is not known."

Do I have to insert "some more" or a different header?

best regards from Austria
Martin
 
Have you got the library included in References?
 
you post the Whole structure of the file.

shouldn't you be using acAppendData instead of acStructureAndData?
 
Fooddeveloper said:
Code: 
Application.ImportXML DataSource:="N8E1853 - REPORT - Genetic lifestyle analysis.xml", ImportOptions:=acStructureAndData
.
[...]
but I get an error message "Runtimeerror '-2146697203 (800c000d)': The specified protokoll is not known."
Click to expand...
Use the full file name to the XML file, including the path.
 
The first lines of the structure:

Code: 
<?xml version="1.0" encoding="utf-8"?>
<book>
<section>
  <name>GENERAL - Letter Module - DNA4ME</name>
  <code>LETTER_UK3JD | LETTER</code>
  <tokens>
    <token type="pageHeading">
      <text>ANSCHREIBEN</text>
    </token>
    <token type="text">
      <text>Sehr geehrte Frau  xxx,</text>
      <font>regular</font>
      <size>18</size>
    </token>
    <token type="text">
      <text>Ihre Probe für die Analyse ist am 12/06/2018 bei uns im Labor eingetroffen und wurde anschließend nach höchsten Labor-Qualitätsstandards untersucht. Die Ergebnisse wurden anschließend von zwei unabhängigen Genetikern und Molekularbiologen ausgewertet und freigegeben. Nach der Freigabe wurde Ihr persönlicher Bericht individuell für Sie zusammengestellt. Diesen möchten wir Ihnen hiermit in der gewünschten Form übermitteln.</text>
      <font>regular</font>
      <size>12</size>
    </token>
    <token type="text">
      <text>Wir bedanken uns herzlich für Ihr Vertrauen und hoffen, dass Sie mit unserem Service zufrieden sind. Wir freuen uns über Ihre Fragen und Anregungen, denn nur so können wir unseren Service kontinuierlich verbessern.</text>
      <font>regular</font>
      <size>12</size>
    </token>
    <token type="text">
      <text>Wir hoffen, die Analyse erfüllt Ihre Erwartungen.</text>
      <font>regular</font>
      <size>12</size>
    </token>
    <token type="text">
      <text>Mit freundlichen Grüßen</text>
      <font>regular</font>
      <size>12</size>
    </token>
    <token type="imageWithLabel">
      <name>Signature new.jpg</name>
      <override></override>
      <height>16</height>
      <width>55.65217391304348</width>
      <rotation>0</rotation>
      <labels>
        <label>
          <coordinateX>24.99999999999999</coordinateX>
          <coordinateY>181.1458333333333</coordinateY>
          <content>Dr. Daniel Wallerstorfer BSc.<br>Labordirektor<br>Novogenia GmbH</content>
          <textColor> #000000</textColor>
          <fontSize> 11</fontSize>
          <fontCode>regular</fontCode>
          <cellWidth>0</cellWidth>
          <alignment>L</alignment>
        </label>
      </labels>
      <link></link>
      <posX></posX>
      <posY></posY>
      <alignment></alignment>
    </token>
    <token type="imageWithLabel">
      <name>SignaturRohrmanstorfer.png</name>
      <override></override>
      <height>16</height>
      <width>37.73913043478261</width>
      <rotation>0</rotation>
      <labels>
        <label>
          <coordinateX>84</coordinateX>
          <coordinateY>181.1458333333333</coordinateY>
          <content> yyy xxx, MSc.<br>Laborleiter<br>Novogenia GmbH</content>
          <textColor> #000000</textColor>
          <fontSize> 11</fontSize>
          <fontCode>regular</fontCode>
          <cellWidth>0</cellWidth>
          <alignment>L</alignment>
        </label>
      </labels>
      <link></link>
      <posX></posX>
      <posY></posY>
      <alignment></alignment>
    </token>
    <token type="imageWithLabel">
      <name>Signaturexxx.png</name>
      <override></override>
      <height>16</height>
      <width>55.02645502645503</width>
      <rotation>0</rotation>
      <labels>
        <label>
          <coordinateX>143</coordinateX>
          <coordinateY>181.1458333333333</coordinateY>
          <content>Dr. asdf asdf<br>Geschäftsführer<br>asdf GmbH</content>
          <textColor> #000000 </textColor>
          <fontSize> 11</fontSize>
          <fontCode>regular</fontCode>
          <cellWidth>0</cellWidth>
          <alignment>L</alignment>
        </label>
      </labels>
      <link></link>
      <posX></posX>
      <posY></posY>
      <alignment></alignment>
    </token>
  </tokens>
</section>
<section>
  <name>REPORT - LIFESTYLE - Details and Chapter toc</name>
  ...
</book>

As the whole file consists of app 69000 lines I can´t copy all of them into here.

And as I don´t know the structure of the table I wanted to let it build. That´s why acStructureAndData ...
 
sonic8 said:
Use the full file name to the XML file, including the path.
Click to expand...
Good point!

Now I get a new runtimeerror:
"
RuntimeError '31593': ~ Error on proceeding the XML-Schema in file 'N8E1853 - REPORT - Genetic lifestyle analysis.xml'.

The name contained in the item's end tag must match the item type in the start tag.
"

But I think the end tag matches:
Code: 
...
<section>
  <name>GENERAL - Handwritten Notes</name>
  <code>ADDITIONALINFO_NOTES</code>
  <tokens>
    <token type="text">
      <text>NOTIZEN:</text>
      <font>medium</font>
      <size>14</size>
    </token>
  </tokens>
</section>
</book>

<book>... </book> --> thats the beginning tag and the end tag?
 
Fooddeveloper said:
The name contained in the item's end tag must match the item type in the start tag.
Click to expand...
I'm not sure if it is the cause of this error, but your raw <br> HTML tags are not valid inside the XML. You must encode them as &lt;br&gt;.
EDIT: There are no raw HTML tags in the XML, they are encoded properly. I was mislead by something decoding them for the above post.
 
Last edited:
Attached sample file to let you see all the data. I cleared all names and companies. I had to change the filetype from xml to txt to upload it.
 

Attachments

which Program did you use to Export this xml?
probably not a valid xml (as far as access is concern).
normal xml, for access to recognize, is that the Tag don't have any attributes in them.
 
Last edited:
Here's a different approach. I don't actually try to automate any process for xml. I just couldn't make sense of the inbuilt procedures.

I have uploaded a code sample a long while ago that walks through every node of an xml file, and produces a structure plan of the file in an access table. So, using that as a separate exercise, I then work out which fields I want to process.

Then, to do the actual import, I walk through the whole file a node at a time, and based on my previous analysis, I know how to process each node, and each record. It's very fast. A second or two for large files.

Once I develop the import process, it handles subsequent versions of this same file with no work required - but will report finding unexpected data elements and so on.
 
arnelgp said:
which Program did you use to Export this xml?
probably not a valid xml (as far as access is concern).
normal xml, for access to recognize, is that the Tag don't have any attributes in them.
Click to expand...
I get it from an external source. No idea which program they use. They are doctors and physicans, I don´t think that anyone there knows about it.
 
Fooddeveloper said:
I get it from an external source.
Click to expand...
based on it's structure, it is a report of some sort.
i don't think you can import that as "normal" xml file to access.
it will only be importable on the same program you export it.
 
If I try in Word I get
1660295480875.png
 
the msxml library module has a boolean setting to determine whether the xml is valid or not. I forget the name offhand, but it's something you would always check before doing anything with an xml file normally, I would think.
 
You must log in or register to reply here.

Similar threads

D
XML import from https - error
Replies
9
Views
1,408
theDBguy
theDBguy
M
Importing Multiple XML in Access from Folder
Replies
17
Views
5,255
theDBguy
theDBguy
isladogs
  • Locked
View & Edit IMEX Data Task Specifications
Replies
4
Views
6,044
isladogs
isladogs
S
Import multiple XML files into MS Access
Replies
12
Views
4,398
sam28bul
S
W
xml file import with identitiy transform xslt file
Replies
1
Views
1,338
Wysy
W

Users who are viewing this thread

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