Search code examples
sql-serverxmlstringt-sqlcasting

T-SQL Splitting String field by semi colon produces error XML parsing: line 1, character 49, illegal name character


I'm using SQL Server 2014. I have addresses stored like this as one large string in a column called addr in a table tblAddress (I have no control over this it is from a 3rd party supplier)

addr
--------------------------------------------------
1 Smith Street;My Old Town; Someplace;Pluto;
5 SmokeyStreet;Someoldtown;Someotherplace;;
7 Grove Hill; Someolder town;Someotherplace;Earth

I would like to extract it so it looks like the below split into 4 columns:

Addr1            Addr2           Addr3            Addr4
----------------------------------------------------------
1 Smith Street   My Old Town     Someplace        Pluto
5 SmokeyStreet   Someoldtown     Someotherplace
7 Grove Hill     Someolder town  Someotherplace   Earth

I've tried:

SELECT  
    CAST('<x>' + REPLACE([addr], ';', '</x><x>') + '</x>' AS XML).value('/x[1]', 'nvarchar(50)') AS Addr1,
    CAST('<x>' + REPLACE([addr], ';', '</x><x>') + '</x>' AS XML).value('/x[2]', 'nvarchar(50)') AS Addr2,
    CAST('<x>' + REPLACE([addr], ';', '</x><x>') + '</x>' AS XML).value('/x[3]', 'nvarchar(50)') AS Addr3,
    CAST('<x>' + REPLACE([addr], ';', '</x><x>') + '</x>' AS XML).value('/x[4]', 'nvarchar(50)') AS Addr4
FROM
    tblAddress;

But I keep getting an error:

XML parsing: line 1, character 49, illegal name character

Where am I going wrong?


Solution

  • Rather than repeating calculations, you should consider using a CROSS APPLY once

    Example

    Select Pos1 = xDim.value('/x[1]' ,'varchar(150)')
          ,Pos2 = xDim.value('/x[2]' ,'varchar(150)')
          ,Pos3 = xDim.value('/x[3]' ,'varchar(150)')
          ,Pos4 = xDim.value('/x[4]' ,'varchar(150)')
     From  YourTable A
     Cross Apply ( values (cast('<x>' + replace((Select replace(A.addr,';','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml)) )B(xDim)
    

    Results

    Pos1             Pos2           Pos3            Pos4
    1 Smith Street   My Old Town    Someplace       Pluto
    5 SmokeyStreet   Someoldtown    Someotherplace  
    7 Grove Hill     Someolder town Someotherplace  Earth