Solved

ntext in a temporary table

Posted on 2014-02-24
6
226 Views
Last Modified: 2014-02-25
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
Comment
Question by:Kyle Abrahams
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 70

Expert Comment

by:Qlemo
ID: 39883718
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
 
LVL 40

Author Comment

by:Kyle Abrahams
ID: 39883722
Qlemo . . . so how do I get the full text?
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 39883791
In which tool? Management Studio and Query Analyzer both have a setting to limit the max. amount of characters. Just change that.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 39884521
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
 
LVL 40

Author Comment

by:Kyle Abrahams
ID: 39885869
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39887652
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question