• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

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!
0
CarlsbergFTW
Asked:
CarlsbergFTW
  • 3
  • 3
1 Solution
 
CarlsbergFTWAuthor Commented:
any input would be greatly appreciated.

Thank you!
0
 
jackiechen858Commented:
This should be doable by awk.
0
 
jackiechen858Commented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now