Help ! XML problem (please see this updated version)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Help ! XML problem (please see this updated...

Help ! XML problem (please see this updated version)

Rate Topic

Display Mode

Topic Options

Author

Message

onlo

Posted Wednesday, January 30, 2008 1:36 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 10:34 PM
Points: 187, Visits: 516

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

onlo

Posted Wednesday, January 30, 2008 1:43 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 10:34 PM
Points: 187, Visits: 516

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

Mark-101232

Posted Wednesday, January 30, 2008 2:42 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 11:30 PM
Points: 1,500, Visits: 18,187

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

onlo

Posted Wednesday, January 30, 2008 6:35 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 10:34 PM
Points: 187, Visits: 516

Dear Mark,

It worked.

You are very brilliant.

Thanks a lot.

Are you US citizen ?

Post #449768

Mark-101232

Posted Thursday, January 31, 2008 12:08 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 11:30 PM
Points: 1,500, Visits: 18,187

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

onlo

Posted Thursday, January 31, 2008 12:11 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 10:34 PM
Points: 187, Visits: 516

I am a Chinese and living in Hong Kong.

Post #449828

« Prev Topic | Next Topic »

Permissions