Select ALL the text from a text data type

• andegre

  SSCertifiable

  Points: 5875

  More actions

  February 17, 2012 at 9:07 am

  #255923

  I have a column that is a text data type that contains an xml document. The length of the data in the field is about 55000 characters. I have a special query that I found somewhere to be able to retrieve ALL of the data, but it's not working for one of my records. It works when the length is around 20000 though. The current script is only returning 43679 of the characters out of the ~55000.

  Here's the code, how can I modify it to get everything?

  Declare @ptr varbinary(16),

  @length int,

  @Search varchar(200),

  @ID Int

  -- Batch Print version (DL)

  Set@ID = 46633086

  Select@length = DataLength(CorrespondenceFO) From IARTS..CorrespondenceLog Where CorrespondenceLogID = @ID

  Select@ptr = TextPtr(CorrespondenceFO) From IARTS..CorrespondenceLog Where CorrespondenceLogID = @ID

  Select @ID, @length, @ptr

  If (@ptr IS NOT NULL)

  ReadText IARTS..CorrespondenceLog.CorrespondenceFO @ptr 0 @length

• LutzM

  SSC Guru

  Points: 107049

  More actions

  February 17, 2012 at 10:44 am

  #1448655

  Would it help to convert it to xml instead of dealing with the text data type (which is deprecated anyway, so you should consider using xml or varchar() instead...)

  Declare @ID Int

  -- Batch Print version (DL)

  Set@ID = 46633086

  Selectcast(CorrespondenceFO as xml) From IARTS..CorrespondenceLog Where CorrespondenceLogID = @ID

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• andegre

  SSCertifiable

  Points: 5875

  More actions

  February 17, 2012 at 11:26 am

  #1448690

  Thanks Lutz.

  I tried that and got this as an error:

  Msg 9402, Level 16, State 1, Line 20

  XML parsing: line 1, character 39, unable to switch the encoding

  Is it because this is xslfo, not strictly xml?

  Here's the first chunk of data in the field:

  <?xml version="1.0" encoding="utf-16"?><fo:root xmlns:fo="http://www.w3.org/1999/XSL/Format"><fo:layout-master-set><fo:simple-page-master page-height="11in" page-width="8.5in" margin-top="0.50in" margin-left="0.8in" margin-right="0.8in" margin-bottom="0.25in" master-name="PageMaster">

  Looks like it's failing at the start of the first namespace declaration tag...

• andegre

  SSCertifiable

  Points: 5875

  More actions

  February 17, 2012 at 11:29 am

  #1448693

  There we go! It looks like this worked:

  Set @ID = 46633086

  Select cast(Cast(CorrespondenceFO as ntext) as xml) From IARTS..CorrespondenceLog Where CorrespondenceLogID = @ID

  Thanks Lutz!!!