I trying to find out to sort some data, i have this. But like to sort it by "firmaUrl", is it possible?
<xsl:variable name="sqlexp">
<![CDATA[
SELECT CAST(x.xml AS XML).query('/node/data[@alias="firmaLogo"]') AS kundeLogo, CAST(x.xml AS XML).query('/node/data[@alias="firmaStatement"]') AS kundeStatement, CAST(x.xml AS XML).query('/node/data[@alias="firmaUrl"]') AS kundeUrl
FROM cmsContentXml AS x
INNER JOIN cmsMember AS m ON x.nodeId = m.nodeId
WHERE CAST(x.xml AS XML).value('(/node/data[@alias="erAktiv"])[1]', 'int') = 1
I'd recommend moving that out into a XSLT Helper. Writing a class library to return an XPathNodeIterator. At least I think that's the way to go it's been a while since I returned a nodeset to XSLT. I think TranctSQL can return XML also.
A sample of how it could be done in an extension method would be:
public static XPathNodeIterator GetAllMembers()
{
IEnumerable<Member> members = Member.GetAllAsList();
var builder = new StringBuilder();
builder.Append("<members>");
foreach (Member member in members)
{
builder.Append(member.ToXml(new XmlDocument(), false).OuterXml);
}
builder.Append("</members>");
var retDoc = new XmlDocument();
retDoc.LoadXml(builder.ToString());
return retDoc.CreateNavigator().Select("/");
}
Be ware, that it is resource/db intensive if you have a lot of members.
CDATA and order by
Hi guys,
I trying to find out to sort some data, i have this. But like to sort it by "firmaUrl", is it possible?
<xsl:variable name="sqlexp">
<![CDATA[
SELECT CAST(x.xml AS XML).query('/node/data[@alias="firmaLogo"]') AS kundeLogo, CAST(x.xml AS XML).query('/node/data[@alias="firmaStatement"]') AS kundeStatement, CAST(x.xml AS XML).query('/node/data[@alias="firmaUrl"]') AS kundeUrl
FROM cmsContentXml AS x
INNER JOIN cmsMember AS m ON x.nodeId = m.nodeId
WHERE CAST(x.xml AS XML).value('(/node/data[@alias="erAktiv"])[1]', 'int') = 1
]]>
</xsl:variable>
I'm not sure what is actually going on in your code. Are you using sql to fetch published data?
I am using it to get data from my members - is there another way?
There is a GetMember(int id) method in the standard xslt extensions that would return memberdata as xml. Do you need to list all members?
I'd recommend moving that out into a XSLT Helper. Writing a class library to return an XPathNodeIterator. At least I think that's the way to go it's been a while since I returned a nodeset to XSLT. I think TranctSQL can return XML also.
I only need some of the members that have filled out something in their "kundeStatement"
Thanks Jamie for the idea, i dont really understand what you tell me, but I will have a look at it.
A sample of how it could be done in an extension method would be:
public static XPathNodeIterator GetAllMembers() { IEnumerable<Member> members = Member.GetAllAsList(); var builder = new StringBuilder(); builder.Append("<members>"); foreach (Member member in members) { builder.Append(member.ToXml(new XmlDocument(), false).OuterXml); } builder.Append("</members>"); var retDoc = new XmlDocument(); retDoc.LoadXml(builder.ToString()); return retDoc.CreateNavigator().Select("/"); }Thanks for the help.
I found a way to limit the sgl call and use <xsl:sort select="kundeUrl" order="ascending" data-type="text"/> to sort it.
is working on a reply...