Retrieving XML from a TEXT field.

(SQL Server 2000, SP3)
Hello all!
I *know* that this question has probably been asked and answered a number of times, but I
don't think I crafted the appropriate search terms for Google and sqlxml.org. Please bear
with me.
If I have a relatively simple, but large, XML document stored as a TEXT column in a table,
is there any easy way to take all table rows (of which there aren't many) and somehow call
OPENXML with that TEXT column to process the XML documents therein, and essentially
produce a "native" SQL Server result set based on that XML?
Thanks for any help you can provide!
John PetersonThanks for the help, Michael!
Out of curiosity, do you have a pointer to a sample of your Proposal #1? That sounds like
an interesting approach (though, perhaps "expensive" -- but this would be for a function
that's not heavily used in our system).
Thanks for any additional help you can provide!
John Peterson
"SQL Server Development Team [MSFT]" <sqldev@.microsoft.com> wrote in message
news:eYhTw%23SVDHA.2344@.TK2MSFTNGP09.phx.gbl...
> SQL Server 2000 has the limitation that you cannot define variables of type
> TEXT/NTEXT. There are two expensive workarounds, one of which may not
> continue to work in Yukon (although in Yukon you do not need that workaround
> anymore):
> 1. Pass the data out of the server and back in as a stored proc parameter of
> type NTEXT/TEXT using the sp_OA stored procs.
> 2. Use nested EXECUTEs and a cursor to copy the XML into a string literal
> argument to sp_xml_preparedocument.
> The second one is the one that may not work anymore.
> Best regards
> Michael
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm.
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OdPsARJUDHA.2196@.TK2MSFTNGP11.phx.gbl...
> > (SQL Server 2000, SP3)
> >
> > Hello all!
> >
> > I *know* that this question has probably been asked and answered a number
> of times, but I
> > don't think I crafted the appropriate search terms for Google and
> sqlxml.org. Please bear
> > with me.
> >
> > If I have a relatively simple, but large, XML document stored as a TEXT
> column in a table,
> > is there any easy way to take all table rows (of which there aren't many)
> and somehow call
> > OPENXML with that TEXT column to process the XML documents therein, and
> essentially
> > produce a "native" SQL Server result set based on that XML?
> >
> > Thanks for any help you can provide!
> >
> > John Peterson
> >
> >
>|||I dont know any easy way...
The way I solve it was to call a DTS that pases the text data to a stored
procedure as a parameter.
"John Peterson" <j0hnp@.comcast.net> escribió en el mensaje
news:OdPsARJUDHA.2196@.TK2MSFTNGP11.phx.gbl...
> (SQL Server 2000, SP3)
> Hello all!
> I *know* that this question has probably been asked and answered a number
of times, but I
> don't think I crafted the appropriate search terms for Google and
sqlxml.org. Please bear
> with me.
> If I have a relatively simple, but large, XML document stored as a TEXT
column in a table,
> is there any easy way to take all table rows (of which there aren't many)
and somehow call
> OPENXML with that TEXT column to process the XML documents therein, and
essentially
> produce a "native" SQL Server result set based on that XML?
> Thanks for any help you can provide!
> John Peterson
>|||Since I had a hard-drive fail on me that contained my sample data, I
currently do not have a sample. Sorry.
The HD is in recovery and if I get the data back (and I remember this thread
after my summer vacation), I will check...
Maybe somebody else has some examples (see also other related threads such
as "Text in Stored Procedure").
Best regards
Michael
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23V9l7CWVDHA.2040@.TK2MSFTNGP10.phx.gbl...
> Thanks for the help, Michael!
> Out of curiosity, do you have a pointer to a sample of your Proposal #1?
That sounds like
> an interesting approach (though, perhaps "expensive" -- but this would be
for a function
> that's not heavily used in our system).
> Thanks for any additional help you can provide!
> John Peterson
>
> "SQL Server Development Team [MSFT]" <sqldev@.microsoft.com> wrote in
message
> news:eYhTw%23SVDHA.2344@.TK2MSFTNGP09.phx.gbl...
> > SQL Server 2000 has the limitation that you cannot define variables of
type
> > TEXT/NTEXT. There are two expensive workarounds, one of which may not
> > continue to work in Yukon (although in Yukon you do not need that
workaround
> > anymore):
> >
> > 1. Pass the data out of the server and back in as a stored proc
parameter of
> > type NTEXT/TEXT using the sp_OA stored procs.
> > 2. Use nested EXECUTEs and a cursor to copy the XML into a string
literal
> > argument to sp_xml_preparedocument.
> >
> > The second one is the one that may not work anymore.
> >
> > Best regards
> > Michael
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > Use of included script samples are subject to the terms specified at
> > http://www.microsoft.com/info/cpyright.htm.
> >
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:OdPsARJUDHA.2196@.TK2MSFTNGP11.phx.gbl...
> > > (SQL Server 2000, SP3)
> > >
> > > Hello all!
> > >
> > > I *know* that this question has probably been asked and answered a
number
> > of times, but I
> > > don't think I crafted the appropriate search terms for Google and
> > sqlxml.org. Please bear
> > > with me.
> > >
> > > If I have a relatively simple, but large, XML document stored as a
TEXT
> > column in a table,
> > > is there any easy way to take all table rows (of which there aren't
many)
> > and somehow call
> > > OPENXML with that TEXT column to process the XML documents therein,
and
> > essentially
> > > produce a "native" SQL Server result set based on that XML?
> > >
> > > Thanks for any help you can provide!
> > >
> > > John Peterson
> > >
> > >
> >
> >
>