If you feel more comfortable handling XML conversions than connecting directly to a database, you can instead get data about reviews using the command ccollab admin review-xml. This command has one required argument, the review ID. By default, it will output the entire XML document for the review. However, there are optional arguments of --xpath and --xsl-file which allow you to query the resulting document for particular information.
XPath
XPath allows you to address XML elements in an XML document much like paths in a file system. The hierarchy in an XPath expression tells the XPath parser where in the XML document to find elements.
For example, to get the section of the review-xml document that contains participant custom fields, you would use an XPath expression like this:
/reviews/review/participant-custom-fields
Below is an example command invocation and its response:
$ ccollab admin review-xml 3919 --xpath /reviews/review/participant-custom-fields
<participant-custom-fields>
<user userId="70">
<outside-time metaDataId="198" title="Outside Time">27</outside-time>
<affected-components metaDataId="199" title="Affected Components">APIs
Business Logic
Database Back-end</affected-components>
<rating metaDataId="197" title="Rating">5</rating>
</user>
<user userId="71">
<outside-time metaDataId="198" title="Outside Time">45</outside-time>
<affected-components metaDataId="199" title="Affected Components"/>
<rating metaDataId="197" title="Rating">2</rating>
</user>
<user userId="72">
<outside-time metaDataId="198" title="Outside Time">0</outside-time>
<affected-components metaDataId="199" title="Affected Components"/>
<rating metaDataId="197" title="Rating"/>
</user>
</participant-custom-fields>
Finding Particular Values
To get all values of a particular type, you will need to specify a few more path elements. Note in the sample above that all of the Participant Custom Fields are grouped by the user that specified them. Within that user tag, each of the fields gets a unique XML tag which is a normalized form of the title of the custom field title. The value for that field is stored as text inside of that field's tag. So, to get the values for the Rating field, you would use the following XPath expression:
/reviews/review/participant-custom-fields/user/rating/text()
Below is an example invocation, and its output:
ccollab admin review-xml 3919 --xpath "/reviews/review/participant-custm-fields/user/rating/text()"
5
2
Note that we had to quote the XPath expression because some command-line shells interpret parentheses as non-literal characters.
XSLT
XSLT (Extensible Stylesheet Language Transformation) is a subset of XSL which can be used to transform an XML document into another format. You can use XPath expressions along with XSLT to perform more complicated data queries. For example, if we want to show all "rating" values and the user ID of the user who set them, we can use a file like this:
File: sample.xslt
<?xml version="1.0" encoding="iso-8859-1"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text"/>
<xsl:template match="/">
<!-- Output CSV of Participant Custom Fields -->
<!-- Header: -->
<xsl:text>UserID</xsl:text>
<xsl:text>, </xsl:text>
<xsl:text>Field Name</xsl:text>
<xsl:text>, </xsl:text>
<xsl:text>Field Value</xsl:text>
<xsl:text> </xsl:text><!-- linefeed -->
<xsl:for-each select="/reviews/review/participant-custom-fields/user">
<xsl:value-of select="@userId"/>
<xsl:text>, </xsl:text>
<xsl:value-of select="rating/@title"/>
<xsl:text>, </xsl:text>
<xsl:value-of select="rating/text()"/>
<xsl:text> </xsl:text>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
Here is how you would use the above XSL file, and some sample results:
$ ccollab admin review-xml 3919 --xsl-file sample.xslt
UserID, Field Name, Field Value
70, Rating, 5
71, Rating, 2
72, Rating,
Note that user #72 has not specified a value for Rating, so that value is empty.