Problem Passing Text/NText to sp_xml_preparedocument

I'm stuck, this (the code below) should work, but it doesn't. Why? Is there
another way to do this that I should use instead?
Declare @.Hand1 as int
Declare @.ptrval varbinary(16)
Declare @.Length integer
Select @.ptrval = TEXTPTR(DEV_409.Doc.DocTxt),
@.Length = DataLength(DEV_409.Doc.DocTxt)
from DEV_409.Doc where DEV_409.Doc.DocID = 11320
/* NOTE: dbo.tbl_Documents.DocTxt is a field of data type of TEXT containing
a well formed XML document. */
/* The following statement fails with the error message <<Incorrect syntax
near the keyword 'READTEXT'.>> */
Exec sp_xml_preparedocument @.Hand1 OUTPUT, READTEXT DEV_409.Doc.DocTxt
@.ptrval 0 @.LengthHow can I parse XML from a text column using sp_xml_preparedocument?
http://www.sqlxml.org/faqs.aspx?faq=42
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Ed Lippert" <lippert@.mn.rr.com> wrote in message
news:OI7PEAsBEHA.3348@.TK2MSFTNGP11.phx.gbl...
> I'm stuck, this (the code below) should work, but it doesn't. Why? Is
there
> another way to do this that I should use instead?
> Declare @.Hand1 as int
> Declare @.ptrval varbinary(16)
> Declare @.Length integer
> Select @.ptrval = TEXTPTR(DEV_409.Doc.DocTxt),
> @.Length = DataLength(DEV_409.Doc.DocTxt)
> from DEV_409.Doc where DEV_409.Doc.DocID = 11320
> /* NOTE: dbo.tbl_Documents.DocTxt is a field of data type of TEXT
containing
> a well formed XML document. */
> /* The following statement fails with the error message <<Incorrect syntax
> near the keyword 'READTEXT'.>> */
> Exec sp_xml_preparedocument @.Hand1 OUTPUT, READTEXT DEV_409.Doc.DocTxt
> @.ptrval 0 @.Length
>|||The code you referenced is a poor kludge. While it may work in some cases, e
ven with obvious modifications it is not a universal solution (and won't wor
k in my specific appliocation) and is really unelegant.
There's got to be a better solution. MS states that the sp_xml_preparedocume
nt proc will accept text and ntext, so they must have had a way to accomplis
h it, if for no other reason than they had to test it.|||Hello Ed,
Thank you for posting in the community.
I understand you'd like to retrieve the Text/NText data with READTEXT
and pass the result as the second "xmltext" parameter of the
sp_xml_preparedocument.
BOL says:
[xmltext]
Is the original XML document. The MSXML parser parses this XML
document. xmltext is a text (char, nchar, varchar, nvarchar, text, or ntext
)
parameter. The default value is NULL, in which case an internal
representation of an empty XML document is created.
We can see that [xmltext] is a text parameter, which can be text or ntex
t data
type. However, it cannot be a batch statement "READTEXT DEV_
409.Doc.DocTxt @.ptrval 0 @.Length". Hence, SQL Server will issue a
Incorrect syntax error.
In my view, Jasper has lighted you on the right way. The solution to this
problem is to execute the statement dynamically. If the text length is short
er
than 8000 characters, you can use the following method directly.
----
--
CREATE PROC textproc
@.atext TEXT
AS
DECLARE @.hdoc int
EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.atext
EXEC sp_xml_removedocument @.hdoc
GO
-- Sample XML document
EXEC textproc '
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-
04T00:00:00">
<OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
<OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-
16T00:00:00">
<OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
----
--
If it goes beyond 8000 characters, you need to kludge the string and
execute it for batch processing. Other than this, there is an open DCR to
enhance the functionality of the SP_XML_PREPAREDOCUMENT to allow
for the functionality that you are looking for.
Let us know if you have further questions. Thanks again for participating in
our community.
Best regards,
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.