Error when converting data to XML in SQL Server

Question

I need to retrieve the content in position 10 of a comma separated string in a View table.

Row 1    N,l,S,T,A,,<all>,,N,A,N,N,N,Y,Y,,Y,Y,Y,,AA,SA,Enterprise, 
Row 2    M,,A,S,AS,SS,AS,N,N,N,N,Y,Y,Y,ENTERPRISE,S,,A
Row 3    L,,A,D,S,A,A,AA,Y,Y,Y,YNN,N,N,N,N,A,AA,AD,D,D

Div1 is the name of my column, Div2 is the name of the result column. I use the following code:

SELECT TOP (2000) 
    [Id],
    CONVERT(XML,'<x>' + REPLACE(REPLACE(REPLACE(Div1, '>', ''), '<', ''), ',', '</x <x>') + '</x>').value('/x[10]', 'VARCHAR(MAX)') [Div2], 
    Div1
FROM 
    [dbo].[database]

I use character type VARCHAR(MAX) because that is the type for Div1 in my database. The code works if I run less than 20000 rows. But the data set I use has more than 100,000 rows. If I run the whole data it stops and the following error occurs:

Msg 9421, Level 16, State 1, Line 1.
XML parsing: line 1, character 218, illegal name character

Is there a way to work this around?

To retrieve the content in position 10 of a comma separated string, you do not need to convert the string to xml. You can find lots of SQL samples to convert a csv string to resultset. — PeterHe
– PeterHe, Commented Sep 13, 2019 at 14:45
Thanks, my SQL Server Management Studio version is 15.0.18142.0 — db_noob
– db_noob, Commented Sep 17, 2019 at 8:01

Yitzhak Khabinsky · Accepted Answer · 2019-09-13 18:13:17Z

1

XML has CDATA[] section to treat content as-is without parsing. There is no need for multiple REPLACE() function calls. Check it out.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE  (ID INT IDENTITY(1,1) PRIMARY KEY, Div1 VARCHAR(MAX));
INSERT INTO @tbl (Div1)
VALUES
('N,l,S,T,A,,<all>,,N,A,N,N,N,Y,Y,,Y,Y,Y,,AA,SA,Enterprise')
, ('M,,A,S,AS,SS,AS,N,N,N,N,Y,Y,Y,ENTERPRISE,S,,A')
, ('L,,A,D,S,A,A,AA,Y,Y,Y,YNN,N,N,N,N,A,AA,AD,D,D');
-- DDL and sample data population, end

SELECT [Id],
    CAST('<x><![CDATA[' + REPLACE(Div1, ',', ']]></x><x><![CDATA[') + ']]></x>' AS XML).value('(/x/text())[10]', 'VARCHAR(MAX)') [Div2], 
    Div1
FROM @tbl;

edited Sep 13, 2019 at 18:13

answered Sep 13, 2019 at 17:54

Yitzhak Khabinsky

22.7k2 gold badges19 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gottfried Lesigang Over a year ago

Good answer, +1 form my side.

db_noob Over a year ago

It works with an slightly change at value() For some reason the value if I changed the position was not correct. My string is quite long, this was only an extract. Look: SELECT [LacNo], CAST('<x><![CDATA[' + REPLACE(Div1, ',', ']]></x><x><![CDATA[') + ']]></x>' AS XML).value('(/x)[10]', 'VARCHAR(MAX)') [Div2], Div1 FROM dbo.database;

PeterHe · Accepted Answer · 2019-09-13 14:54:09Z

You can create a function to split string like below:

CREATE FUNCTION dbo.split_delimited_string
(
    @list varchar(max),
    @delimiter varchar(5)
)  
RETURNS @items TABLE 
(
    pos_id int identity(1,1),
    item varchar(255)
) 
AS  
BEGIN 
    DECLARE @pos int, @delimiter_len tinyint;
    SET @pos = CHARINDEX(@delimiter,@list);
    SET @delimiter_len=LEN(@delimiter);
    WHILE (@pos>0)
    BEGIN
        INSERT INTO @items (item)
        SELECT LEFT(@list,@pos - 1)

        SET @list = RIGHT(@list,LEN(@list) - @pos - @delimiter_len + 1);
        SET @pos = CHARINDEX(@delimiter,@list);
    END 
    IF @list<>N''
    BEGIN
        INSERT INTO @items (item)
        SELECT @list;
    END 
    RETURN;
END

The following query will return the content in the 10th position:

SELECT
    t.[Id],
    l.item AS Div2
    t.Div1
FROM [dbo].[database] t
CROSS APPLY dbo.split_delimited_string(t.Div1,',') l
WHERE l.pos_id = 10;

Collectives™ on Stack Overflow

Error when converting data to XML in SQL Server

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related