Archive for category XML

Copying XML data from a SQL query

We have an older system which stores XML as a varchar in the database. Copying & pasting from this often truncates the content, and if it doesn;t you then have to paste it into visual studio or something to get the correct formatting etc etc…. it’s a pain.

A quick way round this- if you’re using SQL server 2005 or above- is to cast the output as xml- this makes the xml like a clickable hyperlink in the results panel, and clicking it opens up the full unabridged version of the xml, nicly formatted and indented as god intended.

SELECT TOP 100 [ID]
      ,cast(replace(request,'encoding="UTF-8"','encoding="UTF-16"') as xml)
      ,cast(replace(response,'encoding="UTF-8"','encoding="UTF-16"') as xml)
      ,[UserID]
      ,[RequestDate]
  FROM [MyXMLDataLogTable]
  order by id desc

Thanks go once more to my good colleague Andrew Carmichael for this top tip.

1 Comment

Paged List of Child Nodes in Umbraco

We just started looking at Umbraco- and so I’m delving into Macro’s – these are the packaged up chunks of functionality which make your site actually do things and either take the form of XSLT files which process and spit out the contents of your database, or dot net controls.

I found an excellent example XSLT that allows you to create a paginated list of child nodes over on Tim Geyssens “nibble” blog, and I’ve modified it ever so slightly to stop the numerical index rendering if the pagecount is less than 2- so thought I would re-post it!


<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE xsl:stylesheet [
  <!ENTITY nbsp "&amp;amp;amp;#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:param name="currentPage"/>
  <xsl:template match="/">

    <xsl:variable name="recordsPerPage" select="/macro/recordsPerPage"/>
    <xsl:variable name="pageNumber" >
      <xsl:choose>
        <!-- first page -->
        <xsl:when test="umbraco.library:RequestQueryString('page') <= 0 or string(umbraco.library:RequestQueryString('page')) = '' or string(umbraco.library:RequestQueryString('page')) = 'NaN'">0</xsl:when>
        <!-- what was passed in -->
        <xsl:otherwise>
          <xsl:value-of select="umbraco.library:RequestQueryString('page')"/>
        </xsl:otherwise>
      </xsl:choose>

    </xsl:variable> &amp;amp;amp;nbsp;

    <xsl:variable name="numberOfRecords" select="count($currentPage/node)"/>

    <!-- The fun starts here -->
    <ul>
      <xsl:for-each select="$currentPage/node [string(data [@alias='umbracoNaviHide']) != '1']">
        <xsl:if test="position() > $recordsPerPage * number($pageNumber) and
position() <= number($recordsPerPage * number($pageNumber) +
$recordsPerPage )">
          <li>
            <a href="{umbraco.library:NiceUrl(@id)}">
              <xsl:value-of select="@nodeName"/>

            </a>
          </li>
        </xsl:if>
      </xsl:for-each>
    </ul>

    <xsl:if test="$pageNumber > 0">
      <a href="?page={$pageNumber -1}">previous </a>
    </xsl:if>

<xsl:if test="($numberOfRecords > $recordsPerPage)">
    <xsl:call-template name="for.loop">
      <xsl:with-param name="i">1</xsl:with-param>
      <xsl:with-param name="page" select="$pageNumber +1"></xsl:with-param>
      <xsl:with-param name="count" select="ceiling(count($currentPage/node)div $recordsPerPage)"></xsl:with-param>
    </xsl:call-template>
</xsl:if>

    <xsl:if test="(($pageNumber +1 ) * $recordsPerPage) < ($numberOfRecords)">
      <a href="?page={$pageNumber +1}">next</a>
    </xsl:if>
  </xsl:template>

  <xsl:template name="for.loop">

    <xsl:param name="i"/>
    <xsl:param name="count"/>
    <xsl:param name="page"/>
    <xsl:if test="$i <= $count - 1">

      <xsl:if test="$page != $i">
        <a href="{umbraco.library:NiceUrl($currentPage/@id)}?page={$i - 1}" >

          <xsl:value-of select="$i" />
        </a>
      </xsl:if>

      <xsl:if test="$page = $i">

        <xsl:value-of select="$i" />

      </xsl:if>
    </xsl:if>
    <xsl:if test="$i <= $count - 1">
      <xsl:call-template name="for.loop">
        <xsl:with-param name="i">
          <xsl:value-of select="$i + 1"/>
        </xsl:with-param>
        <xsl:with-param name="count">
          <xsl:value-of select="$count"/>
        </xsl:with-param>
        <xsl:with-param name="page">
          <xsl:value-of select="$page"/>
        </xsl:with-param>
      </xsl:call-template>
    </xsl:if>
  </xsl:template>
</xsl:stylesheet>

2 Comments

Syndicating to RSS using the built in .net SyndicationFeed classes

My boss recently showed me a pretty handy bunch of classes for generating RSS and ATOM feeds, all built into dot net.

Start by adding a new reference to your project; System.Services.Web.

The SyndicationFeed class holds all the data about your feed and lets you spit everything out in a number of different formats (RSS2.0, ATOM1.0)- each item in the feed is held in a generic list of SyndicationItem objects. As I work for a publishers, I was creating an RSS feed of coming soon books so I added an extension method to my book class- ToSyndicationItem();

public static SyndicationItem ToSyndicationItem(this Book book)
{
    return new SyndicationItem(book.CoverTitle + " " + book.Subtitle, book.Description, new Uri(book.GetURL()))
    {
        Summary = new TextSyndicationContent(book.DescriptionShort, TextSyndicationContentKind.Plaintext),
        Id = book.Id.ToString(),
        LastUpdatedTime = book.InsertedDate
    };
}

This allows me to use all the standard select methods I already in my book class, to also populate my RSS feed. So to create the feed all I need do in my RSS.ashx handler file is;

List<Book> books = Book.GetBookPublishedBetween(DateTime.Now,DateTime.Now.AddDays( int.Parse( GroupConstants.ComingSoonMaxDays)));
List<SyndicationItem> syndicationItems = new List<SyndicationItem>();
foreach (Book book in books)
    syndicationItems.Add(book.ToSyndicationItem());

feed = new SyndicationFeed(syndicationItems)
{
    Title = new TextSyndicationContent("Coming Soon Titles"),
    Description = new TextSyndicationContent("Forthcoming publications."),
    BaseUri = new Uri(LinkHelper.GetBaseUrl())
};

var output = new StringWriter();
var writer = new XmlTextWriter(output);

new Rss20FeedFormatter(feed).WriteTo(writer);

context.Response.ContentType = "application/rss+xml";
context.Response.Write(output.ToString());

, , ,

1 Comment

RSS XSLT

Simple piece of XSL to format an RSS Feed;

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:output method="html"></xsl:output>
    <xsl:template match="rss/channel/item">
    <li>
      <a href="{link}" title="{title}">
        <xsl:value-of select="title" />
      </a>
      <p>
        <xsl:value-of select="description" />
      </p>
    </li>
  </xsl:template>
</xsl:stylesheet>

No Comments

Consuming RSS Feeds using ASP.net controls

Real simple example, using an XmlDataSource and my new super best friend, the ListView control;

    <asp:ListView ID="RSSList" runat="server" DataSourceID="RSSData">
        <LayoutTemplate>
            <ul>
                <li id="itemPlaceholder" runat="server" />
            </ul>
        </LayoutTemplate>
        <ItemTemplate>
            <li><h2>
                <%#XPath("title") %>
            </h2>
            
            <%#XPath("author") %>
            <a href='<%#XPath("link") %>' title=''>View Original Post</a>
            </li>
        </ItemTemplate>
    </asp:ListView>
    <asp:XmlDataSource 
        ID="RSSData" 
        runat="server" 
        DataFile="http://www.shawson.co.uk/codeblog/feed/"
        XPath="rss/channel/item">
    </asp:XmlDataSource>

No Comments

Visually build XPath using “Sketch Path”

Paul found an awesome tool fopr visually parsing XML files and building XPath statements, called Sketch Path available for download here

No Comments

Quick XML serialisation of an object..

Quick code snippet for taking a serialisable object, serialising to XMl then spitting out a string for you to write out to a debug log or something- i used this for spitting back the responses I was getting from a web service call i was making

            StringBuilder sb_xml = new StringBuilder();
            XmlSerializer s = new XmlSerializer( typeof( Hachette.Checkout.Vista.Stock.ProductStockLiteResultResponse ) );
            StringWriter w = new StringWriter(sb_xml);
            s.Serialize(w, ws_response);
            w.Close();
            Response.Write("<!-- Response = " + sb_xml + " -->");

No Comments