Chapter 15. XML and SQL Server: OPENXML

for RuBoard

Generally, in battle, use the normal force to engage; use the extraordinary to win.

Sun Tzu ^[1]

^[1] Tzu, Sun. The Art of War . Cambridge, England: Oxford University Press, 1963. Page 91.

In this chapter we'll talk about SQL Server's OPENXML() function and how it's used to read XML documents. We'll also touch on the new features and changes coming in the forthcoming XML for SQL Server Web Release 1. This will be the final chapter in our coverage of SQL Server's XML functionality.

OPENXML() is a built-in Transact-SQL function that can return an XML document as a rowset. In conjunction with sp_xml_preparedocument and sp_xml_removedocument, OPENXML() allows you to break down (or shred) nonrelational XML documents into relational pieces than can be inserted into tables.

The Books Online documents OPENXML() well, so I won't repeat it here. Here's a basic example of how to use OPENXML():

 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 to Love These Are the Days of Our Lives Bicycle Race Who Wants to Live Forever I Want to Break Free Friends Will Be Friends 

To use OPENXML(), you follow these basic steps:

1. Call sp_xml_preparedocument to load the XML document into memory. MSXML's DOM parser is called to translate the document into a tree of nodes that you can then access with an XPath query. A pointer to this tree is returned by the procedure as an integer.

2. Issue a SELECT from OPENXML(), passing in the handle you received in step 1.

3. Include XPath syntax in the call to OPENXML() in order to specify exactly what nodes you want to access.

4. Optionally include a WITH clause that maps the XML document into a specific table schema. This can be a full table schema as well as a reference to a table itself.

OPENXML() is extremely flexible, so several of these steps have variations and alternatives, but this is the basic process you follow to shred and use an XML document with OPENXML().

Here's a variation of the earlier query that uses a table to define the schema used to map the document:

 USE tempdb GO create table songs (name varchar(80)) go 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 songs EXEC sp_xml_removedocument @hDoc GO DROP TABLE songs 

(Results)

 name --------------------------------------------------------------------------- Somebody to Love These Are the Days of Our Lives Bicycle Race Who Wants to Live Forever I Want to Break Free Friends Will Be Friends 

You can also use the WITH clause to set up detailed mappings between the XML document and the tables in your database. Here's an example:

 DECLARE @hDoc int EXEC sp_xml_preparedocument @hDoc output, '<songs>        <artist name="Johnny Hartman">        <song> <name>It Was Almost Like a Song</name></song>        <song> <name>I See Your Face Before Me</name></song>        <song> <name>For All We Know</name></song>        <song> <name>Easy Living</name></song>        </artist>        <artist name="Harry Connick, Jr.">        <song> <name>Sonny Cried</name></song>        <song> <name>A Nightingale Sang in Berkeley Square</name></song>        <song> <name>Heavenly</name></song>        <song> <name>You Didn''t Know Me When</name></song>        </artist> </songs>' SELECT * FROM OPENXML(@hdoc, '/songs/artist/song', 2) WITH (artist varchar(30) '../@name',       song varchar(50) 'name') EXEC sp_xml_removedocument @hDoc 

(Results)

 artist                         song ------------------------------ -------------------------------------------- Johnny Hartman                 It Was Almost Like a Song Johnny Hartman                 I See Your Face Before Me Johnny Hartman                 For All We Know Johnny Hartman                 Easy Living Harry Connick, Jr.             Sonny Cried Harry Connick, Jr.             A Nightingale Sang in Berkeley Square Harry Connick, Jr.             Heavenly Harry Connick, Jr.             You Didn't Know Me When 

Note that attribute references are prefixed with the "@" symbol. In this example we supply an XPath query that navigates the tree down to the Song element, then reference an attribute called Name in song's parent element, Artist. For the second column, we retrieve a child element of song that's also called Name.

Here's another example:

 DECLARE @hDoc int EXEC sp_xml_preparedocument @hDoc output, '<songs>        <artist> <name>Johnny Hartman</name>        <song> <name>It Was Almost Like a Song</name></song>        <song> <name>I See Your Face Before Me</name></song>        <song> <name>For All We Know</name></song>        <song> <name>Easy Living</name></song>        </artist>        <artist> <name>Harry Connick, Jr.</name>        <song> <name>Sonny Cried</name></song>        <song> <name>A Nightingale Sang in Berkeley Square</name></song>        <song> <name>Heavenly</name></song>        <song> <name>You Didn''t Know Me When</name></song>        </artist> </songs>' SELECT * FROM OPENXML(@hdoc, '/songs/artist/name', 2) WITH (artist varchar(30) '.',       song varchar(50) '../song/name') EXEC sp_xml_removedocument @hDoc 

(Results)

 artist                         song ------------------------------ -------------------------------------------- Johnny Hartman                 It Was Almost Like a Song Harry Connick, Jr.             Sonny Cried 

Notice that we only get two rows. Why is that? It's because our XPath pattern navigated to the Artist/Name node, of which there are only two. In addition to getting each artist's Name element, we also grabbed the name of its first Song element. In the previous query, the XPath pattern navigated us to the Song element, of which there were eight, then referenced each song's parent node (it's Artist) via the XPath ".." designator.

Note the use in the previous query of the XPath "." specifier . This merely references the current element. We need it here because we are changing the name of the current element from name to artist. Keep this technique in mind when you want to rename an element you're returning via OPENXML().

for RuBoard