Combining XQuery and data paging in SQL Server 2005

Folks,
I have a table in SQL Server 2005 that contains a row of type "XML" (for now
untyped - no schema behind it).
I can easily query that table and retrieve values from the XML - way !
I can also easily query that table and use the data paging mechanism based
on the ROW_NUMBER() function - way , too!
But I can't seem to combine the two...
Imagine I have books stored in my XML column:
<books>
<book id='123'>
<author>Smith</author>
<yearpublished>1999</yearpublished>
</book>
<book id='177'>
<author>Black</author>
<yearpublished>2002</yearpublished>
</book>
... (lots more books) .....
</books>
To query the table, I use something like:
SELECT
-- <some fields from the base table>
BooksXML.value('(/book/author)[1]', 'varchar(50)') as 'Author',
BooksXML.value('(/book/yearpublished)[1]', 'int') as 'YearPublished'
FROM BooksTable
Works fine. Now what I'd like to do is add a ROW_NUMBER() function over the
e.g. "YearPublished" attribute (stored in the XML) to get my books by year
published, in batches of 10 or whatever:
WITH BooksList as
(
SELECT
-- <some fields from the base table>
BooksXML.value('(/book/author)[1]', 'varchar(50)') as 'Author',
BooksXML.value('(/book/yearpublished)[1]', 'int') as 'YearPublished',
ROW_NUMBER() OVER(ORDER BY YearPublished) AS 'rownum'
FROM BooksTable
)
SELECT * FROM BooksList
WHERE rownum BETWEEN 11 AND 20
Trouble is - this won't work, since MgmtStudio complains it doesn't know
about the "YearPublished" column.....
What am I missing? Can I still achieve this goal somehow?
Any hints are most welcome !!
Marc
(mscheuner -* AT *- gmail.com)Untested but try this
WITH BooksList as
(
SELECT
-- <some fields from the base table>
BooksXML.value('(/book/author)[1]', 'varchar(50)') as 'Author',
BooksXML.value('(/book/yearpublished)[1]', 'int') as
'YearPublished',
ROW_NUMBER() OVER(ORDER BY
BooksXML.value('(/book/yearpublished)[1]', 'int')) AS 'rownum'
FROM BooksTable
)
SELECT * FROM BooksList
WHERE rownum BETWEEN 11 AND 20|||... or :-)
WITH x AS
(
SELECT
-- <some fields from the base table>
BooksXML.value('(/book/author)[1]', 'varchar(50)') AS Author,
BooksXML.value('(/book/yearpublished)[1]', 'int') AS YearPublished,
FROM BooksTable
), BooksList AS (
SELECT
*
,ROW_NUMBER() OVER (ORDER BY YearPublished) AS RowNum
FROM x
)
SELECT * FROM BooksList
WHERE rownum BETWEEN 11 AND 20
Also, please consider not using single quotes (') for naming aliases, you
should use double quotes (") as that is the ANSI standard and using single
quotes will be debrecated.
HTH
/ Tobias