SQL Server 2019查询在尝试将单列数据分离为多列数据时返回所有空值

Question

我有一个使用/作为分隔符的数据表。我试图将单列数据分成三列。

我的查询返回了正确的行数，但所有三列都为NULL。有没有什么东西像我错过的那样明显地从页面上跳下来？

以下是数据如何存储在PGroup列中：

Steve/Mouse/Vitamin
Matt/Cat/Soda
Shelly/Dog/Bread

我希望返回3个独立的列：Name, Pet, Food

DECLARE @delimiter VARCHAR(50)
SET @delimiter='/' 
;WITH CTE AS
(
SELECT 
CAST('' + REPLACE([PGroup], @delimiter, '') + '' AS XML) AS [PXML]
FROM 
xx123
)
SELECT
[PXML].value('/M[1]', 'varchar(50)') As [Name],
[PXML].value('/M[2]', 'varchar(50)') As [Pet],
[PXML].value('/M[3]', 'varchar(50)') As [Food]
FROM 
CTE

Answer 1

请尝试以下解决方案。

注意事项：

• CData部分保护不受XML实体的影响，如喜欢
• XPath表达式已调整为使用text()来提高性能原因

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, tokens VARCHAR(1000));
INSERT INTO @tbl (tokens) VALUES
('Steve/Mouse/Vitamin'),
('Matt/Cat/Soda'),
('Shelly/Dog/Bread');
-- DDL and sample data population, end
DECLARE @separator CHAR(1) = '/';
SELECT ID
, c.value('(/root/r[1]/text())[1]', 'varchar(50)') As [Name]
, c.value('(/root/r[2]/text())[1]', 'varchar(50)') As [Pet]
, c.value('(/root/r[3]/text())[1]', 'varchar(50)') As [Food]
FROM @tbl
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
REPLACE(tokens, @separator, ']]></r><r><![CDATA[') + 
']]></r></root>' AS XML)) AS t(c);

输出

+----+--------+-------+---------+
| ID |  Name  |  Pet  |  Food   |
+----+--------+-------+---------+
|  1 | Steve  | Mouse | Vitamin |
|  2 | Matt   | Cat   | Soda    |
|  3 | Shelly | Dog   | Bread   |
+----+--------+-------+---------+