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.