View Single Post
  #1 (permalink)  
Old April 8th, 2013, 01:41 PM
daurick daurick is offline
Registered User
Points: 5, Level: 1
Points: 5, Level: 1 Points: 5, Level: 1 Points: 5, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2013
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default create xml file from Access 2003 database

trying to generate an xml file from within Access 2003 using VBA: new to xml coding, here is my code and output:
Code:
 and '*****************************************************************XML
     ' now lets do the info table as an xml file
     outputFileNum = FreeFile
      On Error GoTo errend
    Dim objDoc As MSXML2.DOMDocument
    Dim objNode As MSXML2.IXMLDOMNode
    Dim objRoot As MSXML2.IXMLDOMElement
    Dim objElem As MSXML2.IXMLDOMElement
    Set objDoc = New DOMDocument
    objDoc.resolveExternals = True
    Set objNode = objDoc.createProcessingInstruction("xml", "version='1.0' encoding='UTF-8'")
 
    Set objNode = objDoc.insertBefore(objNode, _
                                      objDoc.childNodes.Item(0))
  Set objRoot = objDoc.createElement("informationtable")
    
    Set objDoc.documentElement = objRoot
    
      objRoot.setAttribute "xml-stylesheet", " type=text/xsl href=INFO-TABLE_X01.xsl"
       'objRoot.setAttribute "xsi: schemaLocation", "http://www.sec.gov/edgar/document/thirteenf/informationtable eis_13FDocument.xsd"
      objRoot.setAttribute "xmlns:xsi", "http://www.w3.org/2001/XMLSchema-instance"
      objRoot.setAttribute "xmlns:n1", "http://www.sec.gov/edgar/document/thirteenf/informationtable"
      objRoot.setAttribute "xmlns", "http://www.sec.gov/edgar/document/thirteenf/informationtable"
    Set objDoc.documentElement = objRoot
     
    Set objNode = objDoc.insertBefore(objNode, _
                                      objDoc.childNodes.Item(1))
       
     
                                      
savethefile:
     objDoc.Save strSubmitFilenamexml
    xn = Shell("notepad.exe " & strSubmitFilenamexml, vbMaximizedFocus)
    Exit Sub
    
errend:
MsgBox err.Description

FMsgBox "There was an error creating the XML portion of the report.@@Check that you have 7 days of data. The reports will still be created.@" & err.Description
    GoTo savethefile
Exit Sub
toomany:
    FMsgBox "There was an error creating the XML portion of the report.@@Check the XML file and ensure that there is only 7 days of data."
     GoTo savethefile

End Sub
'************************************************* ****************XML
' now lets do the info table as an xml file
outputFileNum = FreeFile
On Error GoTo errend
Dim objDoc As MSXML2.DOMDocument
Dim objNode As MSXML2.IXMLDOMNode
Dim objRoot As MSXML2.IXMLDOMElement
Dim objElem As MSXML2.IXMLDOMElement
Set objDoc = New DOMDocument
objDoc.resolveExternals = True
Set objNode = objDoc.createProcessingInstruction("xml", "version='1.0' encoding='UTF-8'")

Set objNode = objDoc.insertBefore(objNode, _
objDoc.childNodes.Item(0))
Set objRoot = objDoc.createElement("informationtable")

Set objDoc.documentElement = objRoot

objRoot.setAttribute "xml-stylesheet", " type=text/xsl href=INFO-TABLE_X01.xsl"
'objRoot.setAttribute "xsi: schemaLocation", "http://www.sec.gov/edgar/document/thirteenf/informationtable eis_13FDocument.xsd"
objRoot.setAttribute "xmlns:xsi", "http://www.w3.org/2001/XMLSchema-instance"
objRoot.setAttribute "xmlns:n1", "http://www.sec.gov/edgar/document/thirteenf/informationtable"
objRoot.setAttribute "xmlns", "http://www.sec.gov/edgar/document/thirteenf/informationtable"
Set objDoc.documentElement = objRoot

Set objNode = objDoc.insertBefore(objNode, _
objDoc.childNodes.Item(1))



savethefile:
objDoc.Save strSubmitFilenamexml
xn = Shell("notepad.exe " & strSubmitFilenamexml, vbMaximizedFocus)
Exit Sub

errend:
MsgBox err.Description

FMsgBox "There was an error creating the XML portion of the report.@@Check that you have 7 days of data. The reports will still be created.@" & err.Description
GoTo savethefile
Exit Sub
toomany:
FMsgBox "There was an error creating the XML portion of the report.@@Check the XML file and ensure that there is only 7 days of data."
GoTo savethefile

End Sub
************************************************** *

here is xml created:

<?xml version="1.0" encoding="UTF-8"?>
<informationtable xml-stylesheet=" type=text/xsl href=INFO-TABLE_X01.xsl" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:n1="http://www.sec.gov/edgar/document/thirteenf/informationtable" xmlns="http://www.sec.gov/edgar/document/thirteenf/informationtable"/>

************************************************** ****

here is the xml I need to create:

<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="INFO-TABLE_X01.xsl"?>
<informationTable xsi:schemaLocation="http://www.sec.gov/edgar/document/thirteenf/informationtable eis_13FDocument.xsd"
xmlns="http://www.sec.gov/edgar/document/thirteenf/informationtable"
xmlns:n1="http://www.sec.gov/edgar/document/thirteenf/informationtable"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<infoTable>
<nameOfIssuer>ISSUER NAME ONE</nameOfIssuer>
<titleOfClass>COM NEW</titleOfClass>
<cusip>373891034</cusip>
<value>42067</value>
<shrsOrPrnAmt n1:shrsPrnType="SH">804500</shrsOrPrnAmt>
<investmentDiscretion>SOLE</investmentDiscretion>
<otherManager>1</otherManager>
<votingAuthority>
<Sole>804500</Sole>
<Shared>0</Shared>
<None>0</None>
</votingAuthority>

Last edited by daurick; April 8th, 2013 at 01:49 PM. Reason: removed test messages. add code tags
Reply With Quote