Algorithm - create xml from structure

Question

I have two tables in my database - Category and Food. Food has idCategory. Category has id and idParentCategory.

From them, I need to create a string which will look like this (or xml):

<?xml version="1.0">
<tree id="0">
    <item text="Category1">
        <item text="Food1"/>
        <item text="Food2"/>
    </item>
    <item text="Category2">
        <item text="Category1">
            <item text="Food3"/>
            <item text="Food4"/>
        </item>
        <item text="Category3">
            <item text="Food5"/>
            <item text="Food6"/>
        </item>
    </item>
</tree>

How to create it? I tried finding the category which has id == 1, and then search all its children, but it's very complex. Is there an easy way?

One category can have many categories, but their children can have only food.

Have you tried writing a sql statement joining the data and then just dumping all the results into your string? — jzworkman, Mar 15 '12 at 16:50
You want something to "look like this (not xml, but string)", but what you show is XML. why not show what you want? Where is your third table? what is the relationship between tables? — Stealth Rabbi, Mar 15 '12 at 16:51
@jzworkman I write Linq which joins the tables but what good does it do? — petko_stankoski, Mar 15 '12 at 16:51
Your question mentions three tables but you define only two, which do you mean? — Peter Monks, Mar 15 '12 at 16:51
@PeterMonks I later simplified it, but the word 'three' remained. I edited the question — petko_stankoski, Mar 15 '12 at 16:52
@StealthRabbi I later simplified it, but the word 'three' remained. I edited the question — petko_stankoski, Mar 15 '12 at 16:52
If you can represent what you want in Objects, it's fairly easy to serialize to XML. — Bob2Chiv, Mar 15 '12 at 16:57
@Bob2Chiv No to serialization. I have to do this with simple string/ — petko_stankoski, Mar 15 '12 at 16:59

score 1 · Accepted Answer · edited May 23 '17 at 12:04

1

You can serialize it from an object that has the structure you want; but you'll first have to create your object.

See: Deserializing XML to Objects in C# if you don't know the object structure.

Once you have the object it's easy to serialize into an XML string:

        XmlSerializer serializer = new XmlSerializer(typeof(THEOBJECT));
        string THEOBJECTXMLSTRING;

        using (var stream = new MemoryStream())
        {
            serializer.Serialize(stream, THEINSTANCEOFTHEOBJECT);
            stream.Seek(0, SeekOrigin.Begin);
            var sr = new StreamReader(stream);
            THEOBJECTXMLSTRING = sr.ReadToEnd();
        }

        return THEOBJECTXMLSTRING;

edited May 23 '17 at 12:04

Community

1
1

answered Mar 15 '12 at 17:13

Bob2Chiv

1,858
2
19
29

I know how to serialize an xml file. I don't know how to serialize THE xml file above. – petko_stankoski Mar 15 '12 at 17:24
So the problem is creating the object and hierarchy from the two tables, not creating the XML? – Bob2Chiv Mar 15 '12 at 17:29
The problem is creating Xml like in the question. – petko_stankoski Mar 15 '12 at 17:32
I fear I do not understand. I have shown you how to create an XML string from an object that has the structure that you want (THEOBJECT). In order to use the method shown above you must 1. Create an object (THEOBJECT) that matches your hierarchy, 2. Populate an instance of the object (THEINSTANCEOFTHEOBJECT) with the data from the tables. – Bob2Chiv Mar 15 '12 at 17:38

score 0 · Answer 2 · answered Mar 15 '12 at 16:50

0

You'd be better off generating an XML document and trying to procure the byte information in order to transform it into a string. Doing otherwise is not only time-consuming, but it's very error-prone.

answered Mar 15 '12 at 16:50

Neil

5,762
24
36

Ok, how to produce xml file from this? – petko_stankoski Mar 15 '12 at 16:52

Algorithm - create xml from structure

2 Answers2