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

XML Workshop XXIII - A TSQL ATOM Library

By Jacob Sebastian,

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.

Total article views: 3843 | Views in the last 30 days: 2
 
Related Articles
FORUM

Updating varchar(max) column

Updating varchar(max) column

FORUM

Barcodes Code128 generator function

Searching for a barcode Code128 generator function

FORUM

Barcode 128 generator (using scalar function)

Function to generate barcodes standard code 128

SCRIPT

Generated script to update statistics

Generated script to update statistics

FORUM

generate insert statements with data

function to generate

Tags
atom    
xml    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones