• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • Last Modified:

How to parse the node names of an xml document and insert them into database?

hi,
I have an xml document as shown below and I need to parse this xml and load the names of nodes into access database.
In my database I have a table with two columns say "FieldType" and "Number".
Once the xml is loaded into database, the "FieldType" and "Number" need to have the values as follows. can somebody please give me some idea how to do this? Thanks for any help.

FieldType     Number
-----------     ----------
Borrower     D1
Borrower     D2
Borrower     D3
Cosigner      D1
Cosigner      D2
Cosigner      D3
Guarantor    D1
Guarantor    D2
Guarantor    D3
<XmlPoint Function="CreateAccount">

<Borrower>
  <D1 FieldName="Name">John</D1>
  <D2 FieldName="Phone">742-33-4372</D2>
  <D3 FieldName="DOB">01/01/1973</D3>
</Borrower>

<Cosigner>
  <D1 FieldName="Name">Paul</D1>
  <D2 FieldName="Phone">742-33-4372</D2>
  <D3 FieldName="DOB">01/01/1973</D3>
</Cosigner>

<Guarantor>
  <D1 FieldName="Name">Kim</D1>
  <D2 FieldName="Phone">742-33-4372</D2>
  <D3 FieldName="DOB">01/01/1973</D3>
</Guarantor>

</XmlPoint>

Open in new window

0
ipjyo
Asked:
ipjyo
  • 3
  • 3
2 Solutions
 
gemailjCommented:
this how to read a xml document
static void Main(string[] args)
        {
            XmlTextReader reader = new XmlTextReader(@"C:\books.xml");

            while (reader.Read())
            {
                string s = reader.Name;     // get the name of the current node
            }
        }
    }

Open in new window

0
 
gemailjCommented:
see this example also show you how to read xml tree of nodes in a complex way

http://msdn.microsoft.com/en-us/library/443c16cf(VS.71).aspx
0
 
ipjyoAuthor Commented:
hi,

Thanks for the response.
Do I need to do insert for each record?

Thanks.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
gemailjCommented:
yes of course, this is just how to read the nodes from a XML file
0
 
ipjyoAuthor Commented:
After I parse the xmldocument when I try to insert each value into database as shown below, it is inserting the same value in the second column "Number" as many number of times as the for loop runs.
But when I try to print  the value using a MessageBox before executing the query it is printing the proper value that is expected.

Can you please let me know why the insert query is failing.
Thanks.
string connectionString = null;
OleDbConnection connection = null;
string sql = null;

connectionString = "Data Source=TestDB.mdb;Password=;Provider=\"Microsoft.Jet.OLEDB.4.0\";User ID=Admin;";
connection = new OleDbConnection(connectionString);
sql = "Insert into ExportFieldsFromXmlDoc (FieldType, Number) Values (@FT, @Num)";


xmlDoc.Load(@"C:\Fields.xml");
XmlNode Borrower = xmlDoc.SelectSingleNode("XmlPoint/Borrower");

connection.Open();
OleDbCommand insertCommand = new OleDbCommand(sql, connection);


foreach (XmlNode node in Borrower.ChildNodes)
                {
                    //MessageBox.Show("Number" + node.Name);
                    insertCommand.Parameters.Add("@FT", OleDbType.Char).Value = "Borrower";
                    insertCommand.Parameters.Add("@Num", OleDbType.Char).Value = node.Name;

                    insertCommand.ExecuteNonQuery();
                }

Open in new window

0
 
ipjyoAuthor Commented:
Now it is working fine.
I have to move this statement inside foreach loop.

OleDbCommand insertCommand = new OleDbCommand(sql, connection);

Thanks.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now