[AccessD] XML Import into Access 2003 (2nd retry w/o html)

MartyConnelly martyconnelly at shaw.ca
Fri Nov 3 17:45:08 CST 2006


Umm. I won't go on a rant about using DTD's and XML attributes.
I guess this a standard that has been developed by a committee over 10 
years.
I see, they have been discussing switching to the use of xsd schema's 
for 3 years.
When you see a dtd with a self-contained copyright and a disclaimer
that indemnifies against everything including global warning, I head for 
the hills.

However here is why attributes should not be used this way
http://www.xmlfiles.com/xml/xml_attributes.asp

These are some of the problems using attributes:

attributes can not contain multiple values (elements can)
attributes are not expandable (for future changes)
attributes can not describe structures (like child elements can)
attributes are more difficult to manipulate by program code
attribute values are not easy to test against a DTD

If you start using attributes as containers for XML data, you might end
up with documents that are both difficult to maintain and to manipulate.
What I'm trying to say is that you should use elements to describe your 
data.
Use attributes only to provide information that is not relevant to the
reader. Please don't end up like this:

Rules always have exceptions.
You can  assign ID references to elements in my XML documents.
These ID references can be used to access XML element in much the
same way as the NAME or ID attributes in HTML.

A couple of suggestions you might use a product like XMLSPY to do the 
conversion or
 a sister product like MAPForce from Altova, this would be a $5-700 
investment.
I am not sure if it would do all you want.
Good product if working daily with XML.

http://www.altova.com

It would be hard to read this via xmldom, maybe there is code written 
available
via a user group, it would amout to a couple of thousand lines. They may 
have a
an xslt to do the conversion.

You could also easily read via dotNet XMLTextReader class
Maybe write a VB.Net program to do something like this
You would read xml and use a select case to parse your way through.
The XMLTextReader doesn't verify against the DTD however.


<?xml version="1.0"?>
<MMTP>
    <LookUp>
    <ControlType>C1ComboBoxList</ControlType>
    <DBField>LastMedDate</DBField>
    <BookMark></BookMark>
    <ColumnIndex></ColumnIndex>
    <ControlName>cmbGoal5</ControlName>
    <Values VALUE="0" BookMark="Goal5Progress"/>
    <Values VALUE="1" BookMark="Goal5NoProgress"/>
    <Values VALUE="2" BookMark="Goal5NA"/>
</LookUp>
</MMTP>


Private Shared Function FormatXml(ByVal reader As XmlTextReader, ByVal
filename As String) As clsReportElements
Dim ReportElement As clsReportElement
Dim al As new clsReportElements  'ArrayList(117)
Dim lValue as string
Dim lBookmark as String
Try
   ReportElement = New clsReportElement
   While reader.Read()
      Select Case (reader.NodeType)
         Case XmlNodeType.Element
        '''***Other Elements removed for easy reading*****
            If (reader.Name = "Values") Then
               dim isValueAdded as Boolean  = False
               if reader.HasAttributes then
           Do while reader.MoveToNextAttribute ()
                 if len(Reader.Value.Trim) > 0 then
                if Reader.Name = "VALUE" then
                   lValue = Reader.Value
                End If                      
                 end if          
                 if Reader.Name = "BookMark" then
                if len(Reader.Value.Trim) > 0 then
                   lBookmark = Reader.Value.ToString
                   isValueAdded = True  
                end if
                 End If                                
        Loop                            
     End If                        
      if isValueAdded = True then
         ReportElement.AddValue (lValue, lBookmark)
      end if
   End If                      
         case XmlNodeType.EndElement
        If (reader.Name = "LookUp") then
             al.Add(ReportElement)
              ReportElement = New clsReportElement
        End If                
         case xmlnodetype.Attribute
        If (reader.Name = "Values") Then
           ReportElement.AddValue (0, reader.ReadElementString())
        End If                      
         End Select
End While
Return al
Catch ex As Exception
      Console.WriteLine("Operation Failed.")
      Console.WriteLine("Exception: {0}", ex.ToString())
Finally
      'Finished with XmlTextReader
      If Not reader Is Nothing Then
        reader.Close()
      End If
End Try
End Function


Greg Smith wrote:

> (This is a second resend - my first one was html to illustrate the color
>layout for ease of reading the XML, but it turns out to be too large, so
>here it is in text only format)
>
>Hi everyone!  It's been a while, or seems like it anyway!  Hope everyone is
>well.
>
>Below is a valid XML file, according to the shown DTD, except for the part
>inside the <Docmuent> elements, which is normally image in b64 code, but
>unnecessary for this example.
>
>I know that Access does not like attribute-centric XML and will not import
>it (correctly).  I know that it DOES like element-centric XML and WILL
>import that.  I think (which can be dangerous) that what I have below is
>attribute-centric, or some version of it, because, looking at examples from
>Microsoft of element-centric XML, this does not look like it.  However,
>looking at Microsoft's attribute-centric XML, it doesn't look like that
>either.  Closer, but not like this.  
>
>What I really need to do is import the data from this file into Access (I'm
>using 2003).  Now, I could just read the file as a text file, and strip out
>what I want, but that's not terribly efficient and might prove difficult to
>do when there are duplicated element names or attribute fields.  
>
>Ideally, I would like to be able to just directly import the xml data, using
>a schema or some other document that can define this file, or convert it to
>element-centric XML, for Access to accept.
>
>Does anyone know how to do this or done this before or have some pointers I
>could use to get me started in the right direction?  The creators of this
>beast have NO experience with Access or Access databases so are of no help
>in this matter.  My tax dollars at work.  L
>
>Thanks in advance!!!
>
>Greg
>
>Valid XML File for the stated dtd-(watch for wrapping and alignment issues
>with this type of file and text):
>
><!DOCTYPE REQUEST_GROUP SYSTEM 
>
>"http://iowalandrecords.org/portal/dtd/CLRIS_PRIA_Request.dtd">
><REQUEST_GROUP>
>  <REQUESTING_PARTY/>
>   <RECEIVING_PARTY/>
>    <SUBMITTING_PARTY/>
>     <REQUEST>
>                <KEY _Name="SubmissionID" _Value="4281"/>
>                <KEY _Name="GroupName" _Value="JFB_Exemption20060831"/>
>                <KEY _Name="SubmissionNumber" _Value="000000061000004281"/>
> 
><PRIA_REQUEST_Type="Other"_TypeOtherDescription="ESubmission">
>      	<PACKAGE>
>            <PRIA_DOCUMENT _Code="" _NonRecordableIndicator="N" _Type="Deed"
>_InstrumentDate="20060831" _CountyOfRecordationName="Scott"
>_StateOfRecordationName="IA">
>            <GRANTOR _FirstName="First" _MiddleName="mi" _LastName="Last"
>_NameSuffix="" _UnparsedName="Last First mi" _Capacity=""
>_NonPersonEntityIndicator="N"/>
>            <GRANTEE _Capacity="" _NonPersonEntityIndicator="N"/>
>            <PARTIES>
>            <_RETURN_TO_PARTY>
>            <NON_PERSON_ENTITY_DETAIL/>
>            <CONTACT_DETAIL/>
>            </_RETURN_TO_PARTY>
>            </PARTIES>
>            <EXECUTION/>
>            <RECORDING_ENDORSEMENT>
>            <_VOLUME_PAGE/>
>            <_FEES _TotalAmount="31.00">
>            <_FEE _Amount="15.00" _Description="StandardFee"/>
>            <_FEE _Amount="3.00" _Description="DocMgmtFee"/>
>            <_FEE _Amount="3.00" _Description="ERecordingFee"/>
>            <_FEE _Amount="10.00" _Description="TransferFee"/>
>            <_FEE _Amount="0.00" _Description="TransferTax"/>
>            </_FEES>
>            <_EXEMPTIONS _Description="Deed fulfilling contract"/>
>            </RECORDING_ENDORSEMENT>
>            <EMBEDDED_FILE _Description="" _EncodingType="B64Encode"
>_ID="4421" _Name="Cert.pdf" _NumberOfPages="1">
>            <DOCUMENT>-BLAH  - IMAGE FILE - BLAH BLAH ==</DOCUMENT>
>           </EMBEDDED_FILE>
>          </PRIA_DOCUMENT>
>         </PACKAGE>
>        </PRIA_REQUEST>
>       </REQUEST>
></REQUEST_GROUP>
>
>
>
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada




More information about the AccessD mailing list