Get text data into XML SP - 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 2005 Forums

Transact-SQL (2005)

Get text data into XML SP

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2008-01-15 : 12:09:28

Hi,

I've got some XML which exists as a text variable in a temp table.

I need to pass this XML into sp_xml_preparedocument so I can rebuild a table out of it. But I can't figure out the syntax.

If I try doing this:


declare @idoc int
exec sp_xml_preparedocument @idoc output, (select XmlResult from #cache)

I get an error, with or without the brackets round the select statement.

The temp table is created using an SP, but I can't call that directly either. This:


declare @idoc int
exec sp_xml_preparedocument @idoc output, exec Search$GetCache @searchID

Also throws an error.

I can't put it into a local variable because they can't be of type text. I can't pass it into the SP somewhere as it's being generated on the fly.

How can I get my xml into sp_xml_preparedocument?

Cheers,
Matt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-15 : 12:32:54

Not sure it will do the job, but can you try casting this text to xml var type and load it into a xml variable and use xpath queries to retrieve data?

mattt
Posting Yak Master

194 Posts

Posted - 2008-01-15 : 12:51:44

quote:
Originally posted by visakh16

Not sure it will do the job, but can you try casting this text to xml var type and load it into a xml variable and use xpath queries to retrieve data?

I'm using SQL 2000. I don't think it has an xml data type.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources