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:
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:
I expected to see a result one line at a time, like this:
The result is the entire file read as one string, as follows:
Can anyone suggest a way to parse the XML one tag at a time?
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?