Solved

ntext in a temporary table

Posted on 2014-02-24
6
217 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 69

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 69

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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

778 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