Help me with this query please.

[rhaazy]

tblOrgSystem

OrgSystemID OrgSystem
1 USA
2 CANADA
tblOrgSystemNode

OrgSystemID OrgNodeID OrgNode
1 3 Manistique
1 4 Houston
1 7 M-Sales
1 8 M-IT
1 10 H-Sales
1 11 H-IT
2 5 Toronto
2 6 Ontario
2 13 T-Sales
2 14 T-IT
2 16 O-Sales
2 17 O-IT
tblOrgSystemNodeParent

OrgSystemID OrgNodeId OrgNodeParentID
1 3 3
1 4 4
1 7 3
1 8 3
1 10 4
1 11 4
2 5 5
2 6 6
2 13 5
2 14 5
2 16 6
2 17 6
I so far have this query.

select tblOrgSystem.OrgSystem, tblOrgSystemNode.OrgNode,
OrgNodeParentID from
tblOrgSystemNodeParent join tblOrgSystem on tblOrgSystem.OrgSystemID =
tblOrgSystemNodeParent.OrgSystemID join tblOrgSystemNode on
tblOrgSystemNode.OrgNodeID = tblOrgSystemNodeParent.OrgNodeID

This Query gives me this result:
OrgSystem OrgNode OrgNodeParentID
USA Manistique 3
USA Houston 4
USA M-Sales 3
USA M-IT 3
USA H-Sales 4
USA H-IT 4
CANADA Toronto 5
CANADA Ontario 6
CANADA T-Sales 5
CANADA T-IT 5
CANADA O-Sales 6
CANADA O-IT 6
I want this result instead!!!!

OrgSystem OrgNode OrgNodeParentID
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
I cant figure out if I need another join or some kind of union to get
the name of the nodes instead ot the ID....
Also After I figure the query out I need to generate an XML document
that has a heiracheal structure that matches the 'organizational'
structure of the tables....

[rhaazy]

I figured the query out, simple actually:S

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.OrgNodeID

However I still can't figure the XML portion of this out, I would like
it to look like this:

USA
Manistique
M-Sales
M-IT
Houston
H-Sales
H-IT
CANADA
etc....

I want to bind this to a treeview so it can be easily navigated.

[Erland Sommarskog]

rhaazy (rh****@gmail.com) writes:

I figured the query out, simple actually:S

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.OrgNodeID

However I still can't figure the XML portion of this out, I would like
it to look like this:

USA
Manistique
M-Sales
M-IT
Houston
H-Sales
H-IT
CANADA
etc....

That does not look like XML to me.

I want to bind this to a treeview so it can be easily navigated.

Maybe treeviews eat XML documents for lunch, but this a newsgroup about
server software, and we don't know about GUIs. At least I don't.

So please post the XML document you want as output. For a tested query,
it's also helps if you post CREATE TABLE statements for you tables, and
sample data as INSERT statements. (And my experience of FOR XML ELEMENT
is limited, so I need to test before I make a fool of myself in public.)

Also, please specify which version of SQL Server you are using.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[rhaazy]

<root>
<USA>
<Manistique>
<M-Sales/>
<M-IT/>
</Manistique>
<Houston>
<H-Sales/>
<H-IT/>
</Houston>
</USA>
<CANADA>
<Toronto>
etc...
</root>

Hopefully this format is possible...
Erland Sommarskog wrote:

rhaazy (rh****@gmail.com) writes:

I figured the query out, simple actually:S

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.OrgNodeID

However I still can't figure the XML portion of this out, I would like
it to look like this:

USA
Manistique
M-Sales
M-IT
Houston
H-Sales
H-IT
CANADA
etc....

That does not look like XML to me.

I want to bind this to a treeview so it can be easily navigated.

Maybe treeviews eat XML documents for lunch, but this a newsgroup about
server software, and we don't know about GUIs. At least I don't.

So please post the XML document you want as output. For a tested query,
it's also helps if you post CREATE TABLE statements for you tables, and
sample data as INSERT statements. (And my experience of FOR XML ELEMENT
is limited, so I need to test before I make a fool of myself in public.)

Also, please specify which version of SQL Server you are using.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

rhaazy (rh****@gmail.com) writes:

><root>
<USA>
<Manistique>
<M-Sales/>
<M-IT/>
</Manistique>
<Houston>
<H-Sales/>
<H-IT/>
</Houston>
</USA>
<CANADA>
<Toronto>
etc...
</root>

Hopefully this format is possible...

It does not seem to be. When I look at the examples for FOR XML EXPLICIT
and FOR XML PATH, they don't lead to that result. And indeed the XML
document above is sort of funny. A more normal document would be:
<ROOT>
<OrgSystem Name="USA">
<UpperOrgNode Name="Manistique">
<LowerOrdNode Name="M-Sales"/>
<LowerOrdNode Name="M-IT"/>
</UpperOrgNode>

etc.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[rhaazy]

The problem with this is that there is no upper and lower, the number
or depth if you will of orgnodes is limitless... This is what my major
problem is, figuring out how to get this sort of format (i was giving
more less a psuedo definition of what I need) when say for example
M-Sales gets has a child added to it via the database admin.

Hopefully this format is possible...

It does not seem to be. When I look at the examples for FOR XML EXPLICIT
and FOR XML PATH, they don't lead to that result. And indeed the XML
document above is sort of funny. A more normal document would be:
<ROOT>
<OrgSystem Name="USA">
<UpperOrgNode Name="Manistique">
<LowerOrdNode Name="M-Sales"/>
<LowerOrdNode Name="M-IT"/>
</UpperOrgNode>

etc.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

rhaazy (rh****@gmail.com) writes:

The problem with this is that there is no upper and lower, the number
or depth if you will of orgnodes is limitless... This is what my major
problem is, figuring out how to get this sort of format (i was giving
more less a psuedo definition of what I need) when say for example
M-Sales gets has a child added to it via the database admin.

One thing is clear, you can never get the answer you are looking for,
if you only hand out tidbits of information. For instance, some posts
back I asked which version of SQL Server you are using, but you never
told us.

Really what is the best way to describe a hierachical structure of
any depth in XML, I don't know. You may want talk to the people in
microsoft.public.sqlserver.xml.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[rhaazy]

I apologize for my inability to explain what I mean in such a technical
manner I am not accustomed to. I am an intern and with little
programming experience have managed to work 4 very intense months of
not only programming but designing application software for a major
petroleum company. I have only 4 months of real software development,
however I feel this 4 months has truely given me a head start in my
professional career.

In reference to the version of SQL question, the reason I didn't
respond is because I merely wanted what you clearly re-stated for me in
your last post... -_-'

Also as I have mentioned clearly before, I appreciate everyone's help
in my learning process and can only suggest one thing; try to be more
curtious to someone who may not be familiar with programming, the
internet, or even computers in general! I appreciate the "tough love"
in some cases, but most it only causes frustration (and the occasional
laugh off with my co-worker).

Again, thanks everyone for all your help, I always know where to go
when I need anything answered.

One thing is clear, you can never get the answer you are looking for,
if you only hand out tidbits of information. For instance, some posts
back I asked which version of SQL Server you are using, but you never
told us.

[Ed Murphy]

rhaazy wrote:

In reference to the version of SQL question, the reason I didn't
respond is because I merely wanted what you clearly re-stated for me in
your last post... -_-'

That sort of information often affects the answer (even if it happened
not to do so in this particular case). Giving the info and having it
turn out to be irrelevant is much better than omitting it and having
it turn out to be relevant.