Get SQL XML element using variable

Question

If I have a column that is like 11.22.33.44.55, I am trying to get one of the values depending on what level the user is asking for. So I have a variable for level. This is what I am using as my starting point:

DECLARE @ProjectID int,
        @Level int
SET @ProjectID = 58
SET @Level = 2

SELECT               
    CAST('<t>' + REPLACE(ParentId1 , '.','</t><t>') + '</t>' AS XML).value('/t[1]','varchar(50)')
FROM 
    @tmptbl
WHERE
    linked_task = @ProjectID

So this works (would return 11), but where I have /t[1], I want to use @Level instead of [1], so that if the supplied level is 2, it will return '22', if it is 3, would return '33', and so on, so I tried the follow

Tried:

CAST('<t>' + REPLACE(ParentId1 , '.','</t><t>') + '</t>' AS XML).value('/t['+@Level+']','varchar(50)')

Get this error:

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

Tried:

CAST('<t>' + REPLACE(ParentId1 , '.','</t><t>') + '</t>' AS XML).value('/t["@Level"]','varchar(50)')

Get this error:

XQuery [value()]: Only 'h ttp://www.w3.org/2001/XMLSchema#decimal?', 'h ttp://www.w3.org/2001/XMLSchema#boolean?' or 'node()*' expressions allowed as predicates, found 'xs:string'

How do I accomplish this?

Don't use XML use JSON `JSON_VALUE('["' + REPLACE(ParentId1 , '.','","') + '"]', concat('$[',@Level,']'))` — Martin Smith, Aug 10 '23 at 21:05
While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky, Aug 10 '23 at 21:07
I know this was closed as a duplicate but using String_Split (https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver16) is really simple now that it has the added EnableOrdinal parameter. — Sean Lange, Aug 10 '23 at 21:10
@SeanLange - also in the dupe https://stackoverflow.com/a/72518700/73226 — Martin Smith, Aug 10 '23 at 21:11

score 1 · Answer 1 · answered Aug 10 '23 at 21:16

Please try the following solution.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, ParentId1 VARCHAR(MAX));
INSERT INTO @tbl (ParentId1) VALUES
('11.22.33.44.55'),
('11.77.33.44.55');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = '.'
    , @Level INT = 2;

SELECT t.*
    , c.value('(/root/r[sql:variable("@Level")]/text())[1]', 'VARCHAR(20)') AS result
FROM @tbl AS t
    CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
        REPLACE(ParentId1, @separator, ']]></r><r><![CDATA[') + 
        ']]></r></root>' AS XML)) AS t1(c);

Output

ID	ParentId1	result
1	11.22.33.44.55	22
2	11.77.33.44.55	77

Get SQL XML element using variable

1 Answers1