I was asked to show a small app that can handle an XML file and insert its content into SQL then can read the SQL data and show it.

Help please…………How to insert this xml in sql server.Also i want to know how to get each value from xml.

<?xml version=’1.0′ encoding=’ISO-8859-1′ ?><REPLY><PARAMETER>OK</PARAMETER><LOGIN>SUCCESSFULL</LOGIN><PUSHAPI>ACTIVE</PUSHAPI><STAKEHOLDERID>OK</STAKEHOLDERID><PERMITTED>OK</PERMITTED><SMSINFO><MSISDN>1234567896</MSISDN><SMSTEXT>test</SMSTEXT><CSMSID>45646513213164561</CSMSID><REFERENCEID>4564644654</REFERENCEID></SMSINFO></REPLY>

I have created a small console application that creates this XML file from custom object, read the saved XML file into custom object, write the content to SQL, read the SQL into list of custom object and write all to console.

First create a new database. Let’s name it XmlSqlReadWrite and create 2 tables: Reply and SmsInfo.

“Reply” table CREATE query:

CREATE TABLE [dbo].[Reply](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Parameter] [nvarchar](50) NOT NULL,
	[Login] [nvarchar](50) NOT NULL,
	[PushApi] [nvarchar](50) NOT NULL,
	[StakeholderId] [nvarchar](50) NOT NULL,
	[Permitted] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Reply] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

“SmsInfo” table CREATE query (with foreign key to Reply table Id column):

CREATE TABLE [dbo].[SmsInfo](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[MsIsdn] [bigint] NOT NULL,
	[SmsText] [nvarchar](255) NOT NULL,
	[CsMsId] [bigint] NOT NULL,
	[ReferenceId] [bigint] NOT NULL,
	[ReplyId] [int] NOT NULL,
 CONSTRAINT [PK_SmsInfo] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SmsInfo]  WITH CHECK ADD  CONSTRAINT [FK_SmsInfo_Reply] FOREIGN KEY([ReplyId])
REFERENCES [dbo].[Reply] ([Id])
GO

ALTER TABLE [dbo].[SmsInfo] CHECK CONSTRAINT [FK_SmsInfo_Reply]
GO

Now we have 2 tables with only mandatory fields (NOT NULL) and with one dependency: no value can exist in SmsInfo table without being inserted into Reply table first.

In the console application create 2 classes for Reply and SmsInfo (this time we skip implementing the dependency, because we don’t need it in this example). We also override to ToString() method in Reply class so we get what we want to see when writing all the content to console.

public class Reply
{
    public int Id { get; set; }
    public string Parameter { get; set; }
    public string Login { get; set; }
    public string PushApi { get; set; }
    public string StakeholderId { get; set; }
    public string Permitted { get; set; }
    public SmsInfo SmsInfo { get; set; }

    public Reply(int id, string parameter, string login, string pushApi, string stakeholderId, string permitted, SmsInfo smsInfo)
    {
        Id = id;
        Parameter = parameter;
        Login = login;
        PushApi = pushApi;
        StakeholderId = stakeholderId;
        Permitted = permitted;
        SmsInfo = smsInfo;
    }

    //for XML load
    public Reply(XElement reply)
    {
        Parameter = reply.Element("PARAMETER")?.Value;
        Login = reply.Element("LOGIN")?.Value;
        PushApi = reply.Element("PUSHAPI")?.Value;
        StakeholderId = reply.Element("STAKEHOLDERID")?.Value;
        Permitted = reply.Element("PERMITTED")?.Value;

        SmsInfo = new SmsInfo(
            long.Parse(reply.Element("SMSINFO").Element("MSISDN")?.Value),
            reply.Element("SMSINFO").Element("SMSTEXT")?.Value,
            long.Parse(reply.Element("SMSINFO").Element("CSMSID")?.Value),
            long.Parse(reply.Element("SMSINFO").Element("REFERENCEID")?.Value)
        );
    }

    //for XML Save
    public XElement SaveToXml()
    {
        return new XElement("REPLY",
            new XElement("PARAMETER", Parameter),
            new XElement("LOGIN", Login),
            new XElement("PUSHAPI", PushApi),
            new XElement("STAKEHOLDERID", StakeholderId),
            new XElement("PERMITTED", Permitted),
            new XElement("SMSINFO",
                new XElement("MSISDN", SmsInfo.MsIsdn),
                new XElement("SMSTEXT", SmsInfo.SmsText),
                new XElement("CSMSID", SmsInfo.CsMsId),
                new XElement("REFERENCEID", SmsInfo.ReferenceId)
            )
        );
    }

    public override string ToString()
    {
        return "Parameter:\t" + Parameter + Environment.NewLine + "Login:\t" + Login + Environment.NewLine +
               "PushApi\t" + PushApi + Environment.NewLine + "StakeholderId:\t" + StakeholderId +
               Environment.NewLine + "Permitted:\t" + Permitted + Environment.NewLine + "MSISDN:\t" +
               SmsInfo.MsIsdn + Environment.NewLine + "SmsText:\t" + SmsInfo.SmsText + Environment.NewLine +
               "CSMSID:\t" + SmsInfo.CsMsId + Environment.NewLine + "ReferenceId:\t" + SmsInfo.ReferenceId +
               Environment.NewLine + Environment.NewLine;
    }
}

As you may noticed, we have 3 constructors in this class: the first one will be used when reading from SQL and want to instantiate this class, the second one will be used when reading back the saved XML file and the last one is used to create XML from our class (custom object). The “one-to-many” SQL dependency can be seen here with the public property of SmsInfo class.

The SmsInfo class is very simple:

public class SmsInfo
{
    public long MsIsdn { get; set; }
    public string SmsText { get; set; }
    public long CsMsId { get; set; }
    public long ReferenceId { get; set; }

    public SmsInfo(long msIsdn, string smsText, long csMsId, long referenceId)
    {
        MsIsdn = msIsdn;
        SmsText = smsText;
        CsMsId = csMsId;
        ReferenceId = referenceId;
    }
}

In the Program.cs file create 2 class level fields:

private const string CONNECTION_STRING = "Data Source=localhost;Initial Catalog=XmlSqlReadWrite;Integrated Security=True";
private static readonly SqlConnection Connection = new SqlConnection(CONNECTION_STRING);

The connection string maybe different in your environment. My database is hosted on my computer (localhost) with using windows authentication (Integrated Security=True).

In the Main method let’s create our test instance of Reply class:

Reply test = new Reply(-1, "OK", "SUCCESSFULL", "ACTIVE", "OK", "OK", new SmsInfo(1234567896, "test", 45646513213164561, 4564644654));

We will have 4 additional methods for each functions: saving to XML file, read from XML file, write to SQL and read from SQL.

First create a new XML file from our test instance of Reply class:

private static void SaveTemp(Reply result)
{
    XElement xmlResult = result.SaveToXml();

    XDocument xml = new XDocument(xmlResult);

    xml.Save("testxml.xml");
}

Now let’s try to read back this file in our Main method this way: Reply read = LoadTemp();

private static Reply LoadTemp()
{
    XDocument xml = XDocument.Load("testxml.xml");

    return new Reply(xml.Root);
}

When we write this “read” instance of Reply class to SQL keep in mind that first you must INSERT into Reply table, because what Id you’ll get after INSERT will be used when INSERTing into SmsInfo table:

private static void WriteToSql(Reply reply)
{
    using (SqlCommand cmd = new SqlCommand())
    {
        if (Connection.State != ConnectionState.Open) Connection.Open();
        cmd.Connection = Connection;

        cmd.CommandText = "INSERT INTO Reply (Parameter,Login,PushApi,StakeholderId,Permitted) OUTPUT INSERTED.Id VALUES (@par,@log,@api,@stake,@perm);";
        cmd.Parameters.AddWithValue("@par", reply.Parameter);
        cmd.Parameters.AddWithValue("@log", reply.Login);
        cmd.Parameters.AddWithValue("@api", reply.PushApi);
        cmd.Parameters.AddWithValue("@stake", reply.StakeholderId);
        cmd.Parameters.AddWithValue("@perm", reply.Permitted);
        int id = (int)cmd.ExecuteScalar();

        cmd.CommandText = "INSERT INTO SmsInfo (MsIsdn,SmsText,CsMsId,ReferenceId,ReplyId) VALUES (@ms,@sms,@cs,@ref,@rep);";
        cmd.Parameters.AddWithValue("@ms", reply.SmsInfo.MsIsdn);
        cmd.Parameters.AddWithValue("@sms", reply.SmsInfo.SmsText);
        cmd.Parameters.AddWithValue("@cs", reply.SmsInfo.CsMsId);
        cmd.Parameters.AddWithValue("@ref", reply.SmsInfo.ReferenceId);
        cmd.Parameters.AddWithValue("@rep", id);

        cmd.ExecuteNonQuery();
        if (Connection.State == ConnectionState.Open) Connection.Close();
    }
}

Repeat these steps a few times more so you have some more records in SQL. Then we can read all the inserted data from SQL this way:

private static List ReadFromSql()
{
    List result = new List();
    using (SqlCommand cmd = new SqlCommand())
    {
        if (Connection.State != ConnectionState.Open) Connection.Open();
        cmd.Connection = Connection;
        cmd.CommandText =
            "SELECT Reply.*,SmsInfo.MsIsdn,SmsInfo.SmsText,SmsInfo.CsMsId,SmsInfo.ReferenceId FROM Reply LEFT JOIN SmsInfo ON Reply.Id=SmsInfo.ReplyId";
        using (SqlDataReader dr = cmd.ExecuteReader())
        {
            while (dr.Read())
            {
                result.Add(new Reply(
                    (int)dr["Id"],
                    dr["Parameter"].ToString(),
                    dr["Login"].ToString(),
                    dr["PushApi"].ToString(),
                    dr["StakeholderId"].ToString(),
                    dr["Permitted"].ToString(),
                    new SmsInfo(
                        (long)dr["MsIsdn"],
                        dr["SmsText"].ToString(),
                        (long)dr["CsMsId"],
                        (long)dr["ReferenceId"]
                    )));
            }
            dr.Close();
        }
        if (Connection.State == ConnectionState.Open) Connection.Close();
    }
    return result;
}

Now if you iterate through this List of Reply and add the iteration result to Console.WriteLine method, you’ll see all the field values because we’ve overridden the ToString() method:

List replies = ReadFromSql();

foreach (Reply reply in replies)
{
    Console.WriteLine(reply);
}

That’s all. Very simple.

If you have any question, don’t hesitate to write me.