Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Help with parsing XML 8000 character limit

Help with parsing XML 8000 character limit

Help with parsing XML 8000 character limit

Hi Everyone,

I have a table


create table mytable (counter int, Images varchar(8000)) 

The counter column just contains a numerical counter. The Images column contains a pipe delimited list of file names:

I am trying to spit the images row into individual rows. For example
Counter, ImageList
1, 00001.TIF
1, 00002.TIF
1, 00003.TIF
1, 00004.TIF
2, 00005.TIF
2, 00006.TIF

Someone helped me work up the following script to split this out... and it works great. But I have one row where the length of the images column is 7071 characters. When I run the following script:


SELECT A.[counter],  
     Split.a.value('.', 'VARCHAR(max)') AS imageslist
 FROM  (SELECT [counter],  
         CAST ('<M>' + REPLACE([images], '|', '</M><M>') + '</M>' AS XML) AS images
     FROM  Mytable) AS A CROSS APPLY images.nodes ('/M') AS Split(a); 

I get an error:
Msg 9400, Level 16, State 1, Line 1
XML parsing: line 1, character 8000, unexpected end of input

Is there a way I can get through the 8000 character limit or is there maybe another way I can go to split these out?


RE: Help with parsing XML 8000 character limit

as it seems you are only looking to split the contents into rows then look at the function DelimitedSplit8k at http://www.sqlservercentral.com/articles/Tally+Tab...

It will work quite fast for that size of strings and without the need for XML processing which is always quite slow.

if for some weird reason you are unable to create functions on your environment it is possible to change the contents of the function mentioned above to be inline - just a bit more work required.


Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Help with parsing XML 8000 character limit

This worked wayyyy better!


RE: Help with parsing XML 8000 character limit


Don't you think Frederico deserves a Star for his help?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close