XML Template Query

Question

Post reply

XML Template Query

tibby

SSC Veteran

Points: 207
More actions
May 13, 2004 at 9:58 am

#85336

I have a small issue, here's how my code would normally go:

Select ColumnA, Count(ColumnB) as ColB From MyTable Where ColumnA='Something' Group by ColumnA Sort By ColumnA

The two problems I'm having is one, I get an error that says the group by isn't supported, and two, Something is a dynamic value.

How would I transfer this to a XML Template?

Thanks,
tibby

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

wz700 SSCertifiable Points: 7133 More actions · Answer 1

select * from

(Select ColumnA, Count(ColumnB) as ColB From MyTable Where ColumnA='Something' Group by ColumnA Sort By ColumnA) a

Antares686 SSC Guru Points: 125444 More actions · Answer 2

Here is an example I setup with northwind

file x.xml as defined

<?xml version ='1.0'?>

<sql:header>

<sql:param name="CustomerID">ANATR</sql:param>

</sql:header>

<sql:query>

SELECT * FROM (Select TOP 100 PERCENT CustomerID, Count(OrderID) OrderCount

From dbo.Orders

Where CustomerID = @CustomerID Group by CustomerID Order By CustomerID) Orders

FOR XML AUTO, ELEMENTS

</sql:query>

</document>

file x.xsl as defined

<?xml version='1.0'?>

<xsl:stylesheet xmlns:xsl='http://www.w3.org/1999/XSL/Transform' version='1.0'>

<xsl:template match='Orders'>

<tr>

<td>

<xsl:value-of select='CustomerID'/>

</td>

<td>

<xsl:value-of select='OrderCount'/>

</td>

</tr>

</xsl:template>

<xsl:template match='/'>

<html>

<head>

</head>

<style>

TH {

background-Color:#0000bb;

color:#ff0000;

font-weight:bolder

}

TD {

background-Color:#ffffff

}

</style>

<tr>

<th>Customer ID</th>

<th>Order Count</th>

</tr>

<xsl:apply-templates/>

</table>

</body>

</html>

</xsl:template>

</xsl:stylesheet>

Set up my template directory and did the following

http://address/path/templatedir/x.xml?contenttype=text/html&CustomerID=VINET

tibby SSC Veteran Points: 207 More actions · Answer 3

Just shooting off the hip and looking at your post, how did you get the Group By clause to work? I'm being told it is not supported in SQLXML...

Thank You,

tibby

Antares686 SSC Guru Points: 125444 More actions · Answer 4

If you will look at my query you will notice it is actually a subquery for the GROUP BY expression which is treated like a view to the top query.

By doing this it assumes my query was select * from (some table or view named ORDERS) FOR XML ... so it doesn't see the group by being done as FOR XML.

SELECT * FROM (Select TOP 100 PERCENT CustomerID, Count(OrderID) OrderCount

From dbo.Orders

Where CustomerID = @CustomerID Group by CustomerID Order By CustomerID) Orders

FOR XML AUTO, ELEMENTS