?
Solved

How to read XML file and store data in SQL server database ??

Posted on 2005-04-07
19
Medium Priority
?
216 Views
Last Modified: 2010-04-07
Hi everybody,

Iam an intermediate programmer into ASP.Net and VB.Net.   I have a XML file and i want to read the data in that XML file and put it into tables in SQL Server.  Please help me out with code snippets...  
Let me know what method is best to carry out this task ??

Here is my XML file:
-----------------------------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8" ?>
<Members>
      <USERNAME>user</USERNAME>
      <PASSWORD>pass</PASSWORD>
      <Member_Info>
            <Member>
                  <TRANSACTION_ID>108837</TRANSACTION_ID>
                  <CREATION_DATE>07/01/2004</CREATION_DATE>
                  <PRODUCT>OOL</PRODUCT>
                  <CAMPAIGN>NRTB</CAMPAIGN>
                  <DETAILS>
                  </DETAILS>
                  <OPT_IN>yes</OPT_IN>
                  <PROFILE>
                        <EMAIL>test@test.com</EMAIL>
                        <ACCOUNT_NUMBER></ACCOUNT_NUMBER>
                        <FIRST_NAME></FIRST_NAME>
                        <LAST_NAME></LAST_NAME>
                        <ADDRESS1></ADDRESS1>
                        <ADDRESS2></ADDRESS2>
                        <CITY></CITY>
                        <STATE></STATE>
                        <ZIP></ZIP>
                        <PHONE_NUMBER></PHONE_NUMBER>
                  </PROFILE>
            </Member>
            <Member>
                  <TRANSACTION_ID>109941</TRANSACTION_ID>
                  <CREATION_DATE>07/02/2004</CREATION_DATE>
                  <PRODUCT>IO</PRODUCT>
                  <CAMPAIGN>NEWS</CAMPAIGN>
                  <DETAILS>
                        <DETAIL>TV</DETAIL>
                        <DETAIL>VIDEO</DETAIL>
                  </DETAILS>
                  <OPT_IN></OPT_IN>
                  <PROFILE>
                        <EMAIL>test@test.com</EMAIL>
                        <ACCOUNT_NUMBER>0780051578945</ACCOUNT_NUMBER>
                        <FIRST_NAME>Fname</FIRST_NAME>
                        <LAST_NAME>Lname</LAST_NAME>
                        <ADDRESS1>420 Crossways Park Drive</ADDRESS1>
                        <ADDRESS2></ADDRESS2>
                        <CITY>City</CITY>
                        <STATE>NY</STATE>
                        <ZIP>11797</ZIP>
                        <PHONE_NUMBER>1234567890</PHONE_NUMBER>
                  </PROFILE>
            </Member>
      </Member_Info>
</Members>
-----------------------------------------------------------------------------------------------------
0
Comment
Question by:dn_learner
  • 10
  • 7
  • 2
19 Comments
 
LVL 11

Expert Comment

by:sachiek
ID: 13734373
You want to put data as xml or extract each element and put them into different fields of a tables?


Sachi
0
 
LVL 11

Expert Comment

by:sachiek
ID: 13734545
I assueme you need to put each element into different field.
Check out below sample codes.

From XML to Database using the XmlTextReader
http://www.xmlforasp.net/codeSection.aspx?csID=11


Mapping XML to Relational Databases
http://www.xmlforasp.net/codeSection.aspx?csID=100


Hope it helps you to get you a solution.

Sachi
0
 

Author Comment

by:dn_learner
ID: 13734863
Yes, i want to extract the data and put them in different fields of different tables.

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13742086
The most efficient way is to pass the whole Xml Document to a SQL Server Stored Procedure and then use the T-SQL OPENXML function to get the values into either local variables or tables and save to your table(s).

What values are you trying to save and where?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13742092
P.S. If you do not have any intermediate logic, you can also bypass saving to local variables and save directly to the tables using OPENXML.
0
 

Author Comment

by:dn_learner
ID: 13771965
Hi acperkins,

Could u please help me with some URL's   or  code snippets  ??
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13772300
Post the values from the Xml document you want to save (indicate whether it is an Insert or an Update), as well tables and their structures and the matching columns names.
0
 

Author Comment

by:dn_learner
ID: 13772877
Hi acperkins,

I really appreciate your quick response.

I have posted the XML document in my original post at the top.   I want to save the following values from the nodes in the XML document.

username
password
transaction_id
product
campaign
detail
first_name
last_name
address1
address2
city
state
zip
phone_number


My table name in SQL Server 2000 is also called Members;  and it has the same column names as the XML document nodes.

Please help.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13774810
The reason I need the structure is so that I can match the data types.  The easiest way to do this, is from Enterprise Manager copy the Members table (Ctrl+C or right mouse click and Copy) and paste here.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13774829
Also, indicate whether you are adding or updating.  If you are updating than I will need the primary key (username ?)
0
 

Author Comment

by:dn_learner
ID: 13775024
Hi perkins,

Here are the datatypes for all the columns. The primary key is record_id.  I just want to INSERT the data into the table in SQL Server.

I really appreciate your help.

Thank you.

-----------------------------------------

username  nvarchar(30)
password  nvarchar(30)
transaction_id  integer
product nvarchar(10)
campaign nvarchar(10)
detail nvarchar(30)
first_name nvarchar(30)
last_name nvarchar(30)
address1 nvarchar(30)
address2 nvarchar(30)
city nvarchar(30)
state nvarchar(2)
zip nvarchar(5)
phone_number nvarchar(13)
record_id integer (primary key)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13776543
Declare @iDoc integer

EXEC sp_xml_preparedocument @iDoc OUTPUT,
'<?xml version="1.0" encoding="UTF-8" ?>
<Members>
     <USERNAME>user</USERNAME>
     <PASSWORD>pass</PASSWORD>
     <Member_Info>
          <Member>
               <TRANSACTION_ID>108837</TRANSACTION_ID>
               <CREATION_DATE>07/01/2004</CREATION_DATE>
               <PRODUCT>OOL</PRODUCT>
               <CAMPAIGN>NRTB</CAMPAIGN>
               <DETAILS>
               </DETAILS>
               <OPT_IN>yes</OPT_IN>
               <PROFILE>
                    <EMAIL>test@test.com</EMAIL>
                    <ACCOUNT_NUMBER></ACCOUNT_NUMBER>
                    <FIRST_NAME></FIRST_NAME>
                    <LAST_NAME></LAST_NAME>
                    <ADDRESS1></ADDRESS1>
                    <ADDRESS2></ADDRESS2>
                    <CITY></CITY>
                    <STATE></STATE>
                    <ZIP></ZIP>
                    <PHONE_NUMBER></PHONE_NUMBER>
               </PROFILE>
          </Member>
          <Member>
               <TRANSACTION_ID>109941</TRANSACTION_ID>
               <CREATION_DATE>07/02/2004</CREATION_DATE>
               <PRODUCT>IO</PRODUCT>
               <CAMPAIGN>NEWS</CAMPAIGN>
               <DETAILS>
                    <DETAIL>TV</DETAIL>
                    <DETAIL>VIDEO</DETAIL>
               </DETAILS>
               <OPT_IN></OPT_IN>
               <PROFILE>
                    <EMAIL>test@test.com</EMAIL>
                    <ACCOUNT_NUMBER>0780051578945</ACCOUNT_NUMBER>
                    <FIRST_NAME>Fname</FIRST_NAME>
                    <LAST_NAME>Lname</LAST_NAME>
                    <ADDRESS1>420 Crossways Park Drive</ADDRESS1>
                    <ADDRESS2></ADDRESS2>
                    <CITY>City</CITY>
                    <STATE>NY</STATE>
                    <ZIP>11797</ZIP>
                    <PHONE_NUMBER>1234567890</PHONE_NUMBER>
               </PROFILE>
          </Member>
     </Member_Info>
</Members>'

Insert      members (username, [password], transaction_id, product, campaign, detail, first_name,
            last_name, address1, address2, city, state, zip, phone_number)
Select      username,
      [password],
      transaction_id,
      product,
      campaign,
      detail,
      first_name,
      last_name,
      address1,
      address2,
      city,
      state,
      zip,
      phone_number
From      OpenXml(@idoc, 'Members/Member_Info/Member/PROFILE', 2) With (
      username nvarchar(30) '../../../USERNAME',
      [password] nvarchar(30) '../../../PASSWORD',
      transaction_id integer '../TRANSACTION_ID',
      product nvarchar(10) '../PRODUCT',
      campaign nvarchar(10) '../CAMPAIGN',
      detail nvarchar(30) '../DETAILS',            -- this is a problem, not sure how you want to solve this
      first_name nvarchar(30) 'FIRST_NAME',
      last_name nvarchar(30) 'LAST_NAME',
      address1 nvarchar(30) 'ADDRESS1',
      address2 nvarchar(30) 'ADDRESS2',
      city nvarchar(30) 'CITY',
      state nvarchar(2) 'STATE',
      zip nvarchar(5) 'ZIP',
      phone_number nvarchar(30) 'PHONE_NUMBER')

EXEC sp_xml_removedocument @idoc
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13776561
You can test with the above code.  Just comment out the Insert lines, as in:
--Insert     members (username, [password], transaction_id, product, campaign, detail, first_name,
--          last_name, address1, address2, city, state, zip, phone_number)

When you are sure it is what you want than you can wrap it in a Stored Procedure.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 13776583
Here is the stored Procedure:

Create Procedure usp_AddMembers
                             @MemberXmlDoc text

Declare @iDoc integer

EXEC sp_xml_preparedocument @iDoc OUTPUT, @MemberXmlDoc

Insert     members (username, [password], transaction_id, product, campaign, detail, first_name,
          last_name, address1, address2, city, state, zip, phone_number)
Select     username,
     [password],
     transaction_id,
     product,
     campaign,
     detail,
     first_name,
     last_name,
     address1,
     address2,
     city,
     state,
     zip,
     phone_number
From     OpenXml(@idoc, 'Members/Member_Info/Member/PROFILE', 2) With (
     username nvarchar(30) '../../../USERNAME',
     [password] nvarchar(30) '../../../PASSWORD',
     transaction_id integer '../TRANSACTION_ID',
     product nvarchar(10) '../PRODUCT',
     campaign nvarchar(10) '../CAMPAIGN',
     detail nvarchar(30) '../DETAILS',            -- this is a problem, not sure how you want to solve this
     first_name nvarchar(30) 'FIRST_NAME',
     last_name nvarchar(30) 'LAST_NAME',
     address1 nvarchar(30) 'ADDRESS1',
     address2 nvarchar(30) 'ADDRESS2',
     city nvarchar(30) 'CITY',
     state nvarchar(2) 'STATE',
     zip nvarchar(5) 'ZIP',
     phone_number nvarchar(30) 'PHONE_NUMBER')

EXEC sp_xml_removedocument @idoc

You would call the above stored procedure as follows:


Exec usp_AddMembers
'<?xml version="1.0" encoding="UTF-8" ?>
<Members>
     <USERNAME>user</USERNAME>
     <PASSWORD>pass</PASSWORD>
     <Member_Info>
          <Member>
               <TRANSACTION_ID>108837</TRANSACTION_ID>
               <CREATION_DATE>07/01/2004</CREATION_DATE>
               <PRODUCT>OOL</PRODUCT>
               <CAMPAIGN>NRTB</CAMPAIGN>
               <DETAILS>
               </DETAILS>
               <OPT_IN>yes</OPT_IN>
               <PROFILE>
                    <EMAIL>test@test.com</EMAIL>
                    <ACCOUNT_NUMBER></ACCOUNT_NUMBER>
                    <FIRST_NAME></FIRST_NAME>
                    <LAST_NAME></LAST_NAME>
                    <ADDRESS1></ADDRESS1>
                    <ADDRESS2></ADDRESS2>
                    <CITY></CITY>
                    <STATE></STATE>
                    <ZIP></ZIP>
                    <PHONE_NUMBER></PHONE_NUMBER>
               </PROFILE>
          </Member>
          <Member>
               <TRANSACTION_ID>109941</TRANSACTION_ID>
               <CREATION_DATE>07/02/2004</CREATION_DATE>
               <PRODUCT>IO</PRODUCT>
               <CAMPAIGN>NEWS</CAMPAIGN>
               <DETAILS>
                    <DETAIL>TV</DETAIL>
                    <DETAIL>VIDEO</DETAIL>
               </DETAILS>
               <OPT_IN></OPT_IN>
               <PROFILE>
                    <EMAIL>test@test.com</EMAIL>
                    <ACCOUNT_NUMBER>0780051578945</ACCOUNT_NUMBER>
                    <FIRST_NAME>Fname</FIRST_NAME>
                    <LAST_NAME>Lname</LAST_NAME>
                    <ADDRESS1>420 Crossways Park Drive</ADDRESS1>
                    <ADDRESS2></ADDRESS2>
                    <CITY>City</CITY>
                    <STATE>NY</STATE>
                    <ZIP>11797</ZIP>
                    <PHONE_NUMBER>1234567890</PHONE_NUMBER>
               </PROFILE>
          </Member>
     </Member_Info>
</Members>'
0
 

Author Comment

by:dn_learner
ID: 13776805
Hi

Why do you have to have the complete XML document in single quotes ??

I dont understand it.
0
 

Author Comment

by:dn_learner
ID: 13776888
Is there anyway you can pasre this by using the XMLDocument object?  I heard this way u can traverse the entire tree and visit each and every node.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13776898
>>Why do you have to have the complete XML document in single quotes ??<<
That is the way you declare character strings in T-SQL.

>>I dont understand it.<<
If you cannot execute basic SQL script that I have fully tested in SQL Query Analyzer and know works without any errors, than I cannot help you any further.

As an alternative, consider doing this all in .NET as someone else suggested.

Good luck.
0
 

Author Comment

by:dn_learner
ID: 13777544
Hi perkins,

Yes,  This is a web appl that iam trying to build using ASP.Net and VB.Net.  The interface would allow user to browse for a xml file using a file-input control. Once he clicks the submit button,  there should be some code that should do the processing.  

You have been very helpful with regards to doing this in t-sql.

I was wondering if you could post some code in VB.Net/ASP.Net that would parse the xml file.

thank you.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13795631
>>I was wondering if you could post some code in VB.Net/ASP.Net that would parse the xml file.<<
Why would you want to do that?  That implies you will have to send each individual node list to the database.  Why not send the whole Xml document as I suggested and store all the elements at one time?  I have given you the stored procedure, all you need to code is the .NET portion to pass the Xml document.

If you still insist on doing it at the front-end, than I suggest you take a look at the links posted earlier by sachiek.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
In .NET 2.0, Microsoft introduced the Web Site.  This was the default way to create a web Project in Visual Studio 2005.  In Visual Studio 2008, the Web Application has been restored as the default web Project in Visual Studio/.NET 3.x The Web Si…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Screencast - Getting to Know the Pipeline
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question