XML Workshop XXIII - A TSQL ATOM Library

Introduction

In the last few sessions of XML Workshop we had been looking at ways of generating RSS/ATOM Feeds. You can find the previous sessions here. We have seen how to generate RSS and ATOM feeds in SQL Server 2005 as well as 2000. In the previous sessions, we have seen how to generate RSS and ATOM feeds using FOR XML

PATH as well as FOR XML EXPLICIT. If you are working with SQL Server 2005 (and above), you can take advantage of FOR XML PATH and if you are still in SQL Server 2000, you can use FOR XML EXPLICIT.

In part XIX of XML Workshop (Generating an ATOM 1.0 Feed) we generated an ATOM 1.0 feed using FOR XML PATH. You need to be very careful when writing this code, because, if the XML does not follow the correct structure and if the values are not in the correct format, the XML document will not be recognized as a valid feed. To make this task easier, in this session, we will create a function that generates an ATOM 1.0 feed from a given feed and entry information. We will create a

function that accepts two XML parameters (feed and entry) and generate the required feed structure and returns an XML document.

In XML Workshop XXII, we saw a function that accepts two XML parameters and generates a valid RSS 2.0 feed. In this session we will write the ATOM version of the above function.

We will write a function that accepts two XML parameters containing feed and entry information and generates

a valid ATOM 1.0 feed. We will be able to call the function as in the example given below.

-- declare the variables
DECLARE @fd XML, @ent XML
 
-- create an XML document with Feed information
SELECT @fd = (
    SELECT column_list FROM your_table
    FOR XML PATH(''), ROOT('Feed')
)
 
-- create an XML document with Entry information    
SELECT @ent = (
    SELECT column_list FROM your_table
    FOR XML PATH ('Entry'), ROOT('Entries')
)
 
-- generate the feed    
SELECT dbo.GenerateAtom10( @fd, @ent )

Function that generates an ATOM 1.0 feed

Let us look at the function that generates an ATOM 1.0 Feed. The code is pretty much the same as what we developed in the previous sessions. The only difference is that, in the previous examples we read the feed information from tables, whereas in this function, we will read information from XML parameters.

CREATE FUNCTION [dbo].[GenerateAtom10]
(
    @fd XML,    -- Feed Information
    @ent XML    -- Entry Information
)
RETURNS XML
AS
BEGIN
    -- This is the variable that will hold the result (ATOM feed)
    DECLARE @atom XML
    -- table variable to store feed information temporarily
    DECLARE @feed TABLE
    (
        title VARCHAR(100),
        subtitle VARCHAR(200),
        id VARCHAR(100),
        link VARCHAR(100),
        generator VARCHAR(20),
        updated    DATETIME 
    )
    -- table variabe to store item information temporarily
    DECLARE @item TABLE 
    (
        title VARCHAR(100),
        link VARCHAR(100),
        published DATETIME,
        updated DATETIME,
        content VARCHAR(1000),
        authorname VARCHAR(30),
        authorurl VARCHAR(100)
    )
    -- load feed information into the table variable
    INSERT INTO @feed (title, subtitle, id, link, generator, updated)
    SELECT
        f.value('title[1]','VARCHAR(100)') AS Title,
        f.value('subtitle[1]','VARCHAR(200)') AS Subtitle,
        f.value('id[1]','VARCHAR(100)') AS ID,
        f.value('link[1]','VARCHAR(100)') AS Link,
        f.value('generator[1]','VARCHAR(20)') AS Generator,
        f.value('updated[1]','DATETIME') AS Updated
    FROM @fd.nodes('/Feed') feed(f)
    -- load item information into the table variable
    INSERT INTO @item (title, link, published, updated, content, authorname, authorurl)
    SELECT
        e.value('title[1]','VARCHAR(100)') AS Title,
        e.value('link[1]','VARCHAR(100)') AS Link,
        e.value('published[1]','DATETIME') AS Published,
        e.value('updated[1]','DATETIME') AS Updated,
        e.value('content[1]','VARCHAR(1000)') AS Content,
        e.value('authorname[1]','VARCHAR(30)') AS AuthorName,
        e.value('authorurl[1]','VARCHAR(100)') AS AuthorURL
    FROM @ent.nodes('/Entries/Entry') entry(e)
         
    -- Let us generate the feed
    ;WITH XMLNAMESPACES(
        DEFAULT 'http://www.w3.org/2005/Atom'
    )
    SELECT @atom = (
        SELECT
            'html' AS 'title/@type',
            title,
            'html' AS 'subtitle/@type',
            subtitle,
            id,
            (
                SELECT
                    'alternate' AS 'link/@rel',
                    'text/html' AS 'link/@type',
                    link AS 'link/@href'
                FROM @feed FOR XML PATH(''), TYPE
            ),
            (
                SELECT
                    'self' AS 'link/@rel',
                    'application/atom+xml' AS 'link/@type',
                    id AS 'link/@href'
                FROM @feed FOR XML PATH(''), TYPE
            ),
            link AS 'generator/@uri',
            '1.0' AS 'generator/@version',
            generator,
            CONVERT(VARCHAR(20),updated,127) + 'Z' AS updated,
            (
                SELECT
                    title,
                    'alternate' AS 'link/@rel',
                    'text/html' AS 'link/@type',
                    link AS 'link/@href',
                    link,
                    link AS 'id',
                    CONVERT(nvarchar,published,127) + 'Z' AS published,
                    CONVERT(nvarchar,updated,127) + 'Z' AS updated,
                    content,
                    authorname AS 'author/name',
                    authorurl AS 'author/uri'
                FROM @item 
                FOR XML PATH('entry'), TYPE
            )
            FROM @feed
        FOR XML PATH('feed'),TYPE
    )
    -- return the feed
  RETURN @atom
END

Invoking The Function

Let us test the function to make sure that it produces a valid ATOM 1.0 Feed. The following example tries to generate a feed using the function we just created.

-- declare the variables
DECLARE @fd XML, @ent XML
 
-- create an XML document with Feed information
SELECT @fd = (
    SELECT 
        'Welcome to XML Workshop' AS title,
        'A collection of articles on SQL Server and XML' AS subtitle,
        'http://...TSQLAtom10.xml' AS id,
        'http://blog.sqlserver.me' AS link,
        'FOR XML' AS generator,
        GETDATE() AS updated
    FOR XML PATH(''), ROOT('Feed')
)
 
-- create an XML document with Entry information    
SELECT @ent = (
    SELECT
       'Sales Order Workshop' AS title,
        'http://.../salesorderworkshop' AS link,
        GETDATE() AS published,
        GETDATE() AS updated,
        'A series of 4 articles ...' AS content,
        'Jacob Sebastian' AS authorname,
        'http://blog.sqlserver.me' AS authorurl
    FOR XML PATH ('Entry'), ROOT('Entries')
)
 
-- generate the feed    
SELECT dbo.GenerateAtom10( @fd, @ent )

The function generates the following output, which is a valid ATOM 1.0 feed. Try validating it with the feed validator of your choice. I validated it with the online feed validator at www.feedvalidator.org and the feed got validated successfully.

<feed xmlns="http://www.w3.org/2005/Atom">
  <title type="html">Welcome to XML Workshop</title>
  <subtitle type="html">
    A collection of articles on SQL Server and XML
  </subtitle>
  <id>http://...TSQLAtom10.xml</id>
  <link xmlns="http://www.w3.org/2005/Atom" 
        rel="alternate" type="text/html" 
        href="http://blog.sqlserver.me" />
  <link xmlns="http://www.w3.org/2005/Atom" 
        rel="self" type="application/atom+xml" 
        href="http://...TSQLAtom10.xml" />
  <generator uri="http://blog.sqlserver.me" 
             version="1.0">FOR XML</generator>
  <updated>2009-02-02T09:03:38.Z</updated>
  <entry xmlns="http://www.w3.org/2005/Atom">
    <title>Sales Order Workshop</title>
    <link rel="alternate" type="text/html" 
          href="http://.../salesorderworkshop">
      http://.../salesorderworkshop
    </link>
    <id>http://.../salesorderworkshop</id>
    <published>2009-02-02T09:03:38.003Z</published>
    <updated>2009-02-02T09:03:38.003Z</updated>
    <content>A series of 4 articles ...</content>
    <author>
      <name>Jacob Sebastian</name>
      <uri>http://blog.sqlserver.me</uri>
    </author>
  </entry>
</feed>

While testing this code, I came across a very strange error. The function started failing with an error that says "An error occurred while executing batch. Error message is: Invalid calling sequence: file stream must be initialized first.". If you see this error while running a FOR XML query, it might be caused by your anti-virus software. It looks like SQL Server tries to create a temp file to hold the XML data temporarily and the Antivirus programme did not like that. I had to disable McAfee protection temporarily to get this error fixed.

A Real Life Example

In the previous session, we saw a Real life example that generates an RSS 2.0 feed based on the data taken from the "pubs" sample database. Let us use the same data and try to generate an ATOM 1.0 feed using the function we created above.

Let us generate an ATOM 1.0 feed with details of the books written by each author. For the purpose of our example, we will take author Green Marjorie. First of all, we need to create two XML variables and store the feed and entry information into them. Then we need to pass those two XML values into the function we created, which in turn will generate an ATOM 1.0 feed.

DECLARE @fd XML, @ent XML
-- Load feed (channel) information 
SELECT @fd = (
    SELECT
        au_lname + ' ' + au_fname + '''s Books' AS title,
        'List of books written by ' + au_lname + ' ' + au_fname AS subtitle,
        'http://blog.sqlserver.me/books/' + au_id AS id,
        'http://blog.sqlserver.me/books/' + au_id AS link,
        'TSQL ATOM Generator by Jacob' AS generator,
        GETDATE() AS updated
    FROM authors WHERE au_id = '213-46-8915'
    FOR XML PATH(''), ROOT('Feed')
)
-- load entry (item) information
SELECT @ent = (
    SELECT
        t.title AS title,
        'http://blog.sqlserver.me/books/mg/' + t.title_id AS link,
        GETDATE() AS published,
        GETDATE() AS updated,
        t.notes AS contend,
        au.au_lname + ' ' + au.au_fname AS authorname,
        'http://blog.sqlserver.me/books/' + au.au_id AS authorurl
    FROM titleauthor ta
    INNER JOIN authors au ON au.au_id = ta.au_id
    INNER JOIN titles t ON
        ta.title_id = t.title_id
        AND ta.au_id = '213-46-8915'
    FOR XML PATH ('Entries'), ROOT('Entry')
)
-- generate ATOM 1.0 feed.
SELECT GenerateAtom10(@fd, @ent)

The above code produces the following XML output.

<feed xmlns="http://www.w3.org/2005/Atom">
  <title type="html">Welcome to XML Workshop</title>
  <subtitle type="html">
    A collection of short articles on SQL Server and XML
  </subtitle>
  <id>http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml</id>
  <link xmlns="http://www.w3.org/2005/Atom" rel="alternate" type="text/html" 
        href="http://blog.sqlserver.me" />
  <link xmlns="http://www.w3.org/2005/Atom" 
        rel="self" type="application/atom+xml" 
        href="http://www.sqlserverandxml.com-a.googlepages.com/TSQLAtom10.xml" />
  <generator uri="http://blog.sqlserver.me" version="1.0">FOR XML</generator>
  <updated>2005-10-14T03:17:00Z</updated>
  <entry xmlns="http://www.w3.org/2005/Atom">
    <title>Sales Order Workshop</title>
    <link rel="alternate" type="text/html" 
          href="http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop">
      http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop
    </link>
    <id>http://www.sqlserverandxml.com-a.googlepages.com/salesorderworkshop</id>
    <published>2005-11-24T00:25:00Z</published>
    <updated>2005-11-24T00:25:00Z</updated>
    <content>A series of 4 articles which explain
      how to pass variable number of parameters to a stored procedure
      using XML</content>
    <author>
      <name>Jacob Sebastian</name>
      <uri>http://blog.sqlserver.me</uri>
    </author>
  </entry>
  <entry xmlns="http://www.w3.org/2005/Atom">
    <title>FOR XML Workshop</title>
    <link rel="alternate" type="text/html" 
          href="http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop">
      http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop
    </link>
    <id>http://www.sqlserverandxml.com-a.googlepages.com/forxmlworkshop</id>
    <published>2005-10-14T02:17:00Z</published>
    <updated>2005-10-14T02:17:00Z</updated>
    <content>A collection of short articles that
      explain how to generate XML output using TSQL keyword FOR
      XML.</content>
    <author>
      <name>Jacob Sebastian</name>
      <uri>http://blog.sqlserver.me</uri>
    </author>
  </entry>
</feed>

The XML document generated by the above function is a valid ATOM 1.0 feed.

The function uses XQuery to retrieve information from the XML variables. XQuery is case sensitive and hence you need to make sure that the elements are named with correct spelling and follows correct casing as given in the list below:

The feed parameter recognizes the following elements.

title
subtitle
id
link
generator
updated

The element names should EXACTLY match with the list given above. The function will ignore any element that does not follow the above naming rules. If you mis-spell a few elements, a feed will still be generated, but it may not be a valid ATOM 1.0 feed (as it would be missing some of the required elements).

The entry parameter recognizes the following elements.

title

link

published

updated

content

authorname

authorurl

Conclusions

In this session, we created a function that generates an ATOM 1.0 feed. The function takes two XML parameters containing the feed and entry information. A feed is then generated based on the information stored in the XML parameters.

About the author

Jacob Sebastian is a SQL Server MVP and blogs regulary at http://blog.sqlserver.me/ on SQL Server and XML related topics. You can find his linkedin profile here.

XML Workshop XXIII - A TSQL ATOM Library

Introduction

Function that generates an ATOM 1.0 feed

Invoking The Function

A Real Life Example

Conclusions

About the author

Rate

Share

Categories

Share

Rate

XML Workshop XXIII - A TSQL ATOM Library

Introduction

Function that generates an ATOM 1.0 feed

Invoking The Function

A Real Life Example

Conclusions

About the author

Rate

Share

Categories

Share

Rate

Related content

XML Workshop XIII - XSD And Variable Content Containers

XML Workshop XII - Parsing a delimited string

XML Workshop XI - Default Namespaces

XML Workshop X - Working with namespaces

XML Workshop IX - Mixed Types