How to pass text column to SP?
- Hi everyone,I'm storing XML in text column.Basically I need to pass that column to sp_xml_preparedocument procedure.Is there any way to do that? I cannot use varchar(8000) because XML can be much longer (3-20 megabytes).So basically my question is: is there any way to pass text column as a parameter to stored procedure in SQL server 2000?Thanks,Alex
-
Hold the phone:
You have an XML String that you are storing
And you need to read it using the sp_xml_preparedocument procedure
not sure what the problems is here..
please post more of what you are trying to do
Maybe some code..
Will
-
The problem is that I'm storing XML in table column.
For example I have table:
create table Table1(ID int, InXML text)
Let's say I have 1 row in this table and InXML column has string with over 20000000 bytes (complex XML).
Now how do I call sp_xml_preparedocument and pass InXML field as xmltext parameter???
if I have less than 8000 characters in my InXML string than I can do it:
-----
declare @InDoc int, @InXML varchar(8000)
select @InXML = InXML from Table1 where ID=1
exec sp_xml_preparedocument @InDoc output, @InXML
-----
but I cannot declare local variable of type text
Alex
-
I am sorry to be a bad news delivery boy but this is how it goes:
1. Text Columns are one of the most Inflexible features in SQL Server
2. It is impossible to have text variables
3. It is very difficult to manipulate them
If I have not scare you out of it yet here is a link to a very clucky way to make it work. It uses Dynamic Sql but for your data SIZE I would test it VERY well because that was one of the reasons that MS came up with the sql xml bulk load utility because sql would not play nice with large xml files
HTH
* Noel -
Although a little round-about you can declare a local variable of type text. In the create statement of the SP declare a parameter of type text and default it to empty then use it as you would anyother local veriable.
CREATE PROCEDURE XXXX
@local TEXT = '',
@anythingelse VARCHAR(100),
AS
SET @local = 'Some TEXT field here'
I use this often when dealing with XML
-
Well - that's too simple - I tried to compile the following SP:
CREATE PROCEDURE XXXX
@local TEXT = '',
@anythingelse VARCHAR(100)
AS
begin
SET @local = 'Some TEXT field here'
end
and I've got the following message:
Server: Msg 409, Level 16, State 1, Procedure XXXX, Line 11
The assignment operator operation cannot take a text data type as an argument.
Please let me know what I did wrong
Alex
-
Noel - thanks for the link - I knew this option, but I hoped to see something else.
Basically I resolved that issue by ... not storing XML in text column. I'm parsing XML and store it in a table with NodeID, PrevID, NodeType, Name and Value - so I'm not limited to the size of XML and I don't even need to parse XML every time I need some data - I can just run select statement against that table. Then if I need to return XML back to client - I'm creating it from that table. It works, but it's very complex SQL system - I want to make it much simpler.
Thanks for the link.
Alex
Noel wrote:
=======================
I am sorry to be a bad news delivery boy but this is how it goes:
1. Text Columns are one of the most Inflexible features in SQL Server
2. It is impossible to have text variables
3. It is very difficult to manipulate them
If I have not scare you out of it yet here is a link to a very clucky way to make it work. It uses Dynamic Sql but for your data SIZE I would test it VERY well because that was one of the reasons that MS came up with the sql xml bulk load utility because sql would not play nice with large xml files
HTH
* Noel
-
Well if you are doing all that, then just save a link to the xml file and let the client side do the XPath if needed
Anyways glad you make it work
* Noel -
How are you getting the XML Doc?
I am using Bulk Load and an overflow-field (this goes in the .xsd) to get the entire XML DOC into a TEXT field in the database. Then I use the folowing to prepare the doc for XPath queries.
CREATE PROCEDURE [dbo].[XML_Objects]
@iImport_id [int],
@cTransaction_id [varchar](10),
@sXML [text] = ''
AS
DECLARE @iDoc INT
DECLARE @iReturn INT
SET @sXML = ''
SET @sXML = (SELECT Object_XML
FROM dbo.XML_Transactions
WHERE Import_nid = @iImport_id
AND id = @cTransaction_id)
EXEC @iReturn = sp_xml_preparedocument @iDoc OUTPUT, @sXML
-
So you are saying you have table XML_Transactions where field Object_XML
is defined as TEXT?
Anyway - I cannot even compile that SP with 1 line of code: SET @sXML = ''
SQL server returns error:
Server: Msg 409, Level 16, State 1, Procedure XXXX, Line 6
The assignment operator operation cannot take a text data type as an argument.
Alex
cowboyferrier wrote:
I am using Bulk Load and an overflow-field (this goes in the .xsd) to get the entire XML DOC into a TEXT field in the database. Then I use the folowing to prepare the doc for XPath queries.
CREATE PROCEDURE [dbo].[XML_Objects]
@iImport_id [int],
@cTransaction_id [varchar](10),
@sXML [text] = ''
AS
DECLARE @iDoc INT
DECLARE @iReturn INT
SET @sXML = ''
SET @sXML = (SELECT Object_XML
FROM dbo.XML_Transactions
WHERE Import_nid = @iImport_id
AND id = @cTransaction_id)
EXEC @iReturn = sp_xml_preparedocument @iDoc OUTPUT, @sXML
-
noeld - could you please show your create table statement for dbo.XML_Transactions table?
Thanks,
Alex
-
Home from work and don't have the exact code but you are correct, the table dbo.XML_Transactions has two fields the root tag and Overflow field defined as a TEXT column. Will get you the exact code in the morning. What version of SQL Server are you on and what service pack? I will try it on your version of SQL to make sure there is no difference.
-
I'm not sure what service pack it is - but it's SQL server 2000 enterprise edition with the latest service pack. I can check it tomorrow morning.
Alex
-
You may parse the entire XML in 2 steps, if your procedure is called in ASP or VB.
1. Make a new stored procedure. You use here the READTEXT statement to read the entire xml. The output of this stored procedure is saved in a Stream object.
2. The text of the Stream object is used as argument for the second stored procedure, argument that is [text]. Thus, in this second stored procedure you can parse the entire xml.
-
bliviu - Thanks for your suggestion.
But it means that every XML parse in SQL server (from text field column) must have two calls from client (first call: client is reading TEXT field; second call: client is calling another SP with XML TEXT as a parameter). Yes - it works, except it does required client's participation. Also in my system I need to parse XML as a response to another client's call
I can probably create an extended SP which I can call to read XML TEXT field and then that extended SP can call another SP and pass that XML as TEXT parameter. Not sure if I want to do this.
As usually with SQL server (after 7 years with Oracle) it's not programing - it's just finding the way around (avoiding SQL server limitations).... sorry - I cannot stop complaining
Alex
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply