How to get the second or third element
DYNAMICALLY of a XML document on XQuery with SQL Server, specifically using the .value method.
By dynamically I mean that I can use a SQL SERVER variable like
DECLARE @Index int
with some sort of syntax within the first parameter of the
.value method , because if you just use a VARCHAR variable, for example, you get an error like :
The argument 1 of the XML data type method "value" must be a string literal
This is in Sql server 2008.
I havent found answer to this on internet, or maybe I dont know how to question, but there's only samples to access the first element or the second and so on but hardcoding.
I've seen a similar syntax like the following but with the
.exists method.
(and this is what I would like to do)
-- This is clearly an example of MSDN
DECLARE @Index int
DECLARE @myDoc xml
SET @Index = 3
SET @myDoc = '<Root>
<ProductDescription ProductID="9865" ProductName="Road Bike">
.....'
SELECT @myDoc.value('(/Root/ProductDescription/@ProductID)[ =sql:variable("@Index") ]', 'int' )
Unfortunately this produces the error : XQuery [value()]: Syntax error near '='
Context :
I have a process that needs to execute actions based on the values of the second or third element.
There is no possibility to change the field in database or the way the XML is structured.