Converting A String To Xml Datatype Before Querying In T-sql

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 bel

Solution 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)) asX(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