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