Execute xpath having column name into a variable

Question

I need to extract data from a XML column in SQL Server 2016, for this example I'll create a XML variable, but in the real scenario the XML in inside a table so it is not editable.

This is a sample of XML column, in the real there are more columns, field1, field2 and so on....:

declare @temp table (xmlfield xml)

insert into @temp(xmlfield) 
values(N'<root><field1>text in field1</field1><field2>text in field1</field2></root>')

I want to create a procedure that accepts the column name as a parameter

declare @fieldName nvarchar = 'field1' -- this will be the input parameter of a stored procedure

Select T.xmlfield.value('(//root/'+@fieldName+'/text())[1]', 'varchar(255)') result      
From @temp T

This code throws an error

The argument 1 of the XML data type method "value" must be a string literal

XQuery [@temp.xmlfield.value()]: Syntax error near '[', expected a step expression.

Select T.xmlfield.value('(//root/[sql:variable("@fieldName")]/text())[1]', 'varchar(255)') field From @temp T

Is there a way to have a parameterised xpath expression?

Answer 1

You were close. You need a * before the [ character, and then you also need to denote what the variable represents; in this case the local-name. This results in:

DECLARE @temp table (xmlfield xml);

INSERT INTO @temp (xmlfield)
VALUES (N'<root><field1>text in field1</field1><field2>text in field2</field2></root>');

DECLARE @FieldName sysname = N'field2';

SELECT T.xmlfield.value('(//root/*[local-name() = sql:variable("@FieldName")]/text())[1]', 'varchar(255)') AS field
FROM @temp T;