Using OPENXML

Books Online documents how to use OPENXML pretty well, so I'll try not to repeat that information here. Listing 18.17 shows a basic example of how to use OPENXML.

Listing 18.17. 
DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc output,
'<songs>
  <song><name>Somebody to Love</name></song>
  <song><name>These Are the Days of Our Lives</name></song>
  <song><name>Bicycle Race</name></song>
  <song><name>Who Wants to Live Forever</name></song>
  <song><name>I Want to Break Free</name></song>
  <song><name>Friends Will Be Friends</name></song>
</songs>'
SELECT * FROM OPENXML(@hdoc, '/songs/song', 2) WITH
    (name varchar(80))
EXEC sp_xml_removedocument @hDoc

(Results)

 name ------------------------------------------------------------------ Somebody ...

Get Guru's Guide to SQL Server Architecture and Internals, The now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial