Two interesting XSLTs:
- XSLT to parse CSV into XML,
- XSLT to transform XML to CSV
–jeroen
Archive for the ‘XSD’ Category
A Generalised and Comprehensive Solution to CSV to XML and XML to CSV Transformations – Pascaliburnus
Posted by jpluimers on 2013/02/21
Posted in CSV, Development, Software Development, XML, XML/XSD, XSD, XSLT | Leave a Comment »
.NET/C#: Generating a WordPress posting categories page – part 2
Posted by jpluimers on 2012/08/22
In Generating a WordPress posting categories page – part 1, you learned how to
- get the HTML with all the category information from your WordPress.com blog,
- convert that to XHTML,
- generate an XSD for the XHTML,
- generate C# classes from that XSD
This episode, you will learn how the data read from the XHTML can be transformed to a simple tree in HTML suited for a posting categories page like mine.
In the final post, the same data will be transferred into a real category cloud with font sizes indicating the frequency of the category usage.
From there, you can go into other directions (for instance generating squarified treemaps from the data).
That’s the cool thing about data: there are many ways to visualize, and this series was meant – next to some groundwork on how to get the data – as inspiration into some forms of visualization.
Hope you had fun reading it!
Getting a HTML tree from the optionType items
StringBuilder outputHtml = new StringBuilder();
string rootUrl = args[1];
foreach (optionType item in select.option)
{
if (item.Level == optionType.RootLevel)
continue;
// <a style="font-size: 100.3986332574%; padding: 1px; margin: 1px;" title="XML/XSD (23)" href="https://wiert.me/category/development/xmlxsd/">XML/XSD</a>
string url = String.Format("{0}/category{1}", rootUrl, item.HtmlPath);
string prefix = new string('.', item.Level * 5);// optionType.NbspEscaped.Repeat(item.Level);
outputHtml.AppendFormat("{0}<a title="{2}" href="{1}">{2} ({3})</a>", prefix, url, item.Category, item.Count);
outputHtml.AppendLine();
}
One way of generating an HTML tree, is to prefix every node with a series of dots corresponding with the level of that node. Not the most pretty sight, but it will suffice for this episode.
Inside each node, I want to show the Category and Count.
Since the optionType as generated from the XSD only contains the below properties, a major portion on this posting is how to decode the Value so we can generate HTML like this:
...............<a href='https://wiert.me/category/development/software-development/net/c-' title='C#'>C# (118)</a> ....................<a href='https://wiert.me/category/development/software-development/net/c-/c--2-0' title='C# 2.0'>C# 2.0 (46)</a> ....................<a href='https://wiert.me/category/development/software-development/net/c-/c--3-0' title='C# 3.0'>C# 3.0 (33)</a> ....................<a href='https://wiert.me/category/development/software-development/net/c-/c--4-0' title='C# 4.0'>C# 4.0 (31)</a> ....................<a href='https://wiert.me/category/development/software-development/net/c-/c--5-0' title='C# 5.0'>C# 5.0 (2)</a>
Decoding the optionType Value property
optionType only contains the these properties:
- class
- the class used to reference the style in the stylesheet
- example value: “level-4”
- value
- internal unique WordPress ID for the category (this allows you to alter the
Slug andValuewithout breaking the links between posts and categories - example value: “45149061”
- internal unique WordPress ID for the category (this allows you to alter the
- Value
- string that WordPress uses to make the category combobox look like a tree structure
- example value: “ C# 5.0 (2)”
The extra properties needed for the HTML generation logic above are these:
- Category
- the
Valueundone from leading non breaking space character escapes, and the trailing count information - example value:
C# 5.0
- the
- Count
- the
Valueundone from leading non breaking space character escapes,Captioninformation, separator non breaking space character escapes, and surrounding parenthesis - example value:
2
- the
- Level
- the
classundone from thelevel-prefix - example value:
4
- the
- Slug
- the category slug is the unique value for a category that WordPress uses to form category urls. It is auto-generated from the Category, but you can also edit it. I don’t, as it is not in the combobox HTML, so I derive it from the Category. Note there are also posting slugs used in the permalink of each post.
- example value:
c--5-0(it consists of lowercase letters and hyphens derived from the Category)
- HtmlPath
- recursive path to include after
/categoryin the categoryurl - example value:
/development/software-development/net/c-/c--5-0that points to https://wiert.me/category/development/software-development/net/c-/c–5-0
- recursive path to include after
- parent (used internally for making the HtmlPath code much easier
The really cool thing about XSD2Code is that it generated the optionType C# code as a partial class.
Which means we can extend the generated partial classes in a seperate C# file like the code fragments below (you can download it from the WordPressCategoriesDropDown.cs file at BeSharp.CodePlex.com)
partial class optionType
{
public const int RootLevel = -1;
private const string slash = "/";
private const char hyphen = '-';
public const string NbspEscaped = " ";
private const string emptyCountInParenthesis = "(-1)";
public optionType parent { get; set; }
public string Category
{
get
{
string result;
string countInParenthesis;
splitValue(out result, out countInParenthesis);
return result;
}
}
public int Count
{
get
{
string category;
string countInParenthesis;
splitValue(out category, out countInParenthesis);
string count = countInParenthesis.Substring(1, countInParenthesis.Length - 2);
int result = int.Parse(count);
return result;
}
}
public int Level
{
get
{
if (string.IsNullOrWhiteSpace(@class))
return RootLevel;
string[] split = @class.Split(hyphen);
string number = split[1];
int result = int.Parse(number);
return result;
}
}
/// <summary>
/// This is the HTML part that WordPress uses to reference a Category
/// </summary>
public string Slug
{
get
{
StringBuilder result = new StringBuilder();
foreach (char item in Category)
{
if (char.IsLetterOrDigit(item))
result.Append(item.ToString().ToLower());
else
if (result.Length > 0)
result.Append(hyphen);
}
return result.ToString();
}
}
public string HtmlPath
{
get
{
if (RootLevel == Level)
return string.Empty;
string result = Slug;
if (null != parent)
result = parent.HtmlPath + slash + result;
return result;
}
}
private void splitValue(out string category, out string countInParenthesis)
{
// might want to do this using RegEx, but that is a write-only language https://wiert.me/category/development/software-development/regex/
string result = Value;
int nbspCountToStripFromLeftOfValue = Level * 3; // strip 3 for each Level
for (int i = 0; i < nbspCountToStripFromLeftOfValue; i++)
{
int nbspEscapedLength = NbspEscaped.Length;
if (result.StartsWith(NbspEscaped))
result = result.Substring(nbspEscapedLength, result.Length - nbspEscapedLength);
}
string doubleNbspEscaped = NbspEscaped + NbspEscaped;
if (result.Contains(doubleNbspEscaped))
{
string[] separators = new string[] { doubleNbspEscaped };
string[] split = result.Split(separators, StringSplitOptions.None);
category = split[0];
countInParenthesis = split[1];
}
else
{
category = result;
countInParenthesis = emptyCountInParenthesis;
}
}
public override string ToString()
{
string result = string.Format("Level {0}, Count {1}, Slug {2}, HtmlPath {3}, Category '{4}'", Level, Count, Slug, HtmlPath, Category);
return result;
}
}
The bulk of the above code is in the splitValue method (that could have used RegEx, but I try to avoid RegEx when I can do without it).
Note that the HtmlPath propererty uses the parent property. Without it, the HtmlPath code would have been very complex. The value of the parent properties for all optionType instances is generated in the selectType.FixParents method below since the selectType instance contains all the optionType instances in its’ option property.
partial class selectType
{
public void FixParents()
{
Stack<optionType> itemStack = new Stack<optionType>();
optionType parent = null;
int previousLevel = optionType.RootLevel;
foreach (optionType item in option)
{
int itemLevel = item.Level;
if (itemLevel == previousLevel)
{
if (optionType.RootLevel != itemLevel)
{
itemStack.Pop();
item.parent = parent;
}
itemStack.Push(item);
}
else
{
if (itemLevel > previousLevel)
{
parent = itemStack.Peek();
}
else
{
do
{
itemStack.Pop();
parent = itemStack.Peek();
previousLevel = parent.Level;
}
while (previousLevel >= itemLevel);
}
itemStack.Push(item);
item.parent = parent;
previousLevel = itemLevel;
}
}
}
}
–jeroen
Share this:
Posted in .NET, C#, C# 4.0, C# 5.0, Development, LINQ, Software Development, Usability, User Experience (ux), Web Development, WordPress, WordPress, XML, XML escapes, XML/XSD, XSD | 4 Comments »
.NET/C#: Generating a WordPress posting categories page – part 1
Posted by jpluimers on 2012/07/31
From the category cloud it is hard to see that the categories are organized as a hierarchy. The combobox on the right shows that, but does not have room to properly show the hierarchy. Since WordPress.com does not allow you to deploy your own code, I worked around it in this way using a small .NET C# console program:
- Extract the HTML for the All Categories combobox on the right of the page.
- Convert that HTML to XHTML (and therefore XML)
- Generate XSD from that XML
- Generate C# class wrappers from the XSD
Future posts will show more logic on how to handle the imported information, and generate nice category overviews. Preliminary source code is at the BeSharp.net source repository.
Extract the HTML
The HTML is not fully accurate (see my post on HTML and XML escapes from last week), but it is fairly easy to extract. Most web browsers allow you to view the source of your web page. Do that, then search for “All Categories”. Now you see HTML like this:
</pre> <h2 class="widgettitle">All categories</h2> <pre><select class="postform" name="cat"><option value="-1">Select Category</option></select><select class="postform" name="cat"><option class="level-0" value="256">About (66)</option></select><select class="postform" name="cat"><option class="level-1" value="64"> Personal (60)</option></select><select class="postform" name="cat"><option class="level-2" value="20254983"> Adest Musica (7)</option></select><select class="postform" name="cat"><option class="level-2" value="32122"> Certifications (2)</option></select><select class="postform" name="cat">...</select><select class="postform" name="cat"><option class="level-0" value="756">Comics (3)</option></select><select class="postform" name="cat"><option class="level-0" value="780">Development (473)</option></select><select class="postform" name="cat"><option class="level-1" value="872460"> Database Development (55)</option></select><select class="postform" name="cat">...</select><select class="postform" name="cat"><option class="level-0" value="9280">User Experience (3)</option></select>
I don’t need the H2 heading line, but the rest I do need to generate XML from. I saved the HTML into a text file for processing by the console app.
Convert the HTML to XML
The HTML contains loads of , but XML does not allow for that entity. So the & ampersand needs to be escaped into &This also solves other uses of & in the HTML. The rest of the HTML is XHTML compliant, so does not require change, which results into this C# conversion method:
private static string toXml(string inputHtml)
{
string result = inputHtml.Replace("&", "&");
return result;
}
Generate an XSD for the XML, then amend the XSD
Given my comparison of tools for generating XSD from XML, so I used the XmlForAsp XML Schema generator, with the “Separate Complex Types” option.
(Note: I will link to the XSD before/after, as WordPress – yet again – screws the XSD sourcecode in the post; this should do for now). That gives me XSD like this (XML is also at pastebin):
<?xml version="1.0" encoding="utf-8"?> <xsd:schema attributeFormDefault="unqualified" elementFormDefault="qualified" version="1.0" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <xsd:element name="select" type="selectType" /> <xsd:complexType name="selectType"> <xsd:sequence> <xsd:element maxOccurs="unbounded" name="option" type="optionType" /> </xsd:sequence> <xsd:attribute name="name" type="xsd:string" /> <xsd:attribute name="id" type="xsd:string" /> <xsd:attribute name="class" type="xsd:string" /> </xsd:complexType> <xsd:complexType name="optionType"> <xsd:attribute name="value" type="xsd:int" /> </xsd:complexType> </xsd:schema>
Which is not complete, but gives a good start. The actual XSD it needs to be like this with a more elaborate optionType complex type that also defines it’s own content as deriving from xsd:string, and adds the class attribute (XML is also at pastebin):
<?xml version="1.0" encoding="utf-8"?> <xsd:schema attributeFormDefault="unqualified" elementFormDefault="qualified" version="1.0" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <xsd:element name="select" type="selectType" /> <xsd:complexType name="selectType"> <xsd:sequence> <xsd:element maxOccurs="unbounded" name="option" type="optionType" /> </xsd:sequence> <xsd:attribute name="name" type="xsd:string" /> <xsd:attribute name="id" type="xsd:string" /> <xsd:attribute name="class" type="xsd:string" /> </xsd:complexType> <xsd:complexType name="optionType"> <xsd:simpleContent> <xsd:extension base="xsd:string"> <xsd:attribute name="class" type="xsd:string" /> <xsd:attribute name="value" type="xsd:int" /> </xsd:extension> </xsd:simpleContent> </xsd:complexType> </xsd:schema>
Generate C# classes from the XSD
You can generate C# wrapper classes using the XSD.exe tool that ships with Visual Studio, but XSD.exe is hard to use, is hard to integrate into Visual Studio (despite Microsoft Connect request for it), the XSD.exe generated code still needs work for deserializing, and XSD.exe has very limited generation options (heck, after it changed from .NET 1.x to 2.0, it hasn’t been updated for about a decade). XSD2Code has some great reviews, to I used that in stead. And indeed, very well integrates into Visual Studio 2010, and generates very nice C#, especially when you use the options (see also the screenshot on the right):
- Under Serialization, set Enabled to True
- Under Serialization, set GenerateXmlAttributes to True
That way, loading the HTML, converting it to XML, then deserializing it into object instances is as simple as this:
string inputFileName = args[0];
string inputHtml = getHtml(inputFileName);
string xml = toXml(inputHtml);
selectType select = selectType.Deserialize(xml);
More on actually working with the loaded instances in the next episode, including the great benefit of XSD2Code: it generates C# code as partial classes.
–jeroen
Share this:
Posted in .NET, C#, C# 4.0, C# 5.0, Development, SocialMedia, Software Development, Usability, User Experience (ux), Web Development, WordPress, WordPress, XML, XML escapes, XML/XSD, XSD | 2 Comments »
Excel XML Spreadsheet: going the XSLT way
Posted by jpluimers on 2011/09/01
Because of Excel XML Spreadsheet: Date.Type is mandatory :), I researched a bit more about how people use the XML Spreadsheet format.
I found that Technobabble is using XSL to generate Excel XML Spreadsheets.
Be patient following the above link; it takes a while to completely load, and while loading the style sheet shows black text on a dark grey background :) When loaded completely, the background becomes white.
The article talks more about styles and formulas (XML Spreadsheets store formulas in relative R1C1 style notation which is far easier to parse than the absolute A1 style notation, which most people use).
It seems to imply that for a ss:Column, a ss:AutoFitWidth of 1 (True) combined with a specified ss:Width will autosize text values, but the ss:Column documentation is right: “We do not autofit textual values”.
The default Excel 2003 font has a Width of about 7 points per character, so you can use that as a base to calculate ss:Width values for a column.
–jeroen
Share this:
Posted in Development, Software Development, XML, XML/XSD, XSD | Leave a Comment »
Excel XML Spreadsheet: Styles and formatting
Posted by jpluimers on 2011/08/25
I found some time to continue my series that started with Excel XML Spreadsheet: Date.Type is mandatory :)
This time, it is about Styles and using the styles to format. I’ll limit myself to formatting Columns, but you can equally apply this to individual Cells, Rows, and Tables.
Note that in the below XML listings, I have replaced the angle brackets with { and }, because the WordPress editor will otherwise delete the XML from the sourcecode portions.
First, let’s look at some ss:Styles:
{Styles}
{Style ss:ID="Default" ss:Name="Normal"}
{Alignment ss:Vertical="Bottom"/}
{/Style}
{Style ss:ID="s21"}
{NumberFormat ss:Format="yyyy/mm/dd"/}
{/Style}
{Style ss:ID="s22"}
{NumberFormat ss:Format="yyyy/mm/dd\ h:mm:ss"/}
{/Style}
{Style ss:ID="s31"}
{NumberFormat ss:Format="[ENG][$-409]ddd"/}
{/Style}
{Style ss:ID="s32"}
{NumberFormat ss:Format="[$-F800]dddd\,\ mmmm\ dd\,\ yyyy"/}
{/Style}
{/Styles}
Then the usage of the styles in Columns:
{Column ss:StyleID="s21" ss:Width="53.25"/}
{Column ss:Index="4" ss:StyleID="s31" ss:Width="89.25"/}
{Column ss:StyleID="s22" ss:Width="95.25"/}
{Column ss:StyleID="s32" ss:Width="95.25"/}
First a few remarks about the ss:Styles:
- Styles have IDs, which don’t need to be in the form s##, you can use any unique ID for them. Excel uses s## because that’s how the formatting pick-list works.
- You specify the formatting as a ss:NumberFormat using the components from the Creating international number formats documentation.
- You can add an Excel specific LCID (locale identifier) to a format. Without it, it will use the user’s locale settings.
- You can ommit the language hint (like [ENG]) from the formatting.
- The Excel LCID is very similar to the LCID Structure using hexadecimal values from the (old now defunct Locale ID Chart and replaced by the new) Microsoft Locale ID Values, Language Identifier Constants and Strings table or list of Locale IDs Assigned by Microsoft, but with a few twists.
- There is a lot of confusion about [$-F800] and [$-F400] which actually behaves as LANG_SYSTEM_DEFAULT (0x0800 in the latter table), where [$-F800] displays the long date and [$-F400] displays the time (as correctly identified in this Openoffice Bugzilla bug report – or the Google cache of it).
- Three digit language [$-409] should be extended to 4-digit LCID 0x0409. It will format the cell using that specific language (in this case: English 3-letter weekday abbreviation).
A few remarks about the ss:Columns:
- These columns define formatting for column A, D, E and F.
- You don’t need to have a definition for every column in your Worksheet.Table, just for the ones that need formatting.
- The Column definition is smart: it can be sparse! After each gap, define a column having an ss:Index attribute, then continue defining subsequent columns until you need another gap.
- You can ommit the ss:Width attribute: when empty, the column will auto-size
–jeroen
PS: Rob van Gelder posted a nice formula to show nice translations using Excel formatting.
Share this:
Posted in Development, internatiolanization (i18n) and localization (l10), Software Development, XML, XML/XSD, XSD | Leave a Comment »
Excel XML Spreadsheet: watch your Worksheet.Table.ExpandedColumnCount (or just leave it out)
Posted by jpluimers on 2011/08/11
Recently, I blogged about the Excel XML Spreadsheet: Date.Type is mandatory.
Here is another error you can get:
XML ERROR in Worksheet Setting REASON: Bad Value FILE: V:\CAS400\docs\statistics.cascall-logs\cascall.JU03HV.Bobbe, P.J.L. (Paul).20110620.xml GROUP: Worksheet TAG: Table ATTRIB: ExpandedColumnCount VALUE: 9
This basically means your Worksheet.Table.ExpandedColumnCount is too small to hold your spreadsheet.
In other words: you either have more Worksheet.Table.Column definitions than ExpandedColumnCount, or you have a WorkSheet.Table.Row that has more WorkSheet.Table.Row.Cell entries than the ExpandedColumnCount.
In my case, I made an error generating too many Column definitions (by accidentaly duplicating some of them), and invalidated the ExpandedColumnCount.
Lesson learned: Since Excel will automatically calculate an absent ExpandedColumnCount, you can safely leave it out.
–jeroen
Share this:
Posted in Development, Software Development, XML, XML/XSD, XSD | Leave a Comment »
Excel XML Spreadsheet: Date.Type is mandatory :)
Posted by jpluimers on 2011/07/28
When you generate Excel XML Spreadsheets, and you load it in Excel, it will report some errors in your Temporary Internet Files directory.
For instance, when you forget to fill the Data.Type attribute, then you get a log file with a message like this:
XML ERROR in Table REASON: Missing Tag FILE: V:\export.xml GROUP: Cell TAG: Data ATTRIB: Type
Note that some errors won’t be reported. For instance if you forget to put your Data in a Cell.Data (and put it in Cell); then Excel just show empty worksheet.
There seems to be no XSD for the XML Spreadsheet format, so you have to get yourself familiar with the XML in Excel and the Spreadsheet Component documentation on MSDN.
You can view where to put your stuff in the XML Spreadsheet Tag Hierarchy. That list doesn’t have individual links to the tags, you need the XML Spreadsheet Reference for that.
To make things more practical for myself, I perfomed these steps:
- created a spreadsheet with all of the data variations I expected
- saved this spreadsheet as XML
- generated the XSD from that XML
- imported the XSD to generate wrapper classes and interfaces
Even with that, you will need to accommodate for many peculiarities.
Hopefully I will find some time to write more those down soon.
The first is ss:Data:
- It contains the actual data of a cell
- It has a required ss:Type attribute, which is an enumeration of Number, DateTime, Boolean, String, and Error.
- When it is String, then x:Ticked should be 1 (meaning True) when the string can be parsed as non-String type (and you would type the value into excel starting with a Tick mark (‘ aka single quote or apostrophe)
–jeroen
Share this:
Posted in .NET, Development, Software Development, XML, XML/XSD, XSD | 3 Comments »
Formatted sourcecode in WordPress: uses SyntaxHighlighter 3.0; complete list of supported languages
Posted by jpluimers on 2011/01/18
In the past I wrote a few blog posts on posting sourcecode in WordPress.
Nick Hodges‘ last Flotsam and Jetsam blog post pointed me to the SyntaxHighlighter JavaScript that is used by WordPress and many other engines/sites.
Their site contains an even more elaborate list of supported languages.
I had the basic list right in my last post, but was missing all the aliases (which often are easier than the longer proper names).
This is the new table adapted from their list: Read the rest of this entry »
Share this:
Posted in .NET, Batch-Files, C#, CSS, Database Development, Delphi, Delphi for PHP, Development, HTML, HTML5, Java, PowerShell, RegEx, Scripting, SQL, VBS, Web Development, WordPress, XML, XML/XSD, XSD | 5 Comments »
Generate XSD from XML – XSD.EXE versus on-line tools
Posted by jpluimers on 2010/11/23
Quite a while ago, I wrote about the XSD.EXE tool to generate wrapper classes from an XSD file.
Recently, I had to create an XSD based on some XML.
Actually: a client was implementing a tool, that could export some of the data as XML.
That XML had to go into their database.
But the tool vendor told the client that the underlying XSD was ‘not supported’ (odd: why allow exporting XML and then not provide something supporting as the XSD?).
Anyway, the data was not that difficult, but having an XSD at hand made the import process a lot easier.
So lets see how to get a starting XSD from an of XML files (in practice, you would do this with a couple of XML files, then collect the best pieces into your final XSD). Read the rest of this entry »
Share this:
Posted in .NET, Delphi, Development, Software Development, XML, XML/XSD, XSD | 11 Comments »
Some ASX information in order to create a partical XSD schema of it
Posted by jpluimers on 2010/11/03
I tried searching for an XSD or other schema that describes ASX (Advanced Stream Redirector) files, but somehow could not find them.
The ASX files can be used to generate a playlist, but they are a bit richer in content than the WPL (Windows Media Player Playlist) file format.
So here is a post with some references to ASX files, examples, documentation and an XSD you could use for ASX files, and why ASX is not valid XML, it is just well-formed. Read the rest of this entry »
Share this:
Posted in .NET, C#, Delphi, Development, Software Development, XML, XML/XSD, XSD | 2 Comments »
The Wiert Corner - irregular stream of stuff 