Passing documents > 8000 chars to sp_xml_preparedocument

We are storing incoming xmldocumkents in a TEXT column of a table.
I've been trying to find a way of lifting that text data out of the table and passing the data to sp_xml_preparedocument for further processing with OPENXML.
This is fine if the data is < 8000 bytes, however our documents are going to be larger than that. Given that we cannot create a local variable of type TEXT, how do we work around this?
We made one attempt to chunk the data into a temporary table and rebuild it into an sp TEXT parameter as below, but it didn't work.
Any ideas greatly appreciated
Thanks
Mark
CREATE TABLE [dbo].[tblMessage] (
[tblMessageId] [int] IDENTITY (1, 1) NOT NULL ,
[tblmessageXML] [text] COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
--This is still < 8000
INSERT INTO tblMessage (tblmessageXML) VALUES ('<NewDataSet><Table><tblPrimaryQueueID>5</tblPrimaryQueueID><tblPeopleStagingID>9</tblPeopleStagingID><tblPeopleStagingStatus>0</tblPeopleStagingStatus><tblPeopleStagingPeopleID>1 6</tblPeopleStagingPeopleID><
tblPeopleStagingPeopleFName>Homer</tblPeopleStagingPeopleFName><tblPeopleStagingPeopl eLName>Simpson</tblPeopleStagingPeopleLName><tblPeopleStagingPeopl eCauswayURN>16201</tblPeopleStagingPeopleCauswayURN></Table></NewDataSet>')
create procedure xmltest2(@.XMLTEXT TEXT)
AS
declare @.dl as int
declare @.Ct as int
declare @.cs as Int
--declare @.XMLtext as VARCHAR(8000)
declare @.tmpXML as VARCHAR(8000)
declare @.MessID as INT
SET @.MessID = 1
set @.cs = 20
select @.dl = DataLength(tblMessageXML) from tblMessage
drop table #t
create table #t (tid INT IDENTITY, tmid INT, tvc VARCHAR(20))
SET @.ct = 1
WHILE @.CT < @.DL
BEGIN
INSERT INTO #t (tmid, tvc)
SELECT @.MessID, SUBSTRING(tblMessageXML, @.CT, @.cs)
FROM tblMessage
--WHERE tblMessageID = @.MessID
SET @.CT = @.CT + @.CS
END
DECLARE XML_CURSOR CURSOR FAST_FORWARD
FOR
SELECT tvc
FROM #t
ORDER BY TID
OPEN XML_CURSOR
FETCH NEXT FROM XML_CURSOR INTO @.TMPXML
--SET @.XMLTEXT = ''
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.XMLTEXT = @.XMLTEXT + @.TMPXML
FETCH NEXT FROM XML_CURSOR INTO @.TMPXML
END
CLOSE XML_CURSOR
DEALLOCATE XML_CURSOR
Hi
Check out:
http://sqlxml.org/faqs.aspx?faq=42
John
"Mark McCormick" <anonymous@.discussions.microsoft.com> wrote in message
news:0C8F6CD3-A116-40C4-9468-9B31F975B32F@.microsoft.com...
> We are storing incoming xmldocumkents in a TEXT column of a table.
> I've been trying to find a way of lifting that text data out of the table
and passing the data to sp_xml_preparedocument for further processing with
OPENXML.
> This is fine if the data is < 8000 bytes, however our documents are going
to be larger than that. Given that we cannot create a local variable of
type TEXT, how do we work around this?
> We made one attempt to chunk the data into a temporary table and rebuild
it into an sp TEXT parameter as below, but it didn't work.
> Any ideas greatly appreciated
> Thanks
> Mark
>
> CREATE TABLE [dbo].[tblMessage] (
> [tblMessageId] [int] IDENTITY (1, 1) NOT NULL ,
> [tblmessageXML] [text] COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> --This is still < 8000
> INSERT INTO tblMessage (tblmessageXML) VALUES
('<NewDataSet><Table><tblPrimaryQueueID>5</tblPrimaryQueueID><tblPeopleStagi
ngID>9</tblPeopleStagingID><tblPeopleStagingStatus>0</tblPeopleStagingStatus
><tblPeopleStagingPeopleID>16</tblPeopleStagingPeopleID><tblPeopleStagingPeo
pleFName>Homer</tblPeopleStagingPeopleFName><tblPeopleStagingPeopl eLName>Sim
pson</tblPeopleStagingPeopleLName><tblPeopleStagingPeopl eCauswayURN>16201</t
blPeopleStagingPeopleCauswayURN></Table></NewDataSet>')
> create procedure xmltest2(@.XMLTEXT TEXT)
> AS
> declare @.dl as int
> declare @.Ct as int
> declare @.cs as Int
> --declare @.XMLtext as VARCHAR(8000)
> declare @.tmpXML as VARCHAR(8000)
> declare @.MessID as INT
> SET @.MessID = 1
> set @.cs = 20
> select @.dl = DataLength(tblMessageXML) from tblMessage
> drop table #t
> create table #t (tid INT IDENTITY, tmid INT, tvc VARCHAR(20))
> SET @.ct = 1
> WHILE @.CT < @.DL
> BEGIN
> INSERT INTO #t (tmid, tvc)
> SELECT @.MessID, SUBSTRING(tblMessageXML, @.CT, @.cs)
> FROM tblMessage
> -- WHERE tblMessageID = @.MessID
> SET @.CT = @.CT + @.CS
> END
> DECLARE XML_CURSOR CURSOR FAST_FORWARD
> FOR
> SELECT tvc
> FROM #t
> ORDER BY TID
>
> OPEN XML_CURSOR
> FETCH NEXT FROM XML_CURSOR INTO @.TMPXML
> --SET @.XMLTEXT = ''
>
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.XMLTEXT = @.XMLTEXT + @.TMPXML
> FETCH NEXT FROM XML_CURSOR INTO @.TMPXML
> END
> CLOSE XML_CURSOR
> DEALLOCATE XML_CURSOR
>
>
|||Another good one to have a look at
http://www.experts-exchange.com/Data..._20670044.html

Pass data to a report?

Hi all,

We have an ASP.NET application that generates various reports and word documents. The word documents and the reports require the exact same data and we have written some rather complex functionality that generates the SQL select statements to fetch this data. The statements are currently being used to create ADO.NET DataSets which feed the word documents. How can we now get these DataSets to feed the reports too? We would really, really, really like to avoid having to recreate the logic for constructing the select statements within SSRS. Can we pass the DataSets themselves, or can we serialize them to XML using the WriteXml method then pass the Xml data as parameters or is there a way to just pass the SQL to the report? We have had a play around and have done some research to figure this out but have so far been unsuccessful.
Any help you have to offer would be greatly appreciated!
Thanks,

Stephen.

Why not write this data to a table and just pass an ID to the report, identifying the rows to be printed?

Just an idea.

BobP

|||

Hi Stephen-

I can think of two possibilities if you are using Reporting Services 2005:

(1) You can create a report using the XML Data Provider. You can point the report to an external XML data source (such as a web service) and consume the data that way

(2) Probably, the easiest way would be to use a web report control which takes the data as a .NET data set and can be emebedded within your application.

If you are on RS 2000, you would need to implement a custom data extension. More information for this is available on the RS 2000 books online.

Thanks, Jon

|||

Reports use a store procedures to retrieve the data.

My suggestions is that after you build your SQL statement, you can take this process one step further and store this SQL as a stored procesure in the SQL server where your tables are. Say, you name this procedure SP_myreport.sql You will use this stored procedure name in your report.

To update the procedure in SQL server you will have to add a "DROP PROCEDURE" and "CREATE PROCEDURE" to your sql script.

|||

BobP - BIM wrote:

Why not write this data to a table and just pass an ID to the report, identifying the rows to be printed?

Just an idea.

BobP

Thanks for your help people and sorry for not getting back sooner. In the end, we opted for this solution as it best fit our situation (we are using 2000 and the custom data extensions are a little over the top for what we are doing. We also want to stay away from putting any logic in the DB). I've had a look and the other two suggestions will also work so I'm marking those as answers to.
Thanks again,

Stephen.

Pass data to a report?

Hi all,

We have an ASP.NET application that generates various reports and word documents. The word documents and the reports require the exact same data and we have written some rather complex functionality that generates the SQL select statements to fetch this data. The statements are currently being used to create ADO.NET DataSets which feed the word documents. How can we now get these DataSets to feed the reports too? We would really, really, really like to avoid having to recreate the logic for constructing the select statements within SSRS. Can we pass the DataSets themselves, or can we serialize them to XML using the WriteXml method then pass the Xml data as parameters or is there a way to just pass the SQL to the report? We have had a play around and have done some research to figure this out but have so far been unsuccessful.
Any help you have to offer would be greatly appreciated!
Thanks,

Stephen.

Why not write this data to a table and just pass an ID to the report, identifying the rows to be printed?

Just an idea.

BobP

|||

Hi Stephen-

I can think of two possibilities if you are using Reporting Services 2005:

(1) You can create a report using the XML Data Provider. You can point the report to an external XML data source (such as a web service) and consume the data that way

(2) Probably, the easiest way would be to use a web report control which takes the data as a .NET data set and can be emebedded within your application.

If you are on RS 2000, you would need to implement a custom data extension. More information for this is available on the RS 2000 books online.

Thanks, Jon

|||

Reports use a store procedures to retrieve the data.

My suggestions is that after you build your SQL statement, you can take this process one step further and store this SQL as a stored procesure in the SQL server where your tables are. Say, you name this procedure SP_myreport.sql You will use this stored procedure name in your report.

To update the procedure in SQL server you will have to add a "DROP PROCEDURE" and "CREATE PROCEDURE" to your sql script.

|||

BobP - BIM wrote:

Why not write this data to a table and just pass an ID to the report, identifying the rows to be printed?

Just an idea.

BobP

Thanks for your help people and sorry for not getting back sooner. In the end, we opted for this solution as it best fit our situation (we are using 2000 and the custom data extensions are a little over the top for what we are doing. We also want to stay away from putting any logic in the DB). I've had a look and the other two suggestions will also work so I'm marking those as answers to.
Thanks again,

Stephen.