Microsoft XML - How do i control the number of records displaying from a datasrc xml in a data island?

Author

How do i control the number of records displaying from a datasrc xml in a data island?

Paddy

2006-08-06, 10:31 pm

I have the following code to pull the newflash data into my html data
islands that consists of a table, but i only want to diplay the 3 most
recent stories sorted by date if possible so the newest is first.

Please can someone help me to this, much appreciated.

---------XML Data Island in HTML-------------
<xml id="newsflash" src="xml/newsflash.xml"></xml>
<table width="100%" border="0" cellspacing="2" cellpadding="3"
datasrc="#newsflash">
<tr>
<td><h2 align="left"><span datafld="Date"></span></h2></td>
<td><h2 align="left"><span datafld="Title"></span></h2></td>
</tr>
<tr>
<td colspan="3"><p><div align="left"><span
datafld="Article"></span></div></p></td>
</tr>
</table>
------------------------------------------------------------

--------------newsflash.xml-------------------------

<?xml version="1.0" encoding="iso-8859-1"?>

<NEWS>
<STORY>
<Date>01/02/2006</Date>
<Title>New Website</Title>
<Article>Thegfgdfgd dfgdfgdfgdg df gdfgd f gdgdfgdfgArticle>
</STORY>

<STORY>
<Date>02/02/2006</Date>
<Title>Prints for sale</Title>
<Article>dfgdfg fdg dfgdfg dfgdfgdgdgdgArticle>
</STORY>

<STORY>
<Date>03/02/2006</Date>
<Title>Panormania</Title>
<Article>fdgdfg some select dfgdfstore. dfgand matted makinng them
ideal for home display or a gifts.</Article>
</STORY>

<STORY>
<Date>04/02/2006</Date>
<Title>Go get em tiger</Title>
<Article>You can dfgfdg store. Prints fdgmattedfdgd ideal for
homefdga gifts. asfsdfsdfsdfsdfsdfsdf sdfsd ssdf sd</Article>
</STORY>

Peter W. DeBetta

2006-08-07, 6:33 pm

Assuming you correct your posted XML and assuming you are not using an XML
schema collection...

;WITH StorySection (StoryPath) AS

( SELECT @x.query('/NEWS/STORY') AS StoryPath

FOR XML PATH(''), TYPE

)

,Story AS

( SELECT StoryNodes.DBCol.value('./Date[1]', 'datetime') AS StoryDate

, StoryNodes.DBCol.value('./Title[1]', 'varchar(100)') AS StoryTitle

, StoryNodes.DBCol.value('./Article[1]', 'varchar(4000)') AS StoryArticle

FROM StorySection

CROSS APPLY StoryPath.nodes('/StoryPath/STORY') StoryNodes(DBCol)

)

SELECT TOP 3 *

FROM Story

ORDER BY StoryDate Desc

This should get you going...
--
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--
"Paddy" <pcorkery@jerseymail.co.uk> wrote in message
news:1154909151.733236.187010@m79g2000cwm.googlegroups.com...
>I have the following code to pull the newflash data into my html data
> islands that consists of a table, but i only want to diplay the 3 most
> recent stories sorted by date if possible so the newest is first.
>
> Please can someone help me to this, much appreciated.
>
> ---------XML Data Island in HTML-------------
> <xml id="newsflash" src="xml/newsflash.xml"></xml>
> <table width="100%" border="0" cellspacing="2" cellpadding="3"
> datasrc="#newsflash">
> <tr>
> <td><h2 align="left"><span datafld="Date"></span></h2></td>
> <td><h2 align="left"><span datafld="Title"></span></h2></td>
> </tr>
> <tr>
> <td colspan="3"><p><div align="left"><span
> datafld="Article"></span></div></p></td>
> </tr>
> </table>
> ------------------------------------------------------------
>
> --------------newsflash.xml-------------------------
>
> <?xml version="1.0" encoding="iso-8859-1"?>
>
> <NEWS>
> <STORY>
> <Date>01/02/2006</Date>
> <Title>New Website</Title>
> <Article>Thegfgdfgd dfgdfgdfgdg df gdfgd f gdgdfgdfgArticle>
> </STORY>
>
> <STORY>
> <Date>02/02/2006</Date>
> <Title>Prints for sale</Title>
> <Article>dfgdfg fdg dfgdfg dfgdfgdgdgdgArticle>
> </STORY>
>
> <STORY>
> <Date>03/02/2006</Date>
> <Title>Panormania</Title>
> <Article>fdgdfg some select dfgdfstore. dfgand matted makinng them
> ideal for home display or a gifts.</Article>
> </STORY>
>
> <STORY>
> <Date>04/02/2006</Date>
> <Title>Go get em tiger</Title>
> <Article>You can dfgfdg store. Prints fdgmattedfdgd ideal for
> homefdga gifts. asfsdfsdfsdfsdfsdfsdf sdfsd ssdf sd</Article>
> </STORY>
>

Peter W. DeBetta

2006-08-07, 6:33 pm

And assuming you are working in SQL Server 2005 (my bad for not mentioning
that in the last reply)

--
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--
"Peter W. DeBetta" <debettap@hotmail.com> wrote in message
news:eKCCXsluGHA.4360@TK2MSFTNGP03.phx.gbl...
> Assuming you correct your posted XML and assuming you are not using an XML
> schema collection...
>
> ;WITH StorySection (StoryPath) AS
>
> ( SELECT @x.query('/NEWS/STORY') AS StoryPath
>
> FOR XML PATH(''), TYPE
>
> )
>
> ,Story AS
>
> ( SELECT StoryNodes.DBCol.value('./Date[1]', 'datetime') AS StoryDate
>
> , StoryNodes.DBCol.value('./Title[1]', 'varchar(100)') AS StoryTitle
>
> , StoryNodes.DBCol.value('./Article[1]', 'varchar(4000)') AS StoryArticle
>
> FROM StorySection
>
> CROSS APPLY StoryPath.nodes('/StoryPath/STORY') StoryNodes(DBCol)
>
> )
>
> SELECT TOP 3 *
>
> FROM Story
>
> ORDER BY StoryDate Desc
>
> This should get you going...
> --
> Peter DeBetta, MVP - SQL Server
> http://sqlblog.com
> --
> "Paddy" <pcorkery@jerseymail.co.uk> wrote in message
> news:1154909151.733236.187010@m79g2000cwm.googlegroups.com...
>
>