这里是另一个版本。我还向不带福特XML的表中添加了另一行。
SQL
-- DDL and sample data population,start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY,xml_data XML);
INSERT INTO @tbl (xml_data)
VALUES
(N'<cars>
<car name="Honda" value="11" />
<car name="Toyota" value="22" />
<car name="Ford" value="3333" />
<car name="Ferarri" value="444" />
</cars>'),(N'<cars>
<car name="Honda" value="11" />
<car name="Toyota" value="22" />
<car name="Ferarri" value="444" />
</cars>');
-- DDL and sample data population,end
SELECT id,[xml_data].value('(/cars/car[@name="Ford"]/@value)[1]','INT') AS [value]
FROM @tbl AS tbl;
输出
+----+-------+
| id | value |
+----+-------+
| 1 | 3333 |
| 2 | NULL |
+----+-------+
,
在提出问题时提供DDL和样本数据填充是一个好主意。您最初的XPath肯定已关闭。
在您的情况下,需要CROSS APPLY
来实现所需的功能。检查一下:
SQL
-- DDL and sample data population,xml_data XML);
INSERT INTO @tbl (xml_data)
VALUES
(N'<cars>
<car name="Honda" value="11" />
<car name="Toyota" value="22" />
<car name="Ford" value="3333" />
<car name="Ferarri" value="444" />
</cars>');
-- DDL and sample data population,end
SELECT col.value('@value','INT') AS [value]
FROM @tbl AS tbl
CROSS APPLY tbl.[xml_data].nodes('/cars/car[@name="Ford"]') AS tab(col);
输出
+-------+
| value |
+-------+
| 3333 |
+-------+
本文链接:https://www.f2er.com/3149788.html