Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Parsing XML Document 1

Status
Not open for further replies.
AHJ1

AHJ1

Programmer
Oct 30, 2007
69
US
Hello,

I am using Access 2002 and I have about 5,000 forms that were created using Adobe Life Cycle. When someone fills out the form it creates an XML file like this:
Code: 
  <?xml version="1.0" encoding="UTF-8" ?> 
- <topmostSubform>
  <Name_of_Petitioner>Alan</Name_of_Petitioner> 
  <Contact_Person>Joan Miro</Contact_Person> 
  <dba_Business>Dewesse Holdings, Inc.</dba_Business> 
  <Mailing_Address>1234 Street Road</Mailing_Address> 
  <Contact_Mailing_Address /> 
  <City>New Philly</City> 
  <State>NV</State> 
  <Zip_Code>89521</Zip_Code> 
  <Contact_City /> 
  <Contact_State /> 
  <Contact_Zip_Code /> 
  <Telephone_Number>775.555.1234</Telephone_Number> 
  <Contact_Telephone_Number /> 
  <E-mail_address>alan@dewesse.com</E-mail_address> 
  <Contact_E-mail_address /> 
  <Parcel_Number>1234-234</Parcel_Number> 
  <Physical_Address_of_Property>1234 Street Street, Streetland, NV 89478</Physical_Address_of_Property> 
  <Tax_Year>1980</Tax_Year> 
  <County_Date_Heard>12/21/06</County_Date_Heard> 
  <County_Appeal>Nye</County_Appeal> 
  <County_Case_No>12-1234</County_Case_No> 
  <Appeal_Not_Heard_1>Just testing</Appeal_Not_Heard_1> 
  <Appeal_Not_Heard_2 /> 
  <Tax_Val_Land>12342</Tax_Val_Land> 
  <Assd_Val_Land>13242</Assd_Val_Land> 
  <Cnty_Tax_Val_Land>12342</Cnty_Tax_Val_Land> 
  <Cnty_Assd_val_Land>12342</Cnty_Assd_val_Land> 
  <Tax_Val_Land_Req>1234</Tax_Val_Land_Req> 
  <Ass_val_Land_Req>1234</Ass_val_Land_Req> 
  <Tax_Val_Buildings>13434224</Tax_Val_Buildings> 
  <Assd_Val_Buildings>14323424</Assd_Val_Buildings> 
  <Cnty_Tax_Val_Buildings>14234</Cnty_Tax_Val_Buildings> 
  <Cnty_Assd_val_Buildings>134234</Cnty_Assd_val_Buildings> 
  <Tax_Val_Buildings_Req>14234234</Tax_Val_Buildings_Req> 
  <Cnty_Assd_val_Buildings_Req>142</Cnty_Assd_val_Buildings_Req> 
  <Tax_Val_Personal>142</Tax_Val_Personal> 
  <Cnty_Assd_Val_Personal>243</Cnty_Assd_Val_Personal> 
  <Cnty_Tax_Val_Personal>1341</Cnty_Tax_Val_Personal> 
  <Cnty_Assd_val_Personal>13421</Cnty_Assd_val_Personal> 
  <Tax_Val_Personal_Req>111</Tax_Val_Personal_Req> 
  <Assed_val_Personal_Req>1134</Assed_val_Personal_Req> 
  <Tax_Val_Total>13424</Tax_Val_Total> 
  <Assd_Val_Total>1342</Assd_Val_Total> 
  <Cnty_Tax_Val_Total>12</Cnty_Tax_Val_Total> 
  <Cnty_Assd_val_Total>123</Cnty_Assd_val_Total> 
  <Tax_Val_Total_Req>134234</Tax_Val_Total_Req> 
  <Assd_val_Total_Req>1234234</Assd_val_Total_Req> 
  <Other_Reason>afdsdfsdf</Other_Reason> 
  <Chk_Sec>1</Chk_Sec> 
  <Chk_Unsec>0</Chk_Unsec> 
  <Chk_Suppl>0</Chk_Suppl> 
  <Chk_Indus>0</Chk_Indus> 
  <Chk_Mobile>0</Chk_Mobile> 
  <Chk_Personal>0</Chk_Personal> 
  <Chk_Vacant>0</Chk_Vacant> 
  <Chk_Res>0</Chk_Res> 
  <Chk_Multi_Fam>0</Chk_Multi_Fam> 
  <Chk_Comml>0</Chk_Comml> 
  <Chk_Mining>1</Chk_Mining> 
  <Chk_Agri>0</Chk_Agri> 
  <Chk_a>0</Chk_a> 
  <Chk_b>0</Chk_b> 
  <Chk_c>0</Chk_c> 
  <Chk_d>0</Chk_d> 
  <Chk_e>1</Chk_e> 
  <Chk_f>1</Chk_f> 
  <Chk_Wrong_Form>1</Chk_Wrong_Form> 
  <Chk_Cnty_Board_Yes>0</Chk_Cnty_Board_Yes> 
  <Chk_Cnty_Board_No>0</Chk_Cnty_Board_No> 
  <Exemption_Description>adfdf</Exemption_Description> 
  <Authorization_County /> 
  <Agent_APN>afsdf</Agent_APN> 
  <Auth_Year>afsf</Auth_Year> 
  <Authorized_Agent>afsdfsf</Authorized_Agent> 
  <Contact_Person_Agent>afsfsf</Contact_Person_Agent> 
  <Mailing_Address_Agent>aff</Mailing_Address_Agent> 
  <Mailing_Address_Agent_Contact>afsfs</Mailing_Address_Agent_Contact> 
  <Agent_City>afsfsf</Agent_City> 
  <Agent_State>afdf</Agent_State> 
  <Agent_Zip_Code>afsf</Agent_Zip_Code> 
  <Agent_contact_City>afsfsfd</Agent_contact_City> 
  <Agent_Contact_State>asdfsdf</Agent_Contact_State> 
  <Agent_Contact_Zip_Code>afsf</Agent_Contact_Zip_Code> 
  <Agent_Telephone_Number>999-999-9999</Agent_Telephone_Number> 
  <Agent_Contact_Telephone_Number>888-555-1234</Agent_Contact_Telephone_Number> 
  <Agent_E-mail_address>me@junk.com</Agent_E-mail_address> 
  <Agent_Contact_E-mail_address>me@junk1.com</Agent_Contact_E-mail_address> 
  <Petitioner_Name>Bill Smith</Petitioner_Name> 
  <Petitioner_Title>President</Petitioner_Title> 
  <Chk_All_Nevada>1</Chk_All_Nevada> 
  <Chk_County>1</Chk_County> 
  <Chk_Property>1</Chk_Property> 
  </topmostSubform>

If there is data in a particular field, I need to read that data, and display it in a form for editing. Once edited, the user will push a button and run an append query.

I started out by using this code:
Code: 
Sub ParseTest()
Dim strTextLine
Open "C:\Data\ImportSBE\HDTestAlan_data.xml" For Input As #1    ' Open file.
Do While Not EOF(1)    ' Loop until end of file.
    Line Input #1, strTextLine    ' Read line into variable.
    Debug.Print strTextLine    ' Print to the Immediate window.
    Debug.Print "- - - - - - - - - -"
Loop

Close #1    ' Close file.

End Sub

I expected to see a result one line at a time, like this:
Code: 
  <Name_of_Petitioner>Alan</Name_of_Petitioner>

The result is the entire file read as one string, as follows:
Code: 
[Code]
<?xml version="1.0" encoding="UTF-8"?>
<topmostSubform
><Name_of_Petitioner
>Alan</Name_of_Petitioner
><Contact_Person
>Joan Miro</Contact_Person
><dba_Business
>Dewesse Holdings, Inc.</dba_Business
><Mailing_Address
>1234 Street Road</Mailing_Address
><Contact_Mailing_Address
/><City
>New Philly</City
><State
>NV </State
><Zip_Code
>89521</Zip_Code
><Contact_City
/><Contact_State
/><Contact_Zip_Code
/><Telephone_Number
>775.555.1234</Telephone_Number
><Contact_Telephone_Number
/><E-mail_address
>alan@dewesse.com</E-mail_address
><Contact_E-mail_address
/><Parcel_Number
>1234-234</Parcel_Number
><Physical_Address_of_Property
>1234 Street Street, Streetland, NV  89478</Physical_Address_of_Property
><Tax_Year
>1980</Tax_Year
><County_Date_Heard
>12/21/06</County_Date_Heard
><County_Appeal
>Nye</County_Appeal
><County_Case_No
>12-1234</County_Case_No
><Appeal_Not_Heard_1
>Just testing</Appeal_Not_Heard_1
><Appeal_Not_Heard_2
/><Tax_Val_Land
>12342</Tax_Val_Land
><Assd_Val_Land
>13242</Assd_Val_Land
><Cnty_Tax_Val_Land
>12342</Cnty_Tax_Val_Land
><Cnty_Assd_val_Land
>12342</Cnty_Assd_val_Land
><Tax_Val_Land_Req
>1234</Tax_Val_Land_Req
><Ass_val_Land_Req
>1234</Ass_val_Land_Req
><Tax_Val_Buildings
>13434224</Tax_Val_Buildings
><Assd_Val_Buildings
>14323424</Assd_Val_Buildings
><Cnty_Tax_Val_Buildings
>14234</Cnty_Tax_Val_Buildings
><Cnty_Assd_val_Buildings
>134234</Cnty_Assd_val_Buildings
><Tax_Val_Buildings_Req
>14234234</Tax_Val_Buildings_Req
><Cnty_Assd_val_Buildings_Req
>142</Cnty_Assd_val_Buildings_Req
><Tax_Val_Personal
>142</Tax_Val_Personal
><Cnty_Assd_Val_Personal
>243</Cnty_Assd_Val_Personal
><Cnty_Tax_Val_Personal
>1341</Cnty_Tax_Val_Personal
><Cnty_Assd_val_Personal
>13421</Cnty_Assd_val_Personal
><Tax_Val_Personal_Req
>111</Tax_Val_Personal_Req
><Assed_val_Personal_Req
>1134</Assed_val_Personal_Req
><Tax_Val_Total
>13424</Tax_Val_Total
><Assd_Val_Total
>1342</Assd_Val_Total
><Cnty_Tax_Val_Total
>12</Cnty_Tax_Val_Total
><Cnty_Assd_val_Total
>123</Cnty_Assd_val_Total
><Tax_Val_Total_Req
>134234</Tax_Val_Total_Req
><Assd_val_Total_Req
>1234234</Assd_val_Total_Req
><Other_Reason
>afdsdfsdf</Other_Reason
><Chk_Sec
>1</Chk_Sec
><Chk_Unsec
>0</Chk_Unsec
><Chk_Suppl
>0</Chk_Suppl
><Chk_Indus
>0</Chk_Indus
><Chk_Mobile
>0</Chk_Mobile
><Chk_Personal
>0</Chk_Personal
><Chk_Vacant
>0</Chk_Vacant
><Chk_Res
>0</Chk_Res
><Chk_Multi_Fam
>0</Chk_Multi_Fam
><Chk_Comml
>0</Chk_Comml
><Chk_Mining
>1</Chk_Mining
><Chk_Agri
>0</Chk_Agri
><Chk_a
>0</Chk_a
><Chk_b
>0</Chk_b
><Chk_c
>0</Chk_c
><Chk_d
>0</Chk_d
><Chk_e
>1</Chk_e
><Chk_f
>1</Chk_f
><Chk_Wrong_Form
>1</Chk_Wrong_Form
><Chk_Cnty_Board_Yes
>0</Chk_Cnty_Board_Yes
><Chk_Cnty_Board_No
>0</Chk_Cnty_Board_No
><Exemption_Description
>adfdf</Exemption_Description
><Authorization_County
/><Agent_APN
>afsdf </Agent_APN
><Auth_Year
>afsf </Auth_Year
><Authorized_Agent
>afsdfsf</Authorized_Agent
><Contact_Person_Agent
>afsfsf</Contact_Person_Agent
><Mailing_Address_Agent
>aff</Mailing_Address_Agent
><Mailing_Address_Agent_Contact
>afsfs</Mailing_Address_Agent_Contact
><Agent_City
>afsfsf</Agent_City
><Agent_State
>afdf</Agent_State
><Agent_Zip_Code
>afsf</Agent_Zip_Code
><Agent_contact_City
>afsfsfd</Agent_contact_City
><Agent_Contact_State
>asdfsdf</Agent_Contact_State
><Agent_Contact_Zip_Code
>afsf</Agent_Contact_Zip_Code
><Agent_Telephone_Number
>999-999-9999</Agent_Telephone_Number
><Agent_Contact_Telephone_Number
>888-555-1234</Agent_Contact_Telephone_Number
><Agent_E-mail_address
>me@junk.com</Agent_E-mail_address
><Agent_Contact_E-mail_address
>me@junk1.com</Agent_Contact_E-mail_address
><Petitioner_Name
>Bill Smith</Petitioner_Name
><Petitioner_Title
>President</Petitioner_Title
><Chk_All_Nevada
>1</Chk_All_Nevada
><Chk_County
>1</Chk_County
><Chk_Property
>1</Chk_Property
></topmostSubform
>
- - - - - - - - - -

Can anyone suggest a way to parse the XML one tag at a time?
 
Sort by date Sort by votes
I knew there had to be a simple solution. Here it is:
Code: 
Sub TestXMLImport()
Application.ImportXML _
    DataSource:="C:\Data\ImportSBE\HDTestAlan_data.xml", _
    ImportOptions:=acStructureAndData

End Sub

This creates a table called "topmostSubform". I'm not sure why it does this.

Now, it seems to me that I should be able to place all of the files in a directory and loop through them. I could create an interface that reads each table, or possibly I could append each of these separate tables to a single table.

Suggestions on the best way to proceed will be appreciated.

Thanks,
Alan
 
Upvote 0 Downvote
Or you could keep it all in code and use the DOM handling code:

Code: 
Sub ParseXML()
Const ELEMENT_NAME As String = "Mailing_Address_Agent_Contact"
'Add reference to one of the Microsoft XML libraries
Dim doc As New DOMDocument

'Load the xml
If doc.Load("C:\Data\ImportSBE\HDTestAlan_data.xml") Then
    Dim ndlist As IXMLDOMNodeList
    'return a list of nodes matching your requirements
    Set ndlist = doc.documentElement.getElementsByTagName(ELEMENT_NAME)
    
    Dim el As IXMLDOMElement
    'It's still a node list, even if there's only one attribute, so you'll need to iterate through
    'them. You can check if there are multiple tags and raise an error if they are found
    
    If ndlist.length = 1 Then
    
    For Each el In ndlist
        'do something with the value of the element
        Debug.Print el.nodeTypedValue
    Next el
    
    Else
        MsgBox "Malformed XML document. Was expecting a single elemenent of '" & ELEMENT_NAME & "' but found " & ndlist.length
    End If
Else
    MsgBox ("Unable to open XML File.")
End If
End Sub

This way you're not importing the xml files, just reading the data you want out of them.

hth

Ben

----------------------------------------------
Ben O'Hara
David W. Fenton said:
We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.
Click to expand...
 
Upvote 0 Downvote
Ben,

Thanks for the alternative. I tried the code and it works.

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

hermanlaksko
  • Locked
  • Question
Ribbon on the fly (XML) 2
Replies
13
Views
344
hermanlaksko
hermanlaksko
MrMode
  • Locked
  • Question
vba xmlhttprequest API oauth configuration 1
Replies
1
Views
113
oharab
oharab
TrekBiker
  • Locked
  • Question
XML data imports into Excel but not into Access
Replies
1
Views
37
sxschech
sxschech
PMTom
  • Locked
  • Question
Header issue using VBA to insert Word documents into a Word Template
Replies
1
Views
52
Andrzejek
Andrzejek
chris_who
  • Locked
  • Question
Centrally documenting excel connection strings from multiple workbooks
Replies
2
Views
47
chris_who
chris_who

Part and Inventory Search

Sponsor

Back
Top