[AccessD] XML

mwp.reid at queens-belfast.ac.uk wrote:

>Was the ability to import and export XML via the GUI (or using ADO) in Access 
>available in any version prior to A XP? 
>  
>
 You can handle XML without ADO from Access 97 just a little more 
trouble. The following example reads in an XML file (well I am using a 
string), verifies via a schema xsd file, uses an xsl file to select 
nodes via XSLT and inserts the values into a table. I am using MS XML 
version 5.0 but you could go back to 3.0. The table tblTest has to 
preexist with a field RECORD.

Sub testxml()
Dim sXML As String

sXML = "<RECORDS><RECORD><Value>1234</Value></RECORD>" & _
                "<RECORD><Value>1235</Value>" & _
       "</RECORD></RECORDS>"


If AddDatabase(sXML) Then
    MsgBox "Added XML OK"
Else
    MsgBox " XML failed"
End If
End Sub

 Function AddDatabase(pseDataXML As String) As Boolean

    Dim oResultsXML     As DOMDocument50
    Dim oTransformToSQL As DOMDocument50
    Dim sSQL            As String
    Dim xmlError As IXMLDOMParseError
    Dim dbs As DAO.Database
   
Dim xmlSchema As XMLSchemaCache50
Debug.Print CurrentProject.Path
Set xmlSchema = New XMLSchemaCache50

xmlSchema.Add "http://tempuri.org/WellFormed.xsd", _
              CurrentProject.Path & "\test.xsd"


    Set oResultsXML = New DOMDocument50
    Set oTransformToSQL = New DOMDocument50
    Set oResultsXML.schemas = xmlSchema
    Set dbs = CurrentDb()
   
   oResultsXML.async = False
   oResultsXML.validateOnParse = True
   oResultsXML.resolveExternals = False

    Debug.Print "Preparing to add to database: Loading XSL"
    If oTransformToSQL.Load(CurrentProject.Path & "\test.xsl") Then
   
        Debug.Print "Loading results XML"
        If oResultsXML.loadXML(pseDataXML) Then
            Debug.Print "Transforming XML with XSL"
            sSQL = oResultsXML.transformNode(oTransformToSQL)
   
            Debug.Print sSQL
           
            Dim MyStringArray() As String

              'use AND string as delimiter
             MyStringArray = Split(sSQL, "AND")
            ' Access can't use multiple SQL  inserts in single call so 
seperate
            For i = 0 To UBound(MyStringArray) - 1
             Debug.Print MyStringArray(i)

              dbs.Execute (MyStringArray(i))

            Next
        Else
            Debug.Print "Unable to load result XML:" & vbCrLf & pseDataXML
            Set xmlError = oResultsXML.parseError

            reportParseError xmlError
            GoTo AddDatabase_Exit
        End If
    Else
        Debug.Print "Unable to load XSL:" & vbCrLf & psXSL
        Set xmlError = oTransformToSQL.parseError
        reportParseError xmlError
        GoTo AddDatabase_Exit
    End If
   
    AddDatabase = True

AddDatabase_Exit:

    Set oResultsXML = Nothing
    Set oTransformToSQL = Nothing

End Function
Function reportParseError(err As IXMLDOMParseError)

  Dim s As String
  Dim r As String
  Dim i As Long
 
   s = ""
  For i = 1 To err.linepos - 1
    s = s & " "
  Next
  r = "XML Error loading " & err.url & " * " & err.reason
    'show character postion of error; tired of counting
  If (err.Line > 0) Then
    r = "at line " & err.Line & ", character " & err.linepos & vbCrLf & _
         err.srcText & vbCrLf & s & "^"
  End If
  Debug.Print r
  End Function

test.xsl
------
<xsl:stylesheet version="1.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
<xsl:output method="text" />
  <xsl:template match="/RECORDS">
    <xsl:for-each select="./RECORD"> Insert INTO tblTest (RECORD)
    VALUES('<xsl:value-of select="Value" />') AND
    </xsl:for-each>
 </xsl:template>
</xsl:stylesheet>

test.xsd
-----------------
<?xml version="1.0" ?>
<xs:schema targetNamespace="http://tempuri.org/WellFormed.xsd"
  xmlns="http://tempuri.org/WellFormed.xsd"
  xmlns:xs="http://www.w3.org/2001/XMLSchema"
  attributeFormDefault="qualified"
  elementFormDefault="qualified">
  <xs:element name="Test">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="Name" type="xs:string" minOccurs="0" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>