Is it possible to have the predicate value a variable.

Example:

DECLARE @XmlData        xml
       ,@Predicate      int
       ,@Value          nvarchar (65)


SELECT @XmlData = '<DocumentPacket>
                     <Party>
                       <Number>1000</Number>
                       <ModeOfTransportation>
                         <Code>40</Code>
                         <Description>Air</Description>
                       </ModeOfTransportation>
                     </Party>
                     <Party>
                       <Number>2000</Number>
                       <ModeOfTransportation>
                         <Code></Code>
                         <Description></Description>
                       </ModeOfTransportation>
                     </Party>
                   </DocumentPacket>'

  SET @Predicate = 1

  -- This works fine.
  SELECT @Value = @XmlData.value('(/DocumentPacket/Party/Number)[1]',
'nvarchar(65)')


  -- I want to do something like this, is this possible?
  SELECT @Value =
@XmlData.value('(/DocumentPacket/Party/Number)[sql:variable("@Predicate")]',
'nvarchar(65)')

Hello Loren,

Sure, you almost had it:

SELECT @Value =
@XmlData.value('(/DocumentPacket/Party/Number)[sql:variable("@predicate")][1]',
'nvarchar(65)')

Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Thank-you, it also works for me now.

Show quoteHide quote

XML limits in SQL/Server?
Extracting xml values using xquery
Need help with XML Bulk Load
Newbie - generate XML tree from parent-child hierarchy
Using sp_executesql to dynamically query xml