51工具盒子英文:
Get SQL XML element using variable
问题 {#heading}
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}
得分: 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 |
