Need help creating this XML document...
rhaazy
After performing this query on the database(tables and relations should
be irrelevant for the question):
select tblOrgSystem.OrgSystem, a.OrgNode, b.OrgNode from
tblorgsystemnodeparent
join tblOrgSystem on tblOrgSystem.OrgSystemID =
tblorgsystemnodeparent.OrgSystemID
join tblOrgSystemNode a on a.OrgNodeID =
tblorgsystemnodeparent.OrgNodeID
join tblOrgSystemNode b on b.OrgNodeId =
tblorgsystemnodeparent.orgnodeparentid
I get this for a result:
OrgSystem a.OrgNode b.OrgNode(Parent Column)
USA Manistique Manistique
USA Houston Houston
USA M-Sales Manistique
USA M-IT Manistique
USA H-Sales Houston
USA H-IT Houston
CANADA Toronto Toronto
CANADA Ontario Ontario
CANADA T-Sales Toronto
CANADA T-IT Toronto
CANADA O-Sales Ontario
CANADA O-IT Ontario
If a.OrgNode = b.OrgNode then that means it has no parent node, meaning
it belongs directly to a system.
I want to use a FOR XML EXPLICIT statemen to get an XML document that
looks like this:::
<OrgStruct>
<Systems>
<System Name="USA">
<Manistique>
<M-Sales/>
<M-IT/>
</Manistique>
<Houston>
<H-Sales/>
<H-IT/>
</Houston>
</System>
<System Name="CANADA">
<Toronto>
<T-Sales/>
<T-IT/>
</Toronto>
<Ontario>
<O-Sales/>
<O-IT/>
</Ontario>
</System>
</Systems>
</OrgStruct>