How to access values based on column position?

Question

Hi,

Suppose I have a table like this:

Can I change the query from:

SELECT col1, [long_name_col3 + long_name_col10],... from DATA

to this:

SELECT col1, get_col(3) + get_col(10) from DATA

by using clr function? I mean, can I access values based on column position?

Thank you!

Answer 1

Hi @DangDKhanh-2637,

Please try the following solution based on XML and XQuery functionality built-in in the SQL Server.

It is using column position based on XPath predicate [position] which is column position.

-- DDL and sample data population, start
DECLARE @tbl TABLE (col1 INT, col2 INT, long_name_col3 INT, col4 INT);
INSERT @tbl (col1, col2, long_name_col3, col4) VALUES
(15, 20, 35, 40),
(25, 30, 45, 18);
-- DDL and sample data population, end

SELECT * FROM @tbl;

SELECT t.col1
	, x.value('(/root/*[2]/text())[1]', 'INT') AS col2
	, x.value('(/root/*[3]/text())[1]', 'INT') AS col3
	, x.value('(/root/*[4]/text())[1]', 'INT') AS col4
	, x.value('(/root/*[3]/text())[1]', 'INT')  +
	 x.value('(/root/*[4]/text())[1]', 'INT') AS colX
FROM @tbl AS t
CROSS APPLY (SELECT t.* FOR XML PATH(''), TYPE, ROOT('root')) AS t1(x);

Answer 2

No. Sql maps the column or variable value to the clr function parameter. For a clr function to access row data it must do its own data query.