Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Inserting into an XML document

I was reading through the latest XML Workshop article that I have from Jacob Sebastian. It’s a fantastic series for learning little bits of XML as you go and I’ve tried to read through most of them and learn something. I’m not a big XML guy, but I am trying to work on learning one thing each day.

The article goes through this, but adding an element to an XML document is easy with the insert element syntax. I experimented a bit with Jacob’s syntax in a few ways. I decided I’d use the current list of books I’m reading as my data. Here’s my first thing:

DECLARE @x xml          
SET @x = '<Books></Books>'          

DECLARE @name VARCHAR(50)
SELECT @name = 'The Federalist Papers'

SET @x.modify('
    insert element Employee {sql:variable("@name")}
    into (Books)[1]
    ')

SELECT @x

That returned (spacing mine)

<Books>

<Employee>

  The Federalist Papers

</Employee>

</Books>

As you can see, I cut and pasted Jacob’s code before playing with it, so I have the inner element name wrong. I fixed that, and then added a second insert.

DECLARE @x xml          
SET @x = '
<Books>
</Books>'          

DECLARE @name VARCHAR(50)
SELECT @name = 'The Federalist Papers'

SET @x.modify('
    insert element Book {sql:variable("@name")}
    into (Books)[1]
    ')

SELECT @name = 'Treasure Hunt'

SET @x.modify('
    insert element Book {sql:variable("@name")}
    into (Books)[1]
    ')

SELECT @x

Which returned this (my spacing):

<Books>

  <Book>The Federalist Papers</Book>

  <Book>Treasure Hunt</Book>

</Books>

I left out the first keyword, but I’ll add it in here.

DECLARE @x xml          
SET @x = '
<Books>
</Books>'          

DECLARE @name VARCHAR(50)
SELECT @name = 'The Federalist Papers'

SET @x.modify('
    insert element Book {sql:variable("@name")}
    into (Books)[1]
    ')

SELECT @name = 'Treasure Hunt'
SET @x.modify('
    insert element Book {sql:variable("@name")}
    into (Books)[1]
    ')

SELECT @name = 'Total Recall'
SET @x.modify('
    insert element Book {sql:variable("@name")}
    as first
    into (Books)[1]
    ')

SELECT @x

That gives me the first book as Total Recall, which is a great book.

<Books>

  <Book>Total Recall</Book>

  <Book>The Federalist Papers</Book>

  <Book>Treasure Hunt</Book>

</Books>

I checked books inline for the insert command and found that there are first and last keywords you can use for elements, but not attributes.

I decided to get fancy with an attribute then. I wanted to add the author attribute for the book Total Recall. So I tried a little hard coding. I knew this was the first element in my document (below the root), so checking the example in Books Online, I tried this:

SELECT @name = 'Jim Gemmell'
SET @x.modify('
    insert attribute author {sql:variable("@name")}
     into (/Books/Book[1])[1]
    ')

It worked! I got this result.

<Books>

  <Book author="Jim Gemmell">

    Total Recall

  </Book>

  <Book>The Federalist Papers</Book>

  <Book>Treasure Hunt</Book>

</Books>

Nothing amazing here, but since I don’t work with XML data much at this level, it was kind of cool to use Jacob’s article and actually accomplish something.

Comments

Posted by AjarnMark on 23 March 2010

Steve, great post!  Just right for me, as I don't know a whole lot of XML either, but should probably learn some more.  I'll have to check out Jacob's articles.

By the way, are you really reading The Federalist Papers?  I have that on my stack of books to read this year, too.

Posted by Steve Jones on 24 March 2010

It's interesting stuff. Not sure I'd use it to do work on my side, but I think it would be a good skill for debugging things like SSIS and perhaps fixing developers' work.

Posted by dbowlin on 26 March 2010

Having had limited exposure to manipulating XML documents this is just my speed.  The world is moving so many things to XML a better understanding moving forward is essential.  Thanks for this post and the reference to the series.  I have bookmarked the series so I can go through it from beginning to end.

Posted by DEK on 26 March 2010

Has anyone addressed XSD in relation to this?  I've read up on it a little, but haven't had the time to fully explore it, and how its related to this.  

I'd really like to find a way to relate the schema of a table to the equivalent XSD definition (a tool in SQL Server?).  I do some logging (in XML), but populate my data in a table variable first, then load the XML destination, either a variable or field, with the temp table data using SELECT ... FOR XML.

Posted by Steve Jones on 26 March 2010

I haven't done much XSD. Seems fairly heavyweight, but you could ask Jacob Sebastian (author of the article I linked). He also wrote The Art of XSD (www.sqlservercentral.com/.../65843)

Posted by Charles Kincaid on 26 March 2010

I'm not doing XML in  SQL yet but I'm doing a bunch of XML in applications code.  The .Dot .Net stuff for handling XML documents and elements is cool.  

I could move all my stuff into SQL but none of the cool stuff (like XML, date data type, geographical data, etc.) has made it into the Mobile.

Posted by jamescox on 26 March 2010

Any applicability of this technique to insert SELECTed data into the XML structure that underlies Excel 2007 new .xlsx workbook structure (or Word 2007's new .docx structure)?

Posted by Adrian Hains on 26 March 2010

DEK - I put up a simple example of generating the XSD automatically from a table - hainstech.wordpress.com/.../automatic-creation-of-xml-schema-xsd-for-a-sql-server-table

Leave a Comment

Please register or log in to leave a comment.