XML SQL retrieval

Question

Hi All,

 

I am trying to retrieve xml values dynamically as I don't know how many RuleIDs I will get. Here is my code. It's returning null

 

declare @world table(ID uniqueidentifier primary key default(newid()),Person xml)

insert into @world(Person) values('<Person>

<RuleID1>1</RuleID1>

<RuleID1>2</RuleID1>

</Person>')

select x.y.value('(RuleID1)[1]','int') from @world

cross apply Person.nodes('(/Person/*)') x(y)

 

Please help. I can get as many RuleID1 tags, its dynamic. I want all of its values

 

Thanks

Deepali