问题描述
如何将字符串转换为 XML 数据类型，以便我可以将数据作为 XML 查询:
How do I convert a string into an XML datatype so that I can query the data as XML:
例如(感谢mellamokb the Wise"为此提供了原始 SQL)
For example (thanks to "mellamokb the Wise" who provided the original SQL for this)
如果 xmlstring 是 XML 类型，则下面的代码可以正常工作(请参阅 DEMO)
The code below works fine if xmlstring is of the type XML (see DEMO)
select id, name
from Data
cross apply (
select Destination.value('data(@Name)', 'varchar(50)') as name
from [xmlstring].nodes('/Holidays/Summer/Regions/Destinations/Destination') D(Destination)
) Destinations(Name)
但是，如果 xmlString 是 varchar 类型，即使我将字符串转换为 XML，我也会收到错误消息 (演示):
However, if xmlString is of type varchar I get an error even though I'm converting the string to XML (DEMO):
select id, name
from Data
cross apply (
select Destination.value('data(@Name)', 'varchar(50)') as name
from CONVERT(xml,[xmlstring]).nodes('/Holidays/Summer/Regions/Destinations/Destination') D(Destination)
) Destinations(Name)
推荐答案
您可以在一个额外的交叉应用中进行转换.
You can do the cast it in one extra cross apply.
select id,
T.N.value('@Name', 'varchar(50)') as name
from Data
cross apply (select cast(xmlstring as xml)) as X(X)
cross apply X.X.nodes('/Holidays/Summer/Regions/Destinations/Destination') T(N)
SQL 小提琴
转换为 XML 可能存在性能问题.看看这个答案和这个答案
There might be performance issues with casting to XML. Have a look at this answer and this answer
这篇关于在 T-SQL 中查询之前将字符串转换为 XML 数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！