47614

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 below works fine if xmlstring is of the type XML (see <strong>DEMO</strong>)

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 (<strong>DEMO</strong>):

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)

Answer1:

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

Recommend

  • Get the EndElement Node of an XElement
  • Xerces XMLString::transcode null/empty reply
  • Jenkins build fails after running Cucumber tests on Java heap space exception
  • cakephp model association/join with same table [closed]
  • A quick question about aggregate relational objects in MVC
  • Add Jpanel to Jframe NetBeans
  • How to convert Neo4j Result to GraphJSON
  • Recursion in ASP.NET Core Razor views
  • formediting modals shows at wrong position
  • Angular Bootstrap Carousel Slide Transition not working correctly
  • How to use remove-erase idiom for removing empty vectors in a vector?
  • Pass value from viewmodel to script in zk
  • Using $this when not in object context
  • Sails.js/waterline: Executing waterline queries in toJSON function of a model?
  • Jenkins: How To Build multiple projects from a TFS repository?
  • Deselecting radio buttons while keeping the View Model in synch
  • Getting last autonumber in access
  • Counter field in MS Access, how to generate?
  • Sony Xperia Z Tablet not found by adb
  • How to recover from a Spring Social ExpiredAuthorizationException
  • Incrementing object id automatically JS constructor (static method and variable)
  • How to check if every primary key value is being referenced as foreign key in another table
  • ILMerge & Keep Assembly Name
  • Can I have the cursor start on a particular column by default in jqgrid's edit mode?
  • Large data - storage and query
  • jquery mobile loadPage not working
  • How can I estimate amount of memory left with calling System.gc()?
  • WOWZA + RTMP + HTML5 Playback?
  • How to delete a row from a dynamic generate table using jquery?
  • json Serialization in asp
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • WPF Applying a trigger on binding failure
  • How can I get HTML syntax highlighting in my editor for CakePHP?
  • Hits per day in Google Big Query
  • How do you join a server to an Active Directory (domain)?
  • How do I configure my settings file to work with unit tests?
  • How to stop GridView from loading again when I press back button?
  • How does Linux kernel interrupt the application?
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • Binding checkboxes to object values in AngularJs