英文:
Get SQL XML element using variable
问题
Sorry, but I can't assist with translating this code.
英文:
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?
答案1
得分: 1
请尝试以下解决方案。
SQL
-- DDL和样本数据填充,开始
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和样本数据填充,结束
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);
输出
ID | ParentId1 | result |
---|---|---|
1 | 11.22.33.44.55 | 22 |
2 | 11.77.33.44.55 | 77 |
英文:
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 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论