英文:
Parse column value in SQL Server
问题
这个字符串在一个列中：
<item id="1" value="1"><item id="2" value="6"><item id="4" value="8">
转化为：
Item ID value
1 1
2 6
4 8
英文:
I have a table with a Varchar column with the value as mentioned below. How can i parse this string into columns
This string in a column
<item id="1" value="1"><item id="2" value="6"><item id="4" value="8">
into
Item ID value
--------------
1 1
2 6
4 8
答案1
得分: 2
你的数据类似于 XML 片段，并且它不是格式良好的。
你所需要做的就是动态生成 XML。之后，XQuery 就变得非常容易（正如 @Larnu 已经指出的）。不需要解析任何东西。
SQL
-- DDL 和示例数据填充，开始
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, tokens NVARCHAR(MAX));
INSERT @tbl (tokens) VALUES
(N'<item id="1" value="1"><item id="2" value="6"><item id="4" value="8">');
-- DDL 和示例数据填充，结束
SELECT c.value('@id', 'INT') AS id
, c.value('@value', 'INT') AS [value]
FROM @tbl t
CROSS APPLY (SELECT TRY_CAST('<root>' +
REPLACE(tokens, '>', '/>') +
'</root>' AS XML)) AS t1(x)
CROSS APPLY x.nodes('/root/item') AS t2(c);
输出
| id | value |
|---|---|
| 1 | 1 |
| 2 | 6 |
| 4 | 8 |
英文:
Your data resembles XML fragment. And it is not well-formed.
All what you need is to compose XML on the fly. After that XQuery makes it very easy (as @Larnu pointed out it already). No need to parse anything.
SQL
-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, tokens NVARCHAR(MAX));
INSERT @tbl (tokens) VALUES
(N'<item id="1" value="1"><item id="2" value="6"><item id="4" value="8">');
-- DDL and sample data population, end
SELECT c.value('@id', 'INT') AS id
, c.value('@value', 'INT') AS [value]
FROM @tbl t
CROSS APPLY (SELECT TRY_CAST('<root>' +
REPLACE(tokens, '>', '/>') +
'</root>' AS XML)) AS t1(x)
CROSS APPLY x.nodes('/root/item') AS t2(c);
Output
| id | value |
|---|---|
| 1 | 1 |
| 2 | 6 |
| 4 | 8 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论