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.
