Umbraco CMS: How to Import RSS (and other) data

Written by Mark Burnham

Importing data into Umbraco

It's really easy to import data of all sorts into Umbraco. First of all, to import RSS feeds, you should read Kaspar's article on importing Flickr images:

http://kasperb.dk/2006/6/26/display-your-flickr-images-on-your-umbraco-driven-website.aspx

When I first read this, a number of lights went on, and I realized it was going to be easy to import any other kind of data into Umbraco. Basically, you import whatever data you want as XML,

and transform it exactly like Kasper does, with XSLT.

There are other methods. You can write a library as is described here:

http://blockquote.be/2006/08/09/extending-umbraco-with-a-library/

For me, it's easier and more flexible to output the data with a .NET Web Form and use umbraco.library:GetXmlDocumentByUrl to import it into your XSLT.

If your data source is SQL Server, you can use the "FOR XML" clause in your queries. You should definitely read up on this, because it will save you a bunch of time. Get the basics here:

http://www.sqljunkies.ddj.com/Article/296D1B56-8BDD-4236-808F-E62CC1908C4E.scuk

I'll give you a quick example of importing data from an AdventureWorks database (download the source from here) :

1) create a new Web Form in Visual Studio. Call it ourData.aspx. To make things simple, we'll do all the work in the Page_Load function:

Hide Code [-]


    protected void Page_Load(object sender, EventArgs e)
    {

        string sqlStr = "select top 30 Title, FirstName,LastName,EmailAddress FROM Person.Contact For XML RAW('Person.Contact'),ROOT,ELEMENTS";

        string connStr = "Server=(local);Database=AdventureWorks; User ID=umbracoUser;Password=pass;";


        using (SqlConnection cn = new SqlConnection(connStr))

        using (SqlCommand cmd = new SqlCommand(sqlStr, cn))
        {
            cmd.Connection.Open();
            using (XmlReader xr = cmd.ExecuteXmlReader())
            {
                xr.Read();

                while (xr.ReadState != System.Xml.ReadState.EndOfFile)
                    Response.Write(xr.ReadOuterXml());
            }
        }
    }

Make sure to change the database connection string to whatever you need for you installation. You should be able to load this page in a browser and see a nice dose of XML. If you can't see XML, there's something wrong. NOTE: When you get more complicated data imports, you can always see the XML by loading your "data import page".

2) Copy this Web Form to the /umbraco directory of your installation.

3) Now, in Umbraco, create a new XSLT file and macro that contains the following code:

Hide Code [-]


<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE xsl:Stylesheet [ <!ENTITY nbsp "&#x00A0;"> ]>
<xsl:stylesheet
  version="1.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:msxml="urn:schemas-microsoft-com:xslt"
  xmlns:umbraco.library="urn:umbraco.library"
  exclude-result-prefixes="msxml umbraco.library">


<xsl:output method="xml" omit-xml-declaration="yes"/>

<xsl:variable name="url" select="'http://localhost/umbraco/ourData.aspx'"/>

<xsl:template match="/">

<xsl:variable name="pagexml" select="umbraco.library:GetXmlDocumentByUrl($url)" />

<!--<xsl:copy-of select="$pagexml"/>-->
<ul>
<xsl:for-each select="$pagexml/root/Person.Contact">
<li><xsl:value-of select="Title"/>&nbsp;<xsl:value-of select="FirstName"/>&nbsp;<xsl:value-of select="LastName"/></li>
</xsl:for-each>
</ul>
</xsl:template>

</xsl:stylesheet>