Dynamic FLOWR XQuery into an xml variable

Hello.
I'm trying to do something I haven't seen anywhere, and have been
trying everything but no joy. Basically, my XML is in a variable, and
I'm querying that variable to filter it further. The problem is that
these filters are dynamic. I want to keep the results as an xml
variable because I want to work with them further after this query (I
will do an xpath query so I can use position() and return a page). I'd
really like to stay away from using a tmp table for this.
See the code inline for a few of the things I've tried. I appreciate
any help on this...
Thanks very much in advance for anyone taking the time to help.
Relevant Code with sample xml filling the var
This should be ready to run (and break on the dynamic attempts)
Move the close comments (*/) around to try one method at a time
---
DECLARE @.ItemXML xml
SET @.ItemXML = '
<itemdata>
<item>
<rank>558</rank>
<itemid>11111111</itemid>
<catid>1</catid>
<pid>11</pid>
<ctid>1</ctid>
<link>http://www.foo.com</link>
<thumburl>http://foo.com/thumb.jpg</thumburl>
<title>Foo title 1</title>
<published>2006-04-05T15:52:17</published>
<provider>foo provider #1</provider>
</item>
<item>
<rank>558</rank>
<itemid>22222222</itemid>
<catid>22</catid>
<pid>50</pid>
<ctid>2</ctid>
<link>http://www.foo.com</link>
<thumburl>http://foo.com/thumb.jpg</thumburl>
<title>Foo title 2</title>
<published>2006-04-05T15:52:17</published>
<provider>foo provider #2</provider>
</item>
<item>
<rank>558</rank>
<itemid>333333333</itemid>
<catid>33</catid>
<pid>50</pid>
<ctid>3</ctid>
<link>http://www.foo.com</link>
<thumburl>http://foo.com/thumb.jpg</thumburl>
<title>Foo title 3</title>
<published>2006-04-05T15:52:17</published>
<provider>foo provider #3</provider>
</item>
</itemdata>'
DECLARE @.CatID int, @.ProviderID int, @.CTID int
-- JUST SET 1 VAR FOR NOW, BUT THIS IS BUILT TO ALLOW MULTIPLE FILTERS
SET @.CTID = 2
DECLARE @.ItemStmt varchar(500), @.XQuery varchar(300), @.WhereStmt
varchar(200)
SET @.WhereStmt = 'where 1 = 1'
IF @.CatID IS NOT NULL
SET @.WhereStmt = @.WhereStmt + ' and $i/catid[1] =
'+CONVERT(varchar(4),@.CatID)
IF @.ProviderID IS NOT NULL
SET @.WhereStmt = @.WhereStmt + ' and $i/pid[1] =
'+CONVERT(varchar(4),@.ProviderID)
IF @.CTID IS NOT NULL
SET @.WhereStmt = @.WhereStmt + ' and $i/ctid[1] =
'+CONVERT(varchar(4),@.CTID)
/*
Base Query with no dynamic vars... this works
*/
SET @.ItemXML = @.ItemXML.query('<itemdata>{
for $i in itemdata[1]/item
where 1 = 1 and $i/ctid[1] = 2
return $i
}</itemdata>
')
SELECT 'Attempt #1: Works',@.ItemXML
/*
Attempt #2... sort of like mrorke at
http://blogs.msdn.com/mrorke/archiv.../24/484237.aspx
EXEC the entire SET @.Var statement.
This results in "Must declare the scalar variable "@.ItemXML"."
-- move close comment here:
SET @.XQuery = '<itemdata>{
for $i in itemdata[1]/item
'+@.WhereStmt+'
return $i
}
</itemdata>'
SET @.XQuery = 'SET @.ItemXML = @.ItemXML.query('''+@.XQuery+''')'
EXEC(@.XQuery)
SELECT 'Attempt #2: Breaks', @.ItemXML
*/
/*
Attempt #3... Try a sql:variable inline
Result: XQuery [query()]: Syntax error near 'sql', expected 'where',
'(stable) order by' or 'return'.
-- move close comment here:
SET @.ItemXML = @.ItemXML.query('<itemdata>{
for $i in itemdata[1]/item
sql:variable("@.WhereStmt")
return $i
}
</itemdata>')
SELECT 'Attempt #3: Breaks', @.ItemXML
*/Rather than using EXEC(@.XQuery), try using this
EXEC sp_executeSQL @.XQuery,N'@.ItemXML XML OUTPUT',@.ItemXML OUTPUT
You'll have to declare @.XQuery as nvarchar instead of varchar|||Markc. Thank you very much for your response. That did exactly what I
needed it to do.
One problem I'm seeing now is in my next step where I apply an xpath to
the @.ItemXML just to do the pagination. It seems that xpath step then
accounts for 85% of my query cost. I'm trying another approach just to
be sure I've covered all my bases and can get the most performant
solution, and I'm running into trouble because I'm still learning the
xml datatype & some other SQL 2005 features. Anyway, I though I'd try a
query against the xml variable, while also joining to my lookup tables
and creating a rownumber for pagination. I've tried several different
syntax with this, but it keeps breaking with "Invalid object name 'R'",
so if anyone can see what I'm doing wrong, again, I'd greatly
appreciate it.
SELECT ROW_NUMBER() OVER (ORDER BY rank DESC) AS RowNumber,
i.value('rank','int'),
i.value('itemid','int'),
i.value('catid','int'),
dbo.c.vch_categorytype AS cat,
i.value('pid','int'),
dbo.p.vch_providername_public AS provider,
i.value('ctid','int'),
i.value('link','varchar(300)'),
i.value('thumburl','varchar(300)'),
i.value('title','varchar(500)'),
i.value('published','datetime')
FROM R cross apply @.ItemXML.nodes('itemdata') R(i)
JOIN Categories_LU c ON R.i.value('catid','int') = c.i_category_id
JOIN Providers_LU p ON R.i.value('pid','int') = p.i_provider_id
WHERE RowNumber BETWEEN 1 AND 20 -- assume page 1, items 1-20
AND R.i.value('ctid','int') = 1 -- this would eventually be a dynamic
where clause|||I think
FROM R cross apply @.ItemXML.nodes('itemdata') R(i)
should be
FROM @.ItemXML.nodes('itemdata') R(i)
However, I believe there are other problems here as well. Suggest you
post the DDL for the two tables and some sample data.
Regards
Mark|||Sorry... I know @.ItemXML.nodes('itemdata') won't return the nodes... I
was thrashing around trying different things. Here are some other ways
I've tried to get this...
FROM R cross apply @.ItemXML.nodes('itemdata/item') R(i)
FROM FooTblNm cross apply @.ItemXML.nodes('itemdata/item') R(i)
... also have mixed up R.i.value and i.value... Nothing made a
difference. Always came back with the table ("R", "FooTblNm") being an
invalid object. Sure, I know answer #1 is I need to read a book so I
don't have to guess on syntax. :-)
Thanks again,
STA|||Here's a complete query with tmp table data...
Again, thanks for your help.
----
--
DECLARE @.ItemXML xml
SET @.ItemXML = '
<itemdata>
<item>
<rank>558</rank>
<itemid>11111111</itemid>
<catid>1</catid>
<pid>11</pid>
<ctid>1</ctid>
<link>http://www.foo.com</link>
<thumburl>http://foo.com/thumb.jpg</thumburl>
<title>Foo title 1</title>
<published>2006-04-05T15:52:17</published>
</item>
<item>
<rank>558</rank>
<itemid>22222222</itemid>
<catid>22</catid>
<pid>50</pid>
<ctid>2</ctid>
<link>http://www.foo.com</link>
<thumburl>http://foo.com/thumb.jpg</thumburl>
<title>Foo title 2</title>
<published>2006-04-05T15:52:17</published>
</item>
<item>
<rank>558</rank>
<itemid>333333333</itemid>
<catid>33</catid>
<pid>50</pid>
<ctid>3</ctid>
<link>http://www.foo.com</link>
<thumburl>http://foo.com/thumb.jpg</thumburl>
<title>Foo title 3</title>
<published>2006-04-05T15:52:17</published>
</item>
</itemdata>'
-- CHECK TO SEE IF #tmps ARE THERE ALREADY...
-- THE PROBABLY DIDN'T GET DROPPED BECAUSE THE QUERY BROKE
IF object_id('tempdb..#tmpCats') IS NULL
BEGIN
CREATE TABLE #tmpCats (i_category_id int, vch_category
varchar(100))
INSERT #tmpCats VALUES (1,'Category 1')
INSERT #tmpCats VALUES (2,'Category 2')
INSERT #tmpCats VALUES (3,'Category 3')
END
IF object_id('tempdb..#tmpProviders') IS NULL
BEGIN
CREATE TABLE #tmpProviders (i_provider_id int, vch_provider
varchar(100))
INSERT #tmpProviders VALUES (1,'Provider 1')
INSERT #tmpProviders VALUES (2,'Provider 2')
INSERT #tmpProviders VALUES (3,'Provider 3')
END
SELECT ROW_NUMBER() OVER (ORDER BY rank DESC) AS RowNumber,
i.value('rank','int'),
i.value('itemid','int'),
i.value('catid','int'),
c.vch_category AS cat,
i.value('pid','int'),
p.vch_provider AS provider,
i.value('ctid','int'),
i.value('link','varchar(300)'),
i.value('thumburl','varchar(300)'),
i.value('title','varchar(500)'),
i.value('published','datetime')
FROM R cross apply @.ItemXML.nodes('itemdata/item') R(i)
JOIN #tmpCats c ON R.i.value('catid','int') = c.i_category_id
JOIN #tmpProviders p ON R.i.value('pid','int') = p.i_provider_id
WHERE RowNumber BETWEEN 1 AND 20 -- assume page 1, items 1-20
AND R.i.value('ctid','int') = 1 -- eventually a dynamic where clause
DROP TABLE #tmpCats
DROP TABLE #tmpProviders|||This should help get you started
;
WITH
XMLNodes(RowNumber,rank,itemid,catid,cat
,pid,provider,ctid,link,thumburl,tit
le,published)
AS(
SELECT ROW_NUMBER() OVER (ORDER BY i.value('rank[1]','int') DESC) AS
RowNumber,
i.value('rank[1]','int'),
i.value('itemid[1]','int'),
i.value('catid[1]','int'),
c.vch_category,
i.value('pid[1]','int'),
p.vch_provider,
i.value('ctid[1]','int'),
i.value('link[1]','varchar(300)'),
i.value('thumburl[1]','varchar(300)'),
i.value('title[1]','varchar(500)'),
i.value('published[1]','datetime')
FROM @.ItemXML.nodes('/itemdata/item') R(i)
JOIN #tmpCats c ON R.i.value('catid[1]','int') = c.i_category_id
JOIN #tmpProviders p ON R.i.value('pid[1]','int') = p.i_provider_id
WHERE R.i.value('ctid[1]','int') = 1
)
SELECT RowNumber,
rank,
itemid,
catid,
cat,
pid,
ctid,
link,
thumburl,
title,
published
FROM XMLNodes
WHERE RowNumber BETWEEN 1 AND 20|||mark,
You absolutely rock! Thank you so much for your help on this.
STA