Solved

ntext in a temporary table

Posted on 2014-02-24
6
210 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
  • 2
  • 2
  • 2
6 Comments
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
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 39

Author Comment

by:Kyle Abrahams
Comment Utility
Qlemo . . . so how do I get the full text?
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
In which tool? Management Studio and Query Analyzer both have a setting to limit the max. amount of characters. Just change that.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
Comment Utility
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 39

Author Comment

by:Kyle Abrahams
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
mySql Syntax 7 26
ssms - object execution statistics 12 37
Date conversion in sql server 2012 6 25
Retention Policy for Backups 1 12
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now