Update text col with valid xml based on sql statement in another column in the same row

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)

Update text col with valid xml based on sql statement in another column in the same row

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-30 : 09:26:02

Jasper Smith writes "SQL2000 SP2/Windows 2000 Server SP2/SQLXML 3.0 /MSXML 4.0
Given the following table

CREATE TABLE [xmlrep] (
[name] [varchar] (30) NOT NULL ,
[sql_text] [varchar] (4000) NOT NULL ,
[xml_text] [text] NULL ,
[xsl_text] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE UNIQUE CLUSTERED INDEX [pk_xmlrep] ON [dbo].[xmlrep]([name]) ON [PRIMARY]
GO

and the following INSERT statement

insert xmlrep values(
'EMPLOYEES',
'select FirstName,LastName from Northwind.dbo.Employees E for xml auto,elements',
NULL,
NULL)

What is the best way to update the xml_text column with a valid well formed XML document based on the query in the sql_text column. It needs to have <ROOT> and </ROOT> tags.
This sounds relatively straight forward but it turns out that it is surprisingly difficult (for me!). These DO have to be text columns, varchar will not be large enough(although it would work for this example).Any great idea's ?? The problem is you cannot use FOR XML in an insert statement and local variables can't be text datatype.I have a rather "wobbly" looking solution below but I'm sure I'm missing something vital. Please help as my brain is starting to turn to mush !! Basically - is there a way to do this without using a temporary file ?

SOLUTION(OF SORTS)
It requires a directory c:\xml\temp as a working directory ( I said it was "wobbly" !!)

CREATE PROCEDURE update_xml @name varchar(30)
as
set nocount on
declare @sql varchar(4000)
declare @cmdxmlout varchar(500)
declare @cmdxmlin varchar(500)
declare @cmddeltemp varchar(500)
declare @tempfile varchar(35) ; set @tempfile = @name + '.temp'
declare @xmlptr binary(16)
create table ##xml(xml_text text)

--cmd to generate XML
select @sql = sql_text from xmlrep (nolock) where [name]=@name

--build bcp cmd to generate xml
set @cmdxmlout='bcp "' + @sql + '" queryout c:\xml\temp\'+ @tempfile +' -S2KAS -T -c -r -t'
exec master..xp_cmdshell @cmdxmlout,no_output

--insert the xml into @xmltemp
set @cmdxmlin='BULK INSERT ##xml FROM ''c:\xml\temp\'+@tempfile+''''
exec(@cmdxmlin)

--do the update to xmlrep
update dbo.xmlrep
set xml_text=x.xml_text from ##xml x where [name]=@name

--clean up temp stuff
set @cmddeltemp = 'del /Q c:\xml\temp\'+@tempfile
exec master..xp_cmdshell @cmddeltemp,no_output
drop table ##xml

--Add ROOT tags to make well formed XML doc
select @xmlptr = textptr(xml_text) from xmlrep where [name]=@name

UPDATETEXT xmlrep.xml_text @xmlptr 0 0 '<ROOT>'
UPDATETEXT xmlrep.xml_text @xmlptr NULL 0 '</ROOT>'

--ALL DONE"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-30 : 14:17:45

A. Nice code! Very impressive!

B. I think you've found about the best way to do this particular function. Until Yukon comes out, I think this is about as good as you'll get with putting XML into a SQL Server table.

C. What exactly are you doing with the generated XML? I imagine that you're retrieving it from the SQL table into an ADO Stream for an ASP/.Net or other application, correct? Why not just generate it on the fly instead of storing it? If you need to cache it, generate it once at the top of the session, then put it into a variable or something. Or if it's universal, you can put it into a disk file that can be read by the application.

I don't know if you saw this or not:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=9336

but in case you don't want to use UPDATETEXT to add the <root> tags, you can use that thread instead.

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-04-30 : 16:15:00

I had fun writing it :-)
The problem is our db's are extremely heavily hit OLTP internet ecommerce databases and very normalised so what sounds like a simple adhoc query can really struggle.My aim is to cache the results of these sql queries as XML in a text field. Thus when an intranet user hits a report page all it does is fetch the XML and XSL and via ASP apply the transformation. I use a SQLXML template to generate the report framework and 1 generic asp page to do the transform.
I could persist the xml files but this way I have only 3 files to worry about 1 XML,1 XSL and 1 ASP . The idea is minimum DBA effort to set up a new report. This way its just a simple insert.

That's why I switched to updatetext - got rid of another 2 files :-)
Its a very good post that one - I have used it quite a lot - I just quite liked the idea of having an entire report sweet in 1 table :-)

Cheers
Jasper

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-04-30 : 16:18:54

quote:

I just quite liked the idea of having an entire report sweet in 1 table :-)

I mean report suite

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources