Converting a string to XML datatype before querying in T-SQL

Question

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)

Community · Accepted Answer · 2017-04-13 12:42:36Z

5

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

edited Apr 13, 2017 at 12:42

CommunityBot

11 silver badge

answered Feb 28, 2013 at 13:20

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Converting a string to XML datatype before querying in T-SQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related