Help ! XML problem (please see this updated version)
-
My problem : I have 2 tables
Table "Company"
Company_ID Member_IDs
101
201
Table "Member_info"
Member_ID Member_name
1011 Member_1011
1012 Member_1012
I want to have :
Company_ID Member_ID_name
101
201 ....
My problem :
(1)How can I make my sp or SQL statement to scan all the ID node of the "Company" table ? I mean all ID nodes within each records and then all records ?
(2)Now, I can only hard-code like this :
SELECT Company.Member_IDs.query('
element IDs
{
element ID { string((/IDs/ID)[1])},
element Name { sql:column("Member_info.Member_Name") }
}
') AS Result,
Company_ID
FROM Company, Member_info
WHERE Company.Member_IDs.value('(/IDs/ID)[1]','int')
= Member_info.Member_ID
-
Dear all,
Please see my attachment file as the web site cannot display an XML file properly.
-
select c.Company_ID,(select r.value('.','int') as ID,
m.Member_name as Name
from c.Member_IDs.nodes('/IDs/ID') as x(r)
inner join Member_info m on m.Member_ID=r.value('.','int')
for xml path(''),root('IDs')) as Member_ID_name
from Company c
____________________________________________________
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
-
Dear Mark,
It worked.
You are very brilliant.
Thanks a lot.
Are you US citizen ?
-
Thanks, I'm a UK citizen.
____________________________________________________
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 -
I am a Chinese and living in Hong Kong.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply