Help with parsing XML 8000 character limit 1

[bmacbmac]

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:
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:

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!

 

[fredericofonseca]

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+Table/72993/

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

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[bmacbmac]

This worked wayyyy better!

Thanks!

 

[Andrzejek]

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.