locked
Import XML Data in to Existing Database RRS feed

  • Question

  • User109856870 posted

    Hi,

    I have an existing XML document that I need to import in to a set of database (SQL 2000) tables. The destination tables map very closely to the XML document except a few key fields such as "Type" fields where there an integer foreign key is used rather than the actual value.

     The XML is received via a web service as a string and is validated against an XSD Schema. Can anyone point me in the right direction with regards to getting the XML data in to the database tables? All the examlpes I've found so far use relativley simple XML files such as:

    <items>
        <item>abc</item>
        <item>abc</item>
        <item>abc</item>
    </items>
     My source XML is not massively complicated but is a little more so than the examples. Elements with an * denote elements where the related database field is actually a foreign key link...
    <Applications>
      <Application>
        <Date></Date>
        <Type></Type> *
        <Reason></Reason> *
        <Supplier></Supplier> *
        <Applicants>
          <Applicant>
            <Title></Title> *
            <Forename></Forename>
            <SecondName></SecondName>
            <Surname></Surname>
            <Salutation></Salutation>
            <DateOfBirth></DateOfBirth>
            <Nationality></Nationality>
            <TelephoneNo></TelephoneNo>
            <MobileNo></MobileNo>
            <WorkNo></WorkNo>
            <FaxNo></FaxNo>
            <TimeToContact></TimeToContact> *
            <NumberToContact></NumberToContact>
            <EmailAddresses>
              <EmailAddress></EmailAddress>
              <Type></Type> *
            </EmailAddresses>
            <MaritalStatus></MaritalStatus> *
            <PropertyOwner></PropertyOwner>
            <TotalIncome></TotalIncome>
            <TotalDebt></TotalDebt>
            <Addresses>
              <Address>
                <PropertyNumber></PropertyNumber>
                <PropertyName></PropertyName>
                <Address1></Address1>
                <Address2></Address2>
                <PostalTown></PostalTown> *
                <County></County> *
                <PostCode></PostCode>
                <Correspondance></Correspondance>
                <YearsAtAddress></YearsAtAddress>
                <MonthsAtAddress></MonthsAtAddress>
                <MoveInDate></MoveInDate>
                <MoveOutDate></MoveOutDate>
                <PeopleAtProperty></PeopleAtProperty>
                <EmployedAtProperty></EmployedAtProperty>
                <PropertyValue></PropertyValue>
                <TenancyType></TenancyType> *
                <TenancyCost></TenancyCost>
                <PaymentDay></PaymentDay>
              </Address>
            </Addresses>
            <Employment>
              <Position>
                <Active></Active>
                <Type></Type> *
                <JobTitle></JobTitle>
                <Employer></Employer>
                <Salary></Salary>
                <StartDate></StartDate>
                <EndDate></EndDate>
              </Position>
            </Employment>
            <Creditors>
              <Creditor>
                <Name></Name>
                <CreditAccountNumber></CreditAccountNumber>
                <CreditorType></CreditorType> *
                <CreditAmount></CreditAmount>
                <CreditInterestRate></CreditInterestRate>
                <InterestFrozen></InterestFrozen>
                <CreditTermYears></CreditTermYears>
                <CreditTermMonths></CreditTermMonths>
                <PaymentFrequency></PaymentFrequency>
                <PaymentDay></PaymentDay>
                <PaymentAmmount></PaymentAmmount>
              </Creditor>
            </Creditors>
            <BankAccounts>
              <Bank>
                <Name></Name> *
                <AccountType></AccountType> *
                <AccountNumber></AccountNumber>
                <SortCode></SortCode>
                <TimeAtBankYears></TimeAtBankYears>
                <TimeAtBankMonths></TimeAtBankMonths>
              </Bank>
            </BankAccounts>
          </Applicant>
        </Applicants>
      </Application>
    </Applications>
    
    
     Thanks in advance to anyone able to lend a hand/point me in the right direction

     

    Danny

    Monday, June 4, 2007 8:44 AM

Answers

  • User-512755392 posted

    This would still leave me with CSV data to import in to the SQL tables. Is there not a way to do this directly with the XML??



    Yes.

    You can bulk import XML documents into a SQL Server database .

    To bulk import data from a data file into a SQL Server table or non-partitioned view, you can use the following:

    • bcp utility
      You can also use the bcp utility to export data from anywhere in a SQL Server database that a SELECT statement works, including partitioned views.
    • BULK INSERT
    • INSERT ... SELECT * FROM OPENROWSET(BULK...)

    Here you can find example:

    http://msdn.microsoft.com/en-us/library/ms191184.aspx#existing_row

    In this way you might have to change the format of xml document.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 5, 2007 10:18 PM
  • User109856870 posted

    Hi,

    Sorry for the late reply, been extremely busy.

    I have managed to solve this issue by writing a SQL stored procedure that accepts the XML string as a parameter and uses the OPENXML statement to post the data to the necessary tables.

     
    Cheers
    D
     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 14, 2007 6:29 AM

All replies

  • User-1977747231 posted

    hi,

     you can use xslt to convert xml to comma seperated values. Then use converted file to import data into sql tables.

    Thanks,

    Kiran

    Monday, June 4, 2007 9:14 AM
  • User109856870 posted

    you can use xslt to convert xml to comma seperated values. Then use converted file to import data into sql tables.
     

    This would still leave me with CSV data to import in to the SQL tables. Is there not a way to do this directly with the XML??

     

    Danny 

    Monday, June 4, 2007 10:02 AM
  • User-512755392 posted

    This would still leave me with CSV data to import in to the SQL tables. Is there not a way to do this directly with the XML??



    Yes.

    You can bulk import XML documents into a SQL Server database .

    To bulk import data from a data file into a SQL Server table or non-partitioned view, you can use the following:

    • bcp utility
      You can also use the bcp utility to export data from anywhere in a SQL Server database that a SELECT statement works, including partitioned views.
    • BULK INSERT
    • INSERT ... SELECT * FROM OPENROWSET(BULK...)

    Here you can find example:

    http://msdn.microsoft.com/en-us/library/ms191184.aspx#existing_row

    In this way you might have to change the format of xml document.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 5, 2007 10:18 PM
  • User1851205470 posted

    Fill this input xml to dataset. and then post the data from dataset to sql tables. Hope it could solve ur problem.

    Thursday, June 14, 2007 6:13 AM
  • User109856870 posted

    Hi,

    Sorry for the late reply, been extremely busy.

    I have managed to solve this issue by writing a SQL stored procedure that accepts the XML string as a parameter and uses the OPENXML statement to post the data to the necessary tables.

     
    Cheers
    D
     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 14, 2007 6:29 AM
  • User-635200366 posted

    Hi All,

    Getting or import data from xmlstring.

     

    If xmlstring save in database so get the that xmlstring with the help of datatable.

    First for all add that

    using System;

    using System.Collections;

    using System.ComponentModel;

    using System.Configuration;

    using System.Data;

    using System.Linq;

    using System.Web;

    using System.Web.Security;

    using System.Web.SessionState;

    using System.Web.UI;

    using System.Web.UI.HtmlControls;

    using System.Web.UI.WebControls;

    using System.Xml.Linq;

    using System.Data.OleDb;

    using System.Data.SqlClient;

    using System.Data.ProviderBase;

    using System.Web.Mail;

    using System.Xml;

    using System.Xml.Serialization;

    using System.Xml.Schema;

    using System.Text;

    using System.Text.RegularExpressions;

    using System.IO;

    using System.Windows.Forms;

    using System.Xml;

    using System.Xml.Xsl;

    using System.Xml.XPath;

    Create connection and as well as query for getting xmlstring from database.

    string connection = "Data source=SYED-PC;Initial Catalog=baass;Integrated Security=True;";

    SqlConnection connect = new SqlConnection(connection);

    string query4 = "select Details from Registrations where id = '" + id +""';

    in database my xml string is look like this if i view it from Query analyzer

    <?xml version="1.0" encoding="utf-16"?>
    <root><Address>aaaaaaaa</Address>
    <secadd2>bbbbbbbbbb</secadd2>
    <City>ddddddddd</City>
    <Province>pppppppppppp</Province>
    <Postalcode>1111111</Postalcode>
    <Fax>55555555555555</Fax>
    <Website>wwwwwwww</Website>
    <Attendeename>afadad</Attendeename>
    <SecA>Inventory Control
    </SecA>
    <SecAdate>April 2008</SecAdate>
    <SecB>Business Vision – Level 1
    </SecB>
    <SecBdate>May 2008</SecBdate>
    <Noofatt>1</Noofatt>
    <Subtot>12</Subtot>
    <Gst>11</Gst>
    <Total>23</Total>
    <PaymentType>Others</PaymentType>
    <Expirydate></Expirydate>
    <Ccardno></Ccardno>
    <Ccardname></Ccardname>
    <Hearabout>Phone Call
    </Hearabout>
    <Comments>yes</Comments>
    </root> 

     Know we create Data Adapter:

    SqlDataAdapter da4 = new SqlDataAdapter(query4, connect);

    connect.Open();

    Creating Data table:

    DataTable dt = new DataTable();

    Data adapter fill out data table:

    da4.Fill(dt);

    one string variable to getting particuler xmlstring because we get the xmlstring on any particular selection:

    string variable = dt.Rows[0][0].ToString();

    Creat XML Document

    XmlDocument xmldoc = new XmlDocument();

    xmldoc load the string variable and variable string hold the xmlstring from database. One thing important here dont use "xmldoc.Load" event because it use for file or URL.

    xmldoc.LoadXml(variable .ToString());

    know xmldoc load the xml of my variable,

    TextBox1.Text = xmldoc.DocumentElement.GetElementsByTagName("Address").Item(0).InnerXml.ToString();

    here i move particular element Or xml node move in to text box.

    GetElementsByTagNanme use for getting the node or Element of my xml string from xmldoc.

    InnerXml is use for the data between the <Address> </Address> node or elements.

    for every node we just create a one new xmldoc. we can use xmldoc multitime in a one code.

    thats it.

    hope fully its work.

     

    Monday, April 7, 2008 3:27 PM