SQL error XML Parsing - Equal Expected

[bmacbmac]

Hello,

I have a table (Subs2) with the following data:

Textlist
S~U~BRANCH CREEK 19-28-3~1019-7~Y~N~~~~~~~~~~~~
S~U~13TH STREET SPORT PARK~13SSP~Y~N~~~~~~~~~~~~
S~U~77-SUNSET MOBILE HOME PARK - DOUGLASS~77SMHP~Y~N~~~~~~~~~~~~

I am trying to parse out the fields (with ~ as the delimiter) to individual columns into a table called Subs3. I am running this:

; WITH CTE AS  
( 
        SELECT    A.ID, 
                B.IND_ROW, 
                ROW_NUMBER() OVER ( PARTITION BY A.ID ORDER BY A.ID) AS ROW_NUM 
        FROM 
        ( 
                SELECT    *, 
                        CONVERT( XML, '<ROW>' + REPLACE( C.textlist, '~', '</ROW><ROW>' ) + '</ROW>' ) AS XML_ROW 
                FROM    subs2 C 
        ) A 
        CROSS APPLY 
        ( 
                SELECT    DATA.ROW.value('.', 'VARCHAR(1000)') AS IND_ROW 
                FROM    A.XML_ROW.nodes('ROW') AS DATA(ROW) 
        ) B 
) 

insert into subs3 
SELECT    ID, [1] AS COL_3, [2]  AS COL_2, [3]  AS COL_3, [4]  AS COL_4, [5]  AS COL_5, 
[6]  AS COL_6, [7]  AS COL_7, [8]  AS COL_8, [9]  AS COL_9, [10] AS COL_10,[11] AS COL_11,
[12] AS COL_12,[13] AS COL_13,[14] AS COL_14,[15] AS COL_15,[16] AS COL_16,[17] AS COL_17,
[18] AS COL_18

FROM  
( 
        SELECT    ID 
                , ROW_NUM 
                , IND_ROW  
        FROM    CTE 
)        AS SOURCE_TABLE 
PIVOT 
(     
        MAX(IND_ROW) FOR ROW_NUM IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
        [11], [12], [13], [14], [15], [16], [17], [18]) 
)        AS PIVOT_TABLE

An I'm getting an error:
Msg 9414, Level 16, State 1, Line 3
XML parsing: line 1, character 43, equal expected


Can anyone see what my issue is with my script?

Thanks!

 

[fredericofonseca]

you better off looking at an existing function.

http://www.sqlservercentral.com/articles/Tally+Table/72993/

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886