basically, if i UNION each select statement with node numbers [1],[2]....etc i get the desired results.

but as i said, i want to pass the node number as a variable...

sorry. still no luck.

declare @t int

set @t = 1

SELECT

DISTINCT c.value('(DEVELOPMENT/ID)[1]','nvarchar(25)') AS [ID],

c.value('(MARKETING/HOURS/SPECIALEVENT/TYPE1[position()=sql:variable("@t")])[1]','nvarchar(25)')

,c.value('(MARKETING/HOURS/SPECIALEVENT/CLOSED[position()=sql:variable("@t")])[1]','nvarchar(25)')

FROM [Landing].[X].[Xml]

CROSS APPLY

XmlCol.nodes('//P_RECORDSETRECORD') T(c)

Thanks for your prompt reply.

i think i didnt explain you clearly what i am looking for!

in the xml i have ID under DEVELOPMENT element.

i want to assign TYPE1 (ChristmasDay,BoxingDay,TrainingDay,DoomsDay) which...

hi,

hope you can help me with the below issue.

i am trying to assign a dynamic node number in the stored procedure.

below is the xml i have. i need to query...