How to pass data from database into xml?

Question

1.00/5 (1 vote)

See more:

I am trying to pass data from the database to pass into XML node. The data I am calling, has two columns (title, body) and will always have more than 2 or more rows. The method i have created below, is outputting a null result, on the client-side.

Would I require a for loop before the XML code? Please advice further, if possible.

public static string createXML()
       {

           string xml = "";
           string title = "";
           string content = "";

           SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["########"].ConnectionString);

           string commandtext = "######################";
           SqlCommand command = new SqlCommand(commandtext, con);
           con.Open();
           command.Parameters.Add(new SqlParameter("title", title));
           command.Parameters.Add(new SqlParameter("Body", content));

           SqlDataReader reader = command.ExecuteReader();

           while (reader.Read())
           {
               string top = reader.GetString(0);
               string body = reader.GetString(1);

               var result = top + "<br />" + body + "<br />";

               XElement xeRoot = new XElement("Start");
               XElement xeSendTo = new XElement("properties");
               XElement xeSendTo1 = new XElement("property");
               xeSendTo1.Add(new XAttribute("name", "Main Content"));
               xeSendTo1.Add(new XAttribute("type", "TEXT"));
               xeSendTo1.SetElementValue("value", result);//top & body parameters inside value.
               xeSendTo.Add(xeSendTo1);
               xeRoot.Add(xeSendTo);
               XDocument xDoc = new XDocument(xeRoot);
               xDoc.ToString();
           }

           return xml;
       }

Any hints to where I may be going wrong, would be very much appreciated. Thank you for your time and help.
My desired output, as the following below:

XML

<Start>
<properties>
<property name="Main" type="TEXT">
<value>
Title
content

title
content

</value>
</property>
</properties>
</Start>

Posted 8-Oct-14 4:47am

miss786

Add a Solution

Comments

george4986 8-Oct-14 23:50pm

if u have more than one rows then it will be a table having two columns and multiple rows right? why dont u convert the datatable to xml?

3 solutions

Solution 2

string connetionString = null;
SqlConnection connection );
SqlDataAdapter adapter ;
DataSet ds = new DataSet();
string sql = null;

connetionString = "Data Source=servername;Initial Catalog=databsename;User ID=username;Password=password";
connection = new SqlConnection(connetionString);
sql = "select * from Product";

connection.Open();
adapter = new SqlDataAdapter(sql, connection);
adapter.Fill(ds);
connection.Close();
ds.WriteXml("Product.xml");
MessageBox.Show("Done");

Posted 8-Oct-14 19:10pm

Ainy Mughal

Solution 3

Thank you all, for your help and reply. I used Member 10454138's suggestion to create working solution, as shown below:

C#

SqlDataReader reader = command.ExecuteReader();

            XElement xeRoot = new XElement("start");         
            XElement xeSendTo = new XElement("properties");
            XElement xeSendTo1 = new XElement("property");
            xeSendTo1.Add(new XAttribute("name", "Main Content"));
            xeSendTo1.Add(new XAttribute("type", "TEXT"));
            xeSendTo.Add(xeSendTo1);
            xeRoot.Add(xeSendTo);
            XDocument xDoc = new XDocument(xeRoot);

            while (reader.Read())
            {
                string top = reader.GetString(0);
                string body = reader.GetString(1);

                var result = top + "<br />" + body + "<br />";

                string data = result.ToString();          
                xeSendTo1.Add(new XElement("value", (new XCData(data))));     
            }                      
            return xml = xDoc.ToString();

Thank you all, for your kind input into this thread. I appreciate your time and help. =D

Posted 9-Oct-14 1:22am

miss786

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

jaket-cp · Accepted Answer · 2014-10-08T05:48:00

Looking at your function createXML, the return value xml is not being populated.
The createXML is returning a string so you do not need to use XDocument.

The while loop needs modifying to get the result only, you could do something like this:

C#

string top = "";
string body = "";
string result = "";
while (reader.Read()){
    top = reader.GetString(0);
    body = reader.GetString(1);
    //check to make sure CarriageReturn LineFeed is working, should not use <br/> tag for this
    result = result + top + "\r\n" + body + "\r\n";
}
//create the xml string
xml = "<Start><properties><property name='Main' type='TEXT'><value>" + result + "</value></property></properties></Start>"

//To cater for tags and xml entities within the result it can be wrapped with cdata (characters the xml parser should not parse) like so
result = "<![CDATA[" + result + "]]>";

//The xml string can be parsed using the XDocument
XDocument xDoc = new XDocument();
xDoc = XDocument.Parse(xml);
//This will ensure the xml is well formed - not sure if this is a good way of doing it, or if using the creating XElement etc method below is better.

If you want to use the XDocument then use the same while loop above to get result and modify how the xml string is generated:

C#

//Move the xml stuff outside of the loop
XElement xeRoot = new XElement("Start");
XElement xeSendTo = new XElement("properties");
XElement xeSendTo1 = new XElement("property");
xeSendTo1.Add(new XAttribute("name", "Main Content"));
xeSendTo1.Add(new XAttribute("type", "TEXT"));
xeSendTo1.SetElementValue("value", result);//top & body parameters inside value.
xeSendTo.Add(xeSendTo1);
xeRoot.Add(xeSendTo);
XDocument xDoc = new XDocument(xeRoot);
//remember to give xml a value
xml = xDoc.ToString();

You should also consider changing the xml structure to add extra elements for title and content, something like this:

XML

<Start>
    <properties>
        <property name="Main" type="TEXT">
            <value>
                <title>Title</title>
                <content>content</content>
            </value>
            <value>
                <title>Title</title>
                <content>content</content>
            </value>
        </property>
    </properties>
</Start>

Hope that helps out.
I have not tested and assume your general code to get the data is working.