INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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!

*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.

Jobs

Help with parsing XML 8000 character limit

Help with parsing XML 8000 character limit

(OP)
Hi Everyone,

I have a table

CODE

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:
00001.TIF|00002.TIF|00003.TIF|00004.TIF
00005.TIF|00006.TIF

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:

CODE

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?

Thanks!

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.

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

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

(OP)
This worked wayyyy better!

Thanks!



RE: Help with parsing XML 8000 character limit

bmacbmac,

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!

Resources

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