How to join using several junction tables to XML
-
Hello all,
(this is my first post to a forum, please let me know if I am in the wrong forum)
I have a database of personal info. The addresses and phone numbers are broken out to separate tables, each with a junction table. My objective is to use the "FOR XML" feature to get all column data.
My join of these tables is likely my problem. I am seeing is that the address info is subordinate to the phone information where they should be siblings.
Is there a sample out there on how to form a query using more than one junction table? I am certain my join of the address tables is incorrect.
This query:
select PD.*,Phones.*,dbo.Address.*
from PD
left outer join PD_Phone_link on (PD_Phone_link.PD_ID=PD.ID)
left outer join Phones on (Phones.id = PD_Phone_link.PhoneID)
left outer join PD_Address_link on(PD_Address_link.PD_ID =PD.ID)
left outer join dbo.Address on (dbo.Address.id = PD_Address_link.AddressID)
FOR XML Auto, elements
gets this result. Not that dbo.Address is "under" phones.
TIA,
John
-
Let me apologize for my identity. I am in no way associated with SQL Server Central. To limit spam I create email addresses that I can eliminate when they get too much garbarge. Unfortunately I did not consider how it appears in the forumn. I will create a more appropriate identity for myself in future posts.
John
-
Hi John
Two things:
* First: You can change your nickname if you click "My Account" in header section.
* Second: Please attach your XML as file. 😉
Greets
Flo
-
Hmm I was fairly certain I had pasted the XML. Do I need code tags?
-
😀 Take 3:
-
:hehe:
One more try for the XML....
-
Falvio (4/1/2009)
:hehe:One more try for the XML....
Hi Flavio
First: Compliment for XML posting. 😀
You should have a look to the FOR XML EXPLICIT feature. There are many examples in BOL.
If you have problems with it maybe you should provide the table DDL and some sample data (maybe have a look to the best practices link in my signature to get best feedback).
Greets
Flo
-
Your join is fine. This is the way that auto mode works. If you’ll look closely at your XML, you’ll see that there is a hierarchy in it. The top level is the columns that came from PD table. The middle level is the columns that came from Phones table and the last level is anything that came from Address table. When you use auto mode the server does the hierarchy for you automatically. The order of the columns in the select clause determines the hierarchy. Since the first column in the select clause was from PD table, this table is at the top of the hierarchy. The next table that was in the select clause was phones, so it was the next in the hierarchy. The last table in the hierarchy was addresses talbe because it was the last table that was referenced in the select clause. If you want to change the hierarchy you can use explicit mode or path mode. Another option is to use derived table (you can also try common table expression, I didn’t try it but I don’t see why it won’t work) and run the select statement with the for xml clause on the derived table/CTE. This way you can make all tables appear as one level hierarchy.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Suggest you use FOR XML PATH with subqueries. Without proper DDL and sample data its difficult to give you anything concrete, but I would expect something like this
select PD.ID as "ID",PD.FirstName as "FirstName",
(select Phones.Number as "Number",
Phones.Ext as "Ext"
from Phones
inner join PD_Phone_link on Phones.id = PD_Phone_link.PhoneID
and PD_Phone_link.PD_ID=PD.ID
for xml path('Phones'),type),
(select a.AddressType as "AddressType",
a.Address1 as "Address1"
from dbo.Address a
inner join PD_Address_link on a.id = PD_Address_link.AddressID
and PD_Address_link.PD_ID=PD.ID
for xml path('Address'),type)
from PD
for xml path('PD')
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Great explanation Adi.
"Keep Trying"
-
Chirag (4/2/2009)
Great explanation Adi.Thank you. Nice to get a good feedback sometimes:-)
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Thanks is what I needed 😀
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply