reformating XML , need to add nodes depending on one attribute id.

hello, i need to generate an xml file using data from a mysql database.

Aftger concatenating the tables + tags inside the select query i managed to create this kind of format:

<BEGIN>
<client  id="A1" title="A1 random status" some_attribute="2011-05-03 05:20:04" no="32"/>
<client  id="A1" title="A1 random x status" some_attribute="2011-05-03 18:00:03" no="32"/>
<client  id="A1" title="A1 r2 status" some_attribute="2011-05-03 05:30:01" no="32"/>
<client  id="A1" title="A1 random status" some_attribute="2011-05-03 19:00:02" no="32"/>
<client  id="B1" title="B1 000 status" some_attribute="2011-05-03 01:20:02" no="32"/>
<client  id="B1" title="B1 bla bla status" some_attribute="2011-05-03 13:20:03" no="32"/>
<client  id="C2" title="C2 xx status" some_attribute="2011-05-03 05:20:06" no="32"/>
<client  id="C2" title="C2 xoxoxo status" some_attribute="2011-05-0305:20:06" no="32"/>
</BEGIN>

Open in new window


What i need is a script or a way using mysql or some linux tool that can be automated (.pl .sh .whatever) to insert nodes depending on the id value found in the xml output i posted.

final output should be like this:
<BEGIN>
<A1>
<client  id="A1" title="A1 random status" some_attribute="2011-05-03 05:20:04" no="32"/>
<client  id="A1" title="A1 random x status" some_attribute="2011-05-03 18:00:03" no="32"/>
<client  id="A1" title="A1 r2 status" some_attribute="2011-05-03 05:30:01" no="32"/>
<client  id="A1" title="A1 random status" some_attribute="2011-05-03 19:00:02" no="32"/>\
</A1>
<B1>
<client  id="B1" title="B1 000 status" some_attribute="2011-05-03 01:20:02" no="32"/>
<client  id="B1" title="B1 bla bla status" some_attribute="2011-05-03 13:20:03" no="32"/>
</B1>
<C1>
<client  id="C2" title="C2 xx status" some_attribute="2011-05-03 05:20:06" no="32"/>
<client  id="C2" title="C2 xoxoxo status" some_attribute="2011-05-0305:20:06" no="32"/>
</C1>
</BEGIN>

Open in new window


Thank you experts!
LVL 3
CarlsbergFTWAsked:
Who is Participating?
 
jackiechen858Connect With a Mentor Commented:
save this as a parse.awk file:

BEGIN{
        TAG="";
}
{
        if ( $1 ~/\<client/ ) {
                split($2,a,"\"");
                NEW_TAG=a[2];
                if ( TAG != NEW_TAG) {
                        if ( TAG != "" )
                                print "</"TAG">";
                        TAG=NEW_TAG;
                        print "<"TAG">";
                }
        }
        if ( $1 == "</BEGIN>" )
        {
                if ( TAG != "" )
                        print "</"TAG">";

        }
        print $0;
}
END{
}



then run it as "awk -f parse.awk source.xml"

0
 
CarlsbergFTWAuthor Commented:
any input would be greatly appreciated.

Thank you!
0
 
jackiechen858Commented:
This should be doable by awk.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
jackiechen858Commented:
the result is like:
<BEGIN>
<A1>
<client  id="A1" title="A1 random status" some_attribute="2011-05-03 05:20:04" no="32"/>
<client  id="A1" title="A1 random x status" some_attribute="2011-05-03 18:00:03" no="32"/>
<client  id="A1" title="A1 r2 status" some_attribute="2011-05-03 05:30:01" no="32"/>
<client  id="A1" title="A1 random status" some_attribute="2011-05-03 19:00:02" no="32"/>
</A1>
<B1>
<client  id="B1" title="B1 000 status" some_attribute="2011-05-03 01:20:02" no="32"/>
<client  id="B1" title="B1 bla bla status" some_attribute="2011-05-03 13:20:03" no="32"/>
</B1>
<C2>
<client  id="C2" title="C2 xx status" some_attribute="2011-05-03 05:20:06" no="32"/>
<client  id="C2" title="C2 xoxoxo status" some_attribute="2011-05-0305:20:06" no="32"/>
</C2>
</BEGIN>
0
 
CarlsbergFTWAuthor Commented:
Thank you for this imput. i will let you know about the result!
0
 
CarlsbergFTWAuthor Commented:
Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.