Converting a string to XML datatype before queryin

问题:

How do I convert a string into an XML datatype so that I can query the data as XML:

For example (thanks to "mellamokb the Wise" who provided the original SQL for this)

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)

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)

回答1:

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 Fiddle

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