Modifying XML file using DOM in Access VBA (1 Viewer)

S

Salbrox

Registered User.
Local time
Today, 11:59
Joined
Nov 10, 2012
Messages
49
Hi folks,

I am having an awful problem. I have an xml file structured like this:

Code: 
<root>
<T_IMPORT>
	<VIEW_NAME>0001</VIEW_NAME>
	<IMAGE_FILE>0001.jpg</IMAGE_FILE>
	<T_COMMENTS>
		<COMMENT_NO>Comment 1</COMMENT_NO>
		<STATUS>New</STATUS>
		<USER>user1</USER>
		<COMMENT>001sdfgsdfghsdfgsdfgasdf</COMMENT>
	</T_COMMENTS>
	<T_COMMENTS>
		<COMMENT_NO>Comment 2</COMMENT_NO>
		<STATUS>New</STATUS>
		<USER>User2</USER>
		<COMMENT>002sdfgsdfgsdfgsdfg</COMMENT>
	</T_COMMENTS>
</T_IMPORT>
<T_IMPORT>
	<VIEW_NAME>0002</VIEW_NAME>
	<IMAGE_FILE>0002.jpg</IMAGE_FILE>
	<T_COMMENTS>
		<COMMENT_NO>Comment 1</COMMENT_NO>
		<STATUS>New</STATUS>
		<USER>User3</USER>
		<COMMENT>003sdfgsdfgsdfgsdfgsdf</COMMENT>
	</T_COMMENTS>
	<T_COMMENTS>
		<COMMENT_NO>Comment 2</COMMENT_NO>
		<STATUS>New</STATUS>
		<USER>User5</USER>
		<COMMENT>TESTCOMMENT!</COMMENT>
	</T_COMMENTS>
</T_IMPORT>
</root>

I need to copy the "<VIEW_NAME>" node and place it inside each "<T_COMMENTS>" node for each "<VIEW_NAME>" node.
I need to do this so that I can import the xml file as two tables linked by [VIEW_NAME]

The result would be something like this:

Code: 
<root>
<T_IMPORT>
	<VIEW_NAME>View 1</VIEW_NAME>
	<IMAGE_FILE>0001.jpg</IMAGE_FILE>
	<T_COMMENTS>
		<VIEW_NAME>View 1</VIEW_NAME>
		<COMMENT_NO>Comment 1</COMMENT_NO>
		<STATUS>New</STATUS>
		<USER>user1</USER>
		<COMMENT>001sdfgsdfghsdfgsdfgasdf</COMMENT>
	</T_COMMENTS>
	<T_COMMENTS>
		<VIEW_NAME>View 1</VIEW_NAME>
		<COMMENT_NO>Comment 2</COMMENT_NO>
		<STATUS>New</STATUS>
		<USER>User2</USER>
		<COMMENT>002sdfgsdfgsdfgsdfg</COMMENT>
	</T_COMMENTS>
</T_IMPORT>
<T_IMPORT>
	<VIEW_NAME>View 2</VIEW_NAME>
	<IMAGE_FILE>0002.jpg</IMAGE_FILE>
	<T_COMMENTS>
		<VIEW_NAME>View 2</VIEW_NAME>
		<COMMENT_NO>Comment 1</COMMENT_NO>
		<STATUS>New</STATUS>
		<USER>User3</USER>
		<COMMENT>003sdfgsdfgsdfgsdfgsdf</COMMENT>
	</T_COMMENTS>
	<T_COMMENTS>
		<VIEW_NAME>View 2</VIEW_NAME>
		<COMMENT_NO>Comment 2</COMMENT_NO>
		<STATUS>New</STATUS>
		<USER>User5</USER>
		<COMMENT>TESTCOMMENT!</COMMENT>
	</T_COMMENTS>
</T_IMPORT>
</root>

I am almost at the stage of pulling my hair out with this. I can't find any examples of this online. Very little documentation or tutorials on how to do this with VBA. I'm a complete newbie with XML DOM.

Any help at all is much appreciated!
 
spikepl

spikepl

Eledittingent Beliped
Local time
Today, 12:59
Joined
Nov 3, 2010
Messages
6,142
For the reformatting of data forget XML/DOM

1. Rename the file into .TXT
2. Read it into Word
3. Record a macro, where you step by step make one modification that you then can repeat for next range
4. Open the VBA editor in Word and edit the macro as necessary... perhaps put a loop around it if huge number of records. For small number of records just bind it to a keyboard key and run it the necessary number of times.
 
S

Salbrox

Registered User.
Local time
Today, 11:59
Joined
Nov 10, 2012
Messages
49
Thanks for your response spikepl but unfortunately that will not work for what I need.

The xml file will vary in size, it will never be the same file. The number of <T_COMMENTS> within each <T_IMPORT> will be different. One time there may be one another time there may be 35! This needs to distributed to people who can click a button select a file and voila the data is in the database. I need to use the DOM unfortunately.
 
S

Salbrox

Registered User.
Local time
Today, 11:59
Joined
Nov 10, 2012
Messages
49
Is there a way of doing it without the DOM by parsing the xml file as text?

It could be like for every line with <T_IMPORT> copy the next line and paste it after each line containing <T_COMMENTS> until </T_IMPORT> and repeat the process for each <T_IMPORT>...</T_IMPORT> after that.

I don't know enough to do that myself but perhaps it would be easier? Could anyone help me with that our point me in the right direction?
 
gemma-the-husky

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 11:59
Joined
Sep 12, 2006
Messages
15,650
an xml file is no different to any other text file really, other than that the data is specifically formatted.

so, the trouble is that inserting data within a text file, needs the whole file reconstructing to produce the new file. Easy enough to do, unless the file is abosolutely enormous.

one other issue is that if part of the XML file includes totals or data counts based on the elements, then changing data somewhere may have effects on other parts of the file. it doesn't sound like that is the problem here though.

the dom makes it somewhat easier to handle nodes, but more when reading the xml maybe, than writing one.
 
B

ByteMyzer

AWF VIP
Local time
Today, 03:59
Joined
May 3, 2004
Messages
1,409
Hello, Salbrox,

To do this via the DOMDocument object method, you could try something like this:
Code: 
[COLOR="Navy"]Dim[/COLOR] xmlDoc [COLOR="navy"]As Object
Dim[/COLOR] xmlImps [COLOR="navy"]As Object
Dim[/COLOR] xmlImp [COLOR="navy"]As Object
Dim[/COLOR] xmlView [COLOR="navy"]As Object
Dim[/COLOR] xmlComment [COLOR="navy"]As Object
Dim[/COLOR] xmlNew [COLOR="navy"]As Object

Set[/COLOR] xmlDoc = CreateObject("MSXML2.DOMDocument")

xmlDoc.Load "C:\Source.xml"

[COLOR="navy"]Set[/COLOR] xmlImps = xmlDoc.selectNodes("//T_IMPORT")
[COLOR="navy"]For Each[/COLOR] xmlImp [COLOR="navy"]In[/COLOR] xmlImps
    [COLOR="Navy"]Set[/COLOR] xmlView = xmlImp.selectNodes("VIEW_NAME")
    [COLOR="navy"]For Each[/COLOR] xmlComment [COLOR="navy"]In[/COLOR] xmlImp.selectNodes("T_COMMENTS")
        [COLOR="navy"]Set[/COLOR] xmlNew = xmlView(0).cloneNode([COLOR="navy"]True[/COLOR])
        xmlComment.insertBefore xmlNew, xmlComment.childNodes.Item(0)
    [COLOR="navy"]Next[/COLOR] xmlComment
[COLOR="navy"]Next[/COLOR] xmlImp

xmlDoc.Save "C:\New.xml"
 
S

Salbrox

Registered User.
Local time
Today, 11:59
Joined
Nov 10, 2012
Messages
49
Hey guys thanks for the helpful responses. In the end I discovered XSLT. I used an XSL file to transform the XML to a HTML table before importing. It worked beautifully!
 
K

kurtolsen

New member
Local time
Today, 12:59
Joined
Sep 21, 2013
Messages
1
Can you tell me where to find documentation to DOMDocument object methods using VBA?
Thanks
Kurt
 
jdraw

jdraw

Super Moderator
Staff member
Local time
Today, 06:59
Joined
Jan 23, 2006
Messages
15,377
For the benefit of readers and others searching the subject, could you post a copy of the xsl file?


ByteMyzer,
Can you post links you recommend for XML, DOM etc with vba? It seems good references and/or samples are very hard to find.
Thanks.
 
Last edited:
T

TomcatKzn

New member
Local time
Today, 13:59
Joined
Nov 20, 2013
Messages
1
Salbrox said:
Hey guys thanks for the helpful responses. In the end I discovered XSLT. I used an XSL file to transform the XML to a HTML table before importing. It worked beautifully!
Click to expand...

Please could you post more details of this solution for us.
 
M

Marizel

New member
Local time
Today, 06:59
Joined
Dec 28, 2013
Messages
1
Since the OP seems to not be posting back, I'd like to contribute an answer I found on another site, since I've spent the last week searching for a solution to the same problem with an xml file. I've made modifications from the sample on the other site and it seems to work as a transform to import the xml shown above to Access and keep the VIEW_NAME id in both tables. But I'm brand new at this, so I may have missed something. At any rate, hope it's a starting point for people. Check original site for more details.

stackoverflow.com/questions/19007311/xlst-copy-id-from-one-childnode-to-another-childnode
(sorry, it won't let me post an actual link, same with the second line below you'll need to fix)

photos.xslt
Code: 
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="2.0" xmlns:xsl="ht2p [clslsl] www [dt] w3 [dt] org/1999/XSL/Transform">
    <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

    <!-- Identity to copy all elements -->
    <xsl:template match="@*|node()">
        <xsl:copy>
            <xsl:apply-templates select="@*|node()" />
        </xsl:copy>
    </xsl:template>

    <!-- Match on element child and copy this element, but on top add a new element
          <id> and fill this with the ancestor element called parent and it's child id -->
    <xsl:template match="T_COMMENTS">
        <xsl:copy>
            <xsl:apply-templates select="@*" /> <!-- Copy attributes first because we will add a node after this -->
            <VIEW_NAME><xsl:value-of select="ancestor::T_IMPORT/VIEW_NAME" /></VIEW_NAME>
            <xsl:apply-templates select="node()" />
        </xsl:copy>
    </xsl:template>
</xsl:stylesheet>
 
You must log in or register to reply here.

Similar threads

D
Solved Sync/delivery problems multiple users editing the same Access form.
2 3
Replies
58
Views
2,447
Pat Hartman
P
monheimx9
Generating QR Code for Access using the qr-rs library/CLI written in Rust
Replies
4
Views
947
monheimx9
monheimx9
J
Solved Insert xml data from responsetext to table in access with VBA
Replies
14
Views
1,366
john_gringo
J
C
Export and import data back into DB using XML
Replies
8
Views
678
Chandrajit
C
P
Limiting records displayed in combobox to current user
Replies
2
Views
589
psyc0tic1
P

Users who are viewing this thread

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