April 23, 2009 at 5:17 am
part of my stored procedure is extracting a section out of an XML file.the whole XML file is about 1600 characters.
i've declared the variable to hold the XML file
and i'm filling that variable using a cursor
DECLARE @WorkingSchemaVARCHAR(MAX)
DECLARE ACursor CURSOR FOR
SELECT SchemaCol FROM table 1 where ID = 36
--specifying ID so that it only brings back one record for testing --purposes
OPEN ACursor
FETCH NEXT FROM ACursor INTO @WorkingSchema
WHILE (@@FETCH_STATUS <> -1)
BEGIN
SELECT @Codes = dbo.udf_extracts_section_of_xml(@WorkingSchema,'Codes')
--the above function grabs a section of the XML (@WorkingSchema). it ---looks for the open and closing tags based on the string 'Codes'.
print @Codes --print for testing
FETCH NEXT FROM ACursor INTO @WorkingSchema
END
now everything between the
what i can't work out is why though. if i take this code out of the Cursor and hardcode the XML text into @WorkingSchema it works fine.
is there aproblem that you people know with using a varchar(max) within a cursor or is it possibly something else?
cheers
April 23, 2009 at 6:06 am
If you are diciding that the XML is cutting short with the help of PRINT statement, let me tell you that the print statement prints the limited number of characters.
April 23, 2009 at 6:21 am
the print statement actuall prints nothign at all 🙂
the function is grabbing everything between the "code" tags.
but i'm thinking that if doesn't exist in the XML within the variable then its going to bring nothing back. which seems to be backed up by the fact that the print statement prints nothign.
which is why i'm thinking that the cursor isn't bringing back the entire XML file.
April 23, 2009 at 6:22 am
What will you get, if you hard code @workinschema variable into Cursor only? (mean, dont take it out of cursor)
"Don't limit your challenges, challenge your limits"
April 23, 2009 at 6:40 am
Why do you need the cursor? Try to use the Sql2k5 XML functions:
DECLARE @source TABLE (Id INT, Data XML)
INSERT INTO @source
SELECT 1, 'value1'
UNION ALL SELECT 2, 'value2'
SELECT
Data.value('(root/code/text())[1]', 'varchar(20)')
FROM @source
FOR XML PATH('Item'), ROOT('Root')
Greets
Flo
April 23, 2009 at 7:13 am
thanks for your replys.
i've tried kruti's suggestion and just ran
'select..... and then the xml file in quotes and it doesn't work either.
this leads me to believe taht the problem is with the amount of data the actual table column can hold.
its an ntext (length 16).
so when i select the XML file from that column it may be the case that it just can't fit it all in so its only giving me what its got.
the tables on sqlserver 2000.
the SP is on 2008.
am i right in that thought?
because when i noticed that the text was being truncated within the actual table column i just thought that it was a visual thing and that it would actually bring back the whole lot if i did anything with it.
maximum characters per column is set to 8192 when you go to tools/options/results
April 23, 2009 at 7:34 am
i've done a count of how many characters its allowing in this column and it stops at 4000
April 27, 2009 at 8:06 am
problem solved.
the project has about 6 different stored procedures that run at different intervals. i had changed 5 of them from varchar(8000) to varchar(max) but i'd forgotton to change the SP taht initially inserts the data to the table .
dope :blush:
April 27, 2009 at 9:29 am
Happens... 🙂
Glad to hear that it works now!
Flo
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply