Cache "for xml" output? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Cache "for xml" output?

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2005-12-20 : 15:42:52

I've got a relatively expensive "for xml" query that gets called by a bunch of webservers about once per minute per webserver. The webservers are already caching the results locally so they don't hit the DB too much.

However, I'd like to move to a model where a SQLAgent job runs the SP once a minute and the result is cached in the DB, and then just retrieved once per minute per web server.

I could rewrite the thing to use a table and store recordsets and stuff, but it seems like it would be a lot easier to just cache the XML output of what I've already got in a varchar field of some cache table.

Is there any way for one SP to call a "for xml" sp, get the output stream, and cache that in a table?

Thanks
-b

aiken
Aged Yak Warrior

525 Posts

Posted - 2006-01-06 : 15:17:30

I'm going to plead for help again here, since the issue is still driving me nuts. Any way to get at the xml output stream on the SQL server side?

-b

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-06 : 15:19:34

If the output of the stored procedure is the XML result set, then you can put that into a table via:

INSERT INTO YourCacheTable(XML)
EXEC YourXMLStoredProc @Param1 = ...

Tara Kizer
aka tduggan

aiken
Aged Yak Warrior

525 Posts

Posted - 2006-01-06 : 15:31:35

A good idea -- but it's not working for me. I get:

Server: Msg 6819, Level 16, State 5, Procedure px_bcom_appdata_CacheUpdate, Line 13
The FOR XML clause is not allowed in a INSERT statement.

Cheers
-b

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-06 : 15:37:02

I couldn't even trick it with dynamic SQL.

This doesn't work:


CREATE TABLE Table1 (XML varchar(4000))

INSERT INTO Table1(XML)
EXEC('SELECT ServerName FROM Server FOR XML AUTO')

DROP TABLE Table1

I'm out of ideas.

Tara Kizer
aka tduggan

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-01-06 : 15:49:31

try that method where you create a self linked server with your own server and use openquery.
i don't know if it'll work and i can't test it now but give it a try.

Go with the flow & have fun! Else fight the flow

aiken
Aged Yak Warrior

525 Posts

Posted - 2006-01-06 : 16:23:15

Hmm, that's an interesting one, with weird results. After creating the self-linked server,


select *
from openquery([self_web],'exec dbo.px_bcom_appdata_cacheUpdate')

Server: Msg 6802, Level 16, State 1, Line 1
FOR XML EXPLICIT query contains the invalid column name 'weekday'. Use the TAGNAME!TAGID!ATTRIBUTENAME[!..] format where TAGID is a positive integer.

The funny thing is that that SP doesn't use "weekday" as a tag; here's the only place in the SP that the word appears:

select 1 as Tag,
	NULL as Parent,
	description_full as [events!1!currentDescription!element],
	description_short as [events!1!currentDescriptionShort!element]
from event_schedule WITH(NOLOCK)
join events WITH(NOLOCK)
	on event_schedule.i_events=events.i
where event_schedule.[weekday]=datepart(dw,getdate())
	and @dCurrentTime>=event_schedule.event_start
	and @dCurrentTime<event_schedule.event_end

for xml explicit

What's weird is that it runs fine locally, just not over the openquery. I feel like we're heading into the twilight zone here :)

-b

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-06 : 16:32:26

Try renaming the weekday column in the event_schedule table. You shouldn't use reserved words as object names anyway.

If you can't make that change, then:

select 1 as Tag,
NULL as Parent,
description_full as [events!1!currentDescription!element],
description_short as [events!1!currentDescriptionShort!element]
into #temp
from event_schedule WITH(NOLOCK)
join events WITH(NOLOCK)
on event_schedule.i_events=events.i
where event_schedule.[weekday]=datepart(dw,getdate())
and @dCurrentTime>=event_schedule.event_start
and @dCurrentTime<event_schedule.event_end

select * from #temp for xml explicit

drop table #temp

Tara Kizer
aka tduggan

aiken
Aged Yak Warrior

525 Posts

Posted - 2006-01-06 : 16:48:46

Yeah, I hear you in the names. I inherited some poor decisions, then I made some of my own :)

I've done one better -- for testing, I've just removed that portion of the sp altogether. However, now I get:

Server: Msg 6802, Level 16, State 1, Line 1
FOR XML EXPLICIT query contains the invalid column name 'i'. Use the TAGNAME!TAGID!ATTRIBUTENAME[!..] format where TAGID is a positive integer.

...and I'm pretty sure "i" isn't a reserved word :)

It seems a little bit like, when run from openquery, it doesn't want any columns in the join or where clauses that aren't in the select clause. So, just for kicks, I created a very simple sp:

create procedure px_test as
set nocount on
select 1 as Tag,
	NULL as Parent,
	'world' as [test!1!hello!element]
for xml explicit

...this works with openquery against a self-linked server. Sort of. The results aren't pretty:

XML_F52E2B61-18A1-11d1-B105-00805F49916B                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
--------------------------------------------------------------------------------------------
0x4405680065006C006C006F00440474006500730074000102410101410310E40400000500776F726C6481018102

Getting clever, I tried:

declare @v varchar(8000)
select @v=[XML_F52E2B61-18A1-11d1-B105-00805F49916B]
from openquery([self_web],'exec dbo.px_test')

...which got me

Server: Msg 206, Level 16, State 2, Line 2
Operand type clash: image is incompatible with varchar

...but that does look like a UTF-16 representation of my data; 680065006c006c006f00 = "hello". So,

declare @v varbinary(8000)
select @v=[XML_F52E2B61-18A1-11d1-B105-00805F49916B]
from openquery([self_web],'exec dbo.px_test')

select cast(@v as nvarchar(4000))

...almost works. I'm not sure what Snitz will think of this, but it returns "#1348;hello#1092;test#513;#321;#16641;#4099;#1252;".

Any ideas? Or have we just gone really far down a dead-end road? I'm tempted to write some kind of parser for that varbinary data, but that just seems a little wacky, even for me.

Thanks!
-b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources