Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 237
  • Last Modified:

ntext in a temporary table

Hi All,

I have an XML string that is stored as an ntext column on a 3rd party DB. When running a query against that DB I'm putting some values into a temporary table.

so:

select <col1>, <col2> ... into #temp from myTable

when I do a select * from #temp, I'm noticing that my XML column doesn't return the full string.  However select max(len(myXml)) from #temp returns it's invalid for an ntext type.  How do I see / get the full XML of this when it's passed 8000 characters?

Thanks.
0
Kyle Abrahams
Asked:
Kyle Abrahams
  • 2
  • 2
  • 2
1 Solution
 
QlemoC++ DeveloperCommented:
Depending on the tool you use, you'll only see a part of the string. Nevertheless the next content will be retrieved correctly to up to 4000(!) Unicode chars if stored into a variable.
0
 
Kyle AbrahamsSenior .Net DeveloperAuthor Commented:
Qlemo . . . so how do I get the full text?
0
 
QlemoC++ DeveloperCommented:
In which tool? Management Studio and Query Analyzer both have a setting to limit the max. amount of characters. Just change that.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Anthony PerkinsCommented:
Try it this way:
SELECT  MAX(DATALENGTH(myXml)) / 2
FROM    #temp

If you are using SSMS or Query Analyzer and you have more characters in the column than supported by the tool then you are going to have to SUBSTRING() it to death,  Let me know if you need details.

Incidentally, I realize this is a third party tool, however ntext is a deprecated data type.
0
 
Kyle AbrahamsSenior .Net DeveloperAuthor Commented:
Hi Anthony,

92477 is the result.  It's good to know I can substring if need be.  
I just tried the cast(myxml as xml) and got the full result  back on the 92477 field, so at least it'll process the whole thing.

Thanks very much.
0
 
Anthony PerkinsCommented:
Right, when returning data to the grid you can only view up to 64Kb "non Xml data"  for Xml data apparently it is unlimited.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now