How to WriteXML as hierarchical XML

  • Thread starter Thread starter ad
  • Start date Start date
A

ad

There are four areas in my company , every employees in my company have his
duty area.
I use a select join statement to make a view form Area and Employee table
like below.

I want to make a hierarchical XML form the view.
The XML is like below

How can I do?


the view:

Area Employee
----------------------------------------
North Mary
North John
North Max
East Kai
West Jina
West John
South Tim
South Peter

the hierarchical XML

<?xml version="1.0"?>
<AreaEmployee>
<Area>
North
<Employee>
Mary
</Employee>
</Area>
<Area>
North
<Employee>
John
</Employee>
</Area>
<Area>
North
<Employee>
Max
</Employee>
</Area>
<Area>
East
<Employee>
Kai
</Employee>
</Area>
<Area>
West
<Employee>
Jina
</Employee>
</Area>

,,..............................

</AreaEmployee>
 
There are four areas in my company , every employees in my company have his
duty area.
I use a select join statement to make a view form Area and Employee table
like below.

I want to make a hierarchical XML form the view.
The XML is like below

How can I do?


the view:

Area Employee
----------------------------------------
North Mary
North John
North Max
East Kai
West Jina
West John
South Tim
South Peter

the hierarchical XML

<?xml version="1.0"?>
<AreaEmployee>
<Area>
North
<Employee>
Mary
</Employee>
</Area>
<Area>
North
<Employee>
John
</Employee>
</Area>
<Area>
North
<Employee>
Max
</Employee>
</Area>
<Area>
East
<Employee>
Kai
</Employee>
</Area>
<Area>
West
<Employee>
Jina
</Employee>
</Area>

,,..............................

</AreaEmployee>
Click to expand...

try something like
<Area value="North">
<Employee value="Mary"/>
<Employee value="...

etc..
that should do the trick

greetz,Leon
 
Select a "distinct" list of areas only from the view.
Write an area node for each of the 4 areas.
In each of those nodes, select all employees that belong to that area.
Add an employee node to the current area node.

You'll end up with the XML described by Leon which is better than the
XML you displayed first hand.

Of course, this uses 5 database hits.
You can do 1 general select statement, order by Area, and then loop
through it to get all the employees for each area.
This would be faster of course, but ever so slightly minutely more
complicated.

I suggest the latter... You'll still end up with Leon's well formed
XML.
 
Thanks
You meant I must use DOM to do that?
But it is so tedious. Is there any convenient method?
 
Back
Top