Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help ! XML problem (please see this updated version) Expand / Collapse
Author
Message
Posted Wednesday, January 30, 2008 1:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:12 AM
Points: 193, Visits: 552
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
Post #449218
Posted Wednesday, January 30, 2008 1:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:12 AM
Points: 193, Visits: 552
Dear all,

Please see my attachment file as the web site cannot display an XML file properly.


  Post Attachments 
My problem.doc (7 views, 20.50 KB)
Post #449219
Posted Wednesday, January 30, 2008 2:42 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 1:49 AM
Points: 1,694, Visits: 19,551
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



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #449227
Posted Wednesday, January 30, 2008 6:35 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:12 AM
Points: 193, Visits: 552
Dear Mark,

It worked.

You are very brilliant.

Thanks a lot.

Are you US citizen ?
Post #449768
Posted Thursday, January 31, 2008 12:08 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 1:49 AM
Points: 1,694, Visits: 19,551
Thanks, I'm a UK citizen.

____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #449827
Posted Thursday, January 31, 2008 12:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:12 AM
Points: 193, Visits: 552
I am a Chinese and living in Hong Kong.
Post #449828
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse