問題描述

如何將字符串轉(zhuǎn)換為 XML 數(shù)據(jù)類型，以便我可以將數(shù)據(jù)作為 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 類型，即使我將字符串轉(zhuǎn)換為 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)

推薦答案

您可以在一個額外的交叉應(yīng)用中進(jìn)行轉(zhuǎn)換.

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 小提琴

轉(zhuǎn)換為 XML 可能存在性能問題.看看這個答案和這個答案

There might be performance issues with casting to XML. Have a look at this answer and this answer

這篇關(guān)于在 T-SQL 中查詢之前將字符串轉(zhuǎn)換為 XML 數(shù)據(jù)類型的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！