locked
How do I extract data from XML file into SQL table?

    Question

  • How do I extract the data from an XML file and append it to my SQL table?  I am new to XML, SQL and Visual Studio, with only a MS Access background.  However the XML data is too much for Access to handle and I am having a very hard time finding a successful method to append the XML data to an SQL table.  Thank you!
    • Edited by MyMac Sunday, March 07, 2010 4:40 AM
    Sunday, February 21, 2010 5:01 AM

Answers

  • Look at this thread. You'll find the point to start from
    http://social.msdn.microsoft.com/Forums/en-US/Vsexpressvb/thread/0e1081bd-93eb-44fd-b17d-5d016cf9c732
    please, mark this as answer if it is THE answer
    ----------------
    Diego Cattaruzza
    Microsoft MVP - Visual Basic: Development
    blog: http://community.visual-basic.it/Diego
    web site: http://www.visual-basic.it
    Sunday, February 21, 2010 9:27 AM
  • How do I extract the data from an XML file and append it to my SQL table?  I am new to XML, SQL and Visual Studio, with only a MS Access background.  However the XML data is too much for Access to handle and I am having a very hard time finding a successful method to append the XML data to an SQL table.  Thank you!
    http://www.codeproject.com/KB/cpp/parsefilecode.aspx
    Just Be Humble Malange!
    Sunday, February 21, 2010 2:01 PM
  • Thank you All for your friendly help.

    Hi sparrowce,

    Welcome to MSDN forums!

    Besides, here are some tutorials which may be helpful to you.

    Thread: How to read/write XML file in VB.NET?
    http://social.msdn.microsoft.com/forums/en-US/vbgeneral/thread/02e3ffef-03b0-4979-bc10-1ac3d900f33d

    Thread: There are four methods to make one basic Data Access application(Previous, Next, First. Last, Update, Delete, Insert, Select, Save) for you to check and follow.
    http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/582c13b5-720f-4002-9ed4-da42fdf26b3c


    Best regards,
    Martin Xie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by MyMac Monday, March 08, 2010 5:31 AM
    Friday, February 26, 2010 7:33 AM
  • MyMac,

    I am both a Senior .Net Developer and have held several SQL DBA positions with the largest companies on the globe. I specialize in information processing through n-tier architecture and would be happy to assist you in your introduction to the SQL Server Platform.

    First, I am also a musician and was a guitar instructor for years so I can relate to the analogy. Equally, both .net and SQL Server will be a continuous learning experience. You'll never truly master them but refine your skillsets through time.

    My first suggestion to you would be to pick a book or google some articles on normalization. Understanding the relationship of tables is key to moving forward. From there, a good book on T-SQL is the next logical step.

    As far as connecting it to .net, ADO.net is the common interface/layer. There are many great references to ADO.net both in print and online.

    Is a MS Access environment replicable in .net? Most of my resume is filled with migration projects that involved exactly that. MS Access created the datalayers for you behind the scenes but provides no real optimization. .Net/SQL Server requires a little more work with a tremendous trade-off in performance. To more specifically put it, MS Access had 2 layers - A presentation layer and a business logic layer. You "can" create a datalayer but it's not required because it built it for you.

    Without rambling, to avoid Moderators moving your postings, post your T-SQL to the T-SQL forums, your ADO.net questions to the ADO.net forums, etc...

    Good luck,

    Adam
    Dibble and dabble but please don't babble.
    • Marked as answer by MyMac Monday, March 08, 2010 5:47 PM
    Monday, March 08, 2010 8:12 AM

All replies

  • Look at this thread. You'll find the point to start from
    http://social.msdn.microsoft.com/Forums/en-US/Vsexpressvb/thread/0e1081bd-93eb-44fd-b17d-5d016cf9c732
    please, mark this as answer if it is THE answer
    ----------------
    Diego Cattaruzza
    Microsoft MVP - Visual Basic: Development
    blog: http://community.visual-basic.it/Diego
    web site: http://www.visual-basic.it
    Sunday, February 21, 2010 9:27 AM
  • How do I extract the data from an XML file and append it to my SQL table?  I am new to XML, SQL and Visual Studio, with only a MS Access background.  However the XML data is too much for Access to handle and I am having a very hard time finding a successful method to append the XML data to an SQL table.  Thank you!
    http://www.codeproject.com/KB/cpp/parsefilecode.aspx
    Just Be Humble Malange!
    Sunday, February 21, 2010 2:01 PM
  • The article Malange has recommended is a good article. The thing to remember is that the task is a little awesome the first time. It's essentially reading the Xml file and writing the sql file over and over. Thats all there is too it. Have the XML reader have plenty of error messges with adequate information to recover or to know  too abort.

    Renee
    Sunday, February 21, 2010 4:21 PM
  • Thank you All for your friendly help.

    Hi sparrowce,

    Welcome to MSDN forums!

    Besides, here are some tutorials which may be helpful to you.

    Thread: How to read/write XML file in VB.NET?
    http://social.msdn.microsoft.com/forums/en-US/vbgeneral/thread/02e3ffef-03b0-4979-bc10-1ac3d900f33d

    Thread: There are four methods to make one basic Data Access application(Previous, Next, First. Last, Update, Delete, Insert, Select, Save) for you to check and follow.
    http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/582c13b5-720f-4002-9ed4-da42fdf26b3c


    Best regards,
    Martin Xie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by MyMac Monday, March 08, 2010 5:31 AM
    Friday, February 26, 2010 7:33 AM
  • First I would like to thank everyone for the replies and great links and I apologize - I must not have some settings right as I did not get any email notifications letting me know there were replies and am sorry for the delay in responding.

    I am checking the links and am finding I am very ignorant and suppose I need to start at the beginning in even getting terms right.  The XML files are existing - I won't be writing to them - only need to extract the data and get it into SQL table.  The links talk about parsing and reading - what is the right term I need to use?

    Also please bear with me as I am a total newbie at Visual Studio and have seen the CodeProject site but it is over my head.  I am comfortable with Access and understand the concepts of designing a form - putting code on click or other events -  code on form open/close - etc.  But in Visual Studio how to I transpose that knowledge or what do I need to know about where code goes in that program. 

    The links are helpful but I am such a newbie any step by step - start here - then next type of instructions or links to 'how to's' would be so appreciated.  So far all the links and instructions I've found over these past months have been on the level of assuming the reader knows where to start . . . :)

    I can open the XMLs in Excel and transfer them to Access and then append to SQL table using append query but some of the XMLs are too large for that process and it's all manual.  Also right now there is an XSLT file to go along with these XMLs but in a few weeks they will be releasing a version and no longer supplying the XSLT so solution must be one that doesn't need the XSLT.  

    Maybe Visual Studio is not the best answser - I sure don't know but am looking to streamline this very manual process and automate it if possible as there will be 50+ XMLs at a clip on a regular basis.

    Thank you so much!
    Sunday, March 07, 2010 1:56 AM
  • Coming from someone who has left friendly notes...

    "Also please bear with me as I am a total newbie at Visual Studio and have seen the CodeProject site but it is over my head."

    I dont think you're interested in VS or Windows internals.... The answer of course lies in hard study. You  need to know some basics of programming.

    Renee

    Sunday, March 07, 2010 1:30 PM
  • Coming from someone who has left friendly notes...

    "Also please bear with me as I am a total newbie at Visual Studio and have seen the CodeProject site but it is over my head."

    I dont think you're interested in VS or Windows internals.... The answer of course lies in hard study. You  need to know some basics of programming.

    Renee


    Talk about the pot calling the kettle black...
    Dibble and dabble but please don't babble.
    Sunday, March 07, 2010 4:49 PM
  • Adam,

    I've turned your post in for abuse. Every one knows you've been thrown off of the boards by Microsoft Management, yet you keep ignoring them as you always have.

    Renee
    Sunday, March 07, 2010 4:57 PM
  • Thank you for your help, Renee, consider me put in my place, and I certainly apologize if any part of my post has caused a problem here.  I also can understand it must seem like I am a real dweeb at this and it's certainly true, as this is my first foray into SQL, no bones about it, I'm a newbie at SQL but not at programming.   Possibly I'm not stating my area of pain rightly, which is where does the code go?  For example, in Access I can go into a form and write code to run on click or after update or other button or form open/close/load/exit events or I can place code in a module and call it from a form or report, but I would appreciate any guidance on where to start with SQL - where does the code go in Visual Studio or SQL Server Mgmt 2005 and how do you run it? That is the part about the CodeProject website that is over my head - I haven't worked with the tools before, so it's like a maze. In the environment I've been in the past 10+ years, IT has blocked those kinds of tools to us, and MS Access is what was 'approved' as a tool, so that is what we program with.  When you say "I dont think you're interested in VS or Windows internals.... " are you saying there is a better tool I should be using for this?  If so, could you please elaborate or provide more info/links?

    It is like the first time I played a guitar.  I stared at my fingers and then at the chord book and then back at the neck and changed my fingers to make a new chord, then strummed some more till it was time for a chord change, then stopped, looked at the book, looked at my fingers on the neck, changed them, then strummed some more.  Thought I would never ever get there, but one day I realized I was playing and no longer had to look at the book and my fingers, and if I can entreat you to possibly recall when you were first learning SQL what might seem 2nd nature to you now, and possibly came natural to you, but if you can at all recall the beginning, and give me a few start up pointers, it would be greatly appreciated.  Some have natural talent and ability and some of us less fortunate have to beat our heads a little longer before the light bulb comes on . . . .

    Your first reply said, "The thing to remember is that the task is a little awesome the first time. It's essentially reading the Xml file and writing the sql file over and over. Thats all there is too it. Have the XML reader have plenty of error messges with adequate information to recover or to know  too abort", and yes, I agree, it's very awesome and overwhelming when you are first starting.  I believe with the links everyone has provided, prayerfully I can slug my way through the code part once I get started . . . but there you go . . . what is the starting point I need to learn?

    Thank you.

    Sunday, March 07, 2010 11:58 PM
  • MyMac,

    Really I have other things to do besides creating a place to put new users.

    If you are interested in learning VB then learn it. But learn it on your own, until you have a specific question to ask. Then have no fear in asking it.

    I am not entertaining the idea that there is a better Basic or compiler because there isn't.

    I started coding while I was in grad school about 40 years ago. There were no computing books at the time and there wasn't a net or a Microsoft. So, I learned to rely on myself. I urge you to do the same - but learn.

    Renee
    Monday, March 08, 2010 1:50 AM
  • Adam,

    I've turned your post in for abuse. Every one knows you've been thrown off of the boards by Microsoft Management, yet you keep ignoring them as you always have.

    Renee

    Renee,

    You are the biggest clown in these forums. Go away.
    Dibble and dabble but please don't babble.
    Monday, March 08, 2010 3:44 AM
  • Adam,

    I've turned your post in for abuse. Every one knows you've been thrown off of the boards by Microsoft Management, yet you keep ignoring them as you always have.

    Renee

    Renee,

    Someone is going to figure you out sooner or later. You'd might as well leave on your own. It'll be easier on you.

    We, the real programmers on these forums, are insulted by your presence and disgusted by your pretensions. Your patronizing approach to valid questions has exhausted the patience of OP's for too long. You have cost the forum a reputation. Because of you personally, uncountable valid questions were left unanswered out of frustration of dealing with your ignorance, arrogance, and dysfunctions. These forums really need to give proficiency tests for MVP status so clowns like you will go away.

    Adam
    Dibble and dabble but please don't babble.
    Monday, March 08, 2010 4:35 AM
  • Hi Renee, I do not understand what part of my question was not specific or hard to understand.  What are you accusing me of?  If ignorance, I plead guilty.  If coming to a help forum to find help, I plead guilty.  If you are saying I do not want to learn or am slothful or unwilling, please just say what you mean point blank - why the personal digs?  Kind responders have provided links which I think I will be able to handle once I am able to get oriented to this new tool.

    What do you mean by "Really I have other things to do besides creating a place to put new users."   I am stunned, actually, and this is my first experience in a MSDN forum that I can recall, but have participated in many other forums, including internal corporate ones, and have replied and assisted where I could, and asked for help when needed, as my goal was to return the kindness shown to me.  In forums where I know the tool, such as Access or Word, I'm able to reply and help more than ask, though there was a time when I was first learning the shoe was on the other foot and my posts were more asking for help than giving.  Right now I am in the learning stage of this new tool (new to me), and the curve is the other direction.  Sorry for troubling your day busy with so many other things.

    Please disregard this post and I apologize for posting this question and will go and learn, thank you.






    Monday, March 08, 2010 5:29 AM
  • MyMac,

    Please do not judge this forum by Renee's abusive approach. There are many here that share the equality in helping others become better programmers as you've seen. Unfortunately, in a publich forum, you'll encounter twisted individuals such as Renee, but remember they are just individuals.

    Simply look at her past 10-30 posts. You'll quickly discover that she is ignored for good reasons. Hopefully she'll be gone soon.

    But do come back. Many of us here are truly experienced developers and will always provide good programming practices without the garbage you've seen.

    Adam
    Dibble and dabble but please don't babble.
    Monday, March 08, 2010 6:14 AM
  • Thank you Adam, please be assured I do not judge the forum, and am simply respectfully bowing out so as to not cause any more confrontation.  I do appreciate the links and other helpful info people have provided, and do realize my ignorance I'm sure is a pain in the hinderparts . . . it is to me, believe me!  Thanks again, and cheers.
    Monday, March 08, 2010 6:42 AM
  • MyMac,

    I am both a Senior .Net Developer and have held several SQL DBA positions with the largest companies on the globe. I specialize in information processing through n-tier architecture and would be happy to assist you in your introduction to the SQL Server Platform.

    First, I am also a musician and was a guitar instructor for years so I can relate to the analogy. Equally, both .net and SQL Server will be a continuous learning experience. You'll never truly master them but refine your skillsets through time.

    My first suggestion to you would be to pick a book or google some articles on normalization. Understanding the relationship of tables is key to moving forward. From there, a good book on T-SQL is the next logical step.

    As far as connecting it to .net, ADO.net is the common interface/layer. There are many great references to ADO.net both in print and online.

    Is a MS Access environment replicable in .net? Most of my resume is filled with migration projects that involved exactly that. MS Access created the datalayers for you behind the scenes but provides no real optimization. .Net/SQL Server requires a little more work with a tremendous trade-off in performance. To more specifically put it, MS Access had 2 layers - A presentation layer and a business logic layer. You "can" create a datalayer but it's not required because it built it for you.

    Without rambling, to avoid Moderators moving your postings, post your T-SQL to the T-SQL forums, your ADO.net questions to the ADO.net forums, etc...

    Good luck,

    Adam
    Dibble and dabble but please don't babble.
    • Marked as answer by MyMac Monday, March 08, 2010 5:47 PM
    Monday, March 08, 2010 8:12 AM
  • No, I won't go away. You have been thrown off these forra.

    Renee
    Monday, March 08, 2010 12:42 PM
  • Real pogrammers? I was programming before you were born.

    You achieve much mileage among new people but what can you expect from an applications programmer Adam. I finf very little.

    Renee
    Monday, March 08, 2010 12:47 PM
  • Yawn.
    Monday, March 08, 2010 12:51 PM
  • Real pogrammers? I was programming before you were born.

    You achieve much mileage among new people but what can you expect from an applications programmer Adam. I finf very little.

    Renee

    What is your goal here Renee? You're obviously not here to help and cannot offer any 'applications development' expertise although, strangely enough, this is an application development forum. So what is it?

    ...and so we're all aware, since you're not an application's developer and have no experience as such, how did you get your MVP status?

    ...and so you're aware. You 'were' a programmer 40 years ago. You're not grandfathered into .net because you wrote a few drivers in C 40 years ago. The idea is that you update your skill sets to keep up with the times. You should be asking questions to learn here not attempting to teach something you know nothing about.

    Adam
    Dibble and dabble but please don't babble.
    Monday, March 08, 2010 1:28 PM
  • Your approach is abusive Adam. I don't do anything but reply to your lies, the ones you tell about me, applications programmer.

    And you've been kicked off and are here in violation oh Micosoft managment.
    Renee
    Monday, March 08, 2010 4:10 PM
  • Thank you for the help, I will post in the right forums and appreciate the info.  Best wishes, thanks.

    Monday, March 08, 2010 5:49 PM
  • Good Luck MyMac!
    Renee
    Monday, March 08, 2010 6:08 PM
  • I don't answer the questions of trolls.

    C barely existed 40 years ago and I came from DEC an anti C environment. Drivers were written in asssembler for our machines.

    Renee
    Monday, March 08, 2010 7:45 PM
  • I don't answer the questions of trolls.

    C barely existed 40 years ago and I came from DEC an anti C environment. Drivers were written in asssembler for our machines.

    Renee

    Please troll the assembler fora.

    We'd really appreciate it.
    Dibble and dabble but please don't babble.
    • Proposed as answer by JohnGrove Monday, March 08, 2010 10:46 PM
    Monday, March 08, 2010 10:45 PM
  • You are the troll. You've been thrown off of this forum by Microsoft Management.
    Renee
    Monday, March 08, 2010 11:28 PM
  • Thanks Renee, I will delete that remark.
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Tuesday, March 09, 2010 2:10 AM
  • Any time John.
    Renee
    Tuesday, March 09, 2010 2:46 AM