XML SQL retrieval

Feb 3 2020 10:34 PM
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

Answers (1)