Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

ntext in a temporary table

Posted on 2014-02-24
6
Medium Priority
?
232 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 71

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 41

Author Comment

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

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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 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 41

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

609 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