You must Sign In to post a response.
  • Category: ASP.NET

    How to extract and save into database with two records ?

    this is consider as String

    How to add this two benefiary record into data base (means how to extract this two records)

    String= "<SessionID>B869E8B570</SessionID>
    <BeneficiaryCount>2</BeneficiaryCount>
    <ResponseCode>0</ResponseCode>
    <ResponseMessage>Query Beneficiary List Successful</ResponseMessage>

    <BeneficiaryDetails>

    <BeneficiaryDetail>
    <Name>Test1 </Name>
    <BankName>CBI</BankName>
    <BankBranch>ABC</BankBranch>
    <BankCity>THANE</BankCity>
    <BankIfscode>CBIN00001</BankIfscode>
    <BeneficiaryType>IMPS</BeneficiaryType>
    <BankAccountNumber>12654854</BankAccountNumber>
    <BeneficiaryCode>10000001</BeneficiaryCode>
    <Status>ACTIVE</Status>
    <OrgTransRefNum>15487598</OrgTransRefNum>
    <OrgAckNo>77889955</OrgAckNo>
    </BeneficiaryDetail>

    <BeneficiaryDetail>
    <Name>Test2</Name>
    <BankName>SBI</BankName>
    <BankBranch>XYZ</BankBranch>
    <BankCity>MUMBAI</BankCity>
    <BankIfscode>SBIN48758</BankIfscode>
    <BeneficiaryType>IMPS</BeneficiaryType>
    <BankAccountNumber>875487548754</BankAccountNumber>
    <BeneficiaryCode>1854875</BeneficiaryCode>
    <Status>ACTIVE</Status>
    <OrgTransRefNum>8855441166</OrgTransRefNum>
    <OrgAckNo>659845875</OrgAckNo>
    </BeneficiaryDetail>

    </BeneficiaryDetails>
    "
  • #766015
    Hi,
    Try this:
    I am considering string sResponse = your above whole string.
    System.IO.StringReader str = new System.IO.StringReader("<myroot>" + sResponse + "</myroot>");
    DataSet dt = new DataSet();
    dt.ReadXml(str);
    DataTable y1 = dt.Tables["BeneficiaryDetail"];
    foreach (DataRow r in y1.Rows)
    {
    string Name = r["Name"].ToString();
    string BankName = r["BankName"].ToString();
    string BankBranch = r["BankBranch"].ToString();
    string BankCity = r["BankCity"].ToString();
    \\Like wise you can take all columns from BeneficiaryDetail
    \\Insert all these values now in database
    }

  • #766018
    There are different ways to store xml in database, here are couple of ways
    - using dataset
    - using xml as data type
    It is the xml, so the good way is to create a dataset from these xml and then save that dataset to database
    with the help of 'ReadXml' method of dataset we can read xml in dataset
    see below snippet

    private void button1_Click(object sender, EventArgs e)
    {
    XmlReader xmlFile ;
    xmlFile = XmlReader.Create("Product.xml", new XmlReaderSettings());
    DataSet ds = new DataSet();
    ds.ReadXml(xmlFile);
    int i = 0;
    for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
    {
    MessageBox.Show(ds.Tables[0].Rows[i].ItemArray[2].ToString());
    }
    }

    OR
    SQL Server 2005 and up have a datatype called "XML" which you can store XML in - untyped or typed with a XSD schema.
    You can basically fill columns of type XML from an XML literal string, so you can easily just use a normal INSERT statement and fill the XML contents into that field.
    see below link to store xml in database using xml as data type
    https://www.simple-talk.com/sql/learn-sql-server/sql-server-xml-questions-you-were-too-shy-to-ask/

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #766022
    Hi,

    Try This...

    string connetionString = null;
    SqlConnection connection;
    SqlCommand command ;
    SqlDataAdapter adpter = new SqlDataAdapter();
    DataSet ds = new DataSet();
    XmlReader xmlFile ;
    string sql = null;

    int product_ID = 0;
    string Product_Name = null;
    double product_Price = 0;

    connetionString = "Data Source=servername;Initial Catalog=databsename;User ID=username;Password=password";

    connection = new SqlConnection(connetionString);

    xmlFile = XmlReader.Create("Product.xml", new XmlReaderSettings());
    ds.ReadXml(xmlFile);
    int i = 0;
    connection.Open();
    for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
    {
    product_ID = Convert.ToInt32(ds.Tables[0].Rows[i].ItemArray[0]);
    Product_Name = ds.Tables[0].Rows[i].ItemArray[1].ToString();
    product_Price = Convert.ToDouble(ds.Tables[0].Rows[i].ItemArray[2]);
    sql = "insert into Product values(" + product_ID + ",'" + Product_Name + "'," + product_Price + ")";
    command = new SqlCommand(sql, connection);
    adpter.InsertCommand = command;
    adpter.InsertCommand.ExecuteNonQuery();
    }
    connection.Close();
    MessageBox.Show("Done .. ");

  • #766044
    You can try the "Serialization" and "Deserialization". This is very simple and easy to handle it. You do not worry about parsing the complete XML. Serialization and Deserialization is nothing but converting the XML into Object and vise versa. You have to create the class file based on your XML files. Once you get the object it is easy to save in your data base

    Following is the sample code for main part. Just read the microsoft link and try to implement in your application.


    private void SerializeCollection(string filename){
    Employees Emps = new Employees();
    // Note that only the collection is serialized -- not the
    // CollectionName or any other public property of the class.
    Emps.CollectionName = "Employees";
    Employee John100 = new Employee("John", "100xxx");
    Emps.Add(John100);
    XmlSerializer x = new XmlSerializer(typeof(Employees));
    TextWriter writer = new StreamWriter(filename);
    x.Serialize(writer, Emps);
    }


    Reference link

    https://msdn.microsoft.com/en-us/library/58a18dwa(v=vs.110).aspx

    By Nathan
    Direction is important than speed


  • Sign In to post your comments