SQL Server : xml string to rows

Question

I'm trying to convert a string to rows using T-SQL. I've found some people using XML for this but I'm running into troubles.

The original record:

A new line seperated string of data

New  In Progress  Left Message  On Hold  Researching  Researching (2nd Level)  Researching (3rd Level)  Resolved  Positive  False Positive  Security Respond

Using the following statement converts this string into XML:

select 
    cast('<i>'+REPLACE(convert(varchar(max), list_items), CHAR(13) + CHAR(10),'</i><i>')+'</i>' as xml)
from 
    field
where 
    column_name = 'state' and table_name = 'sv_inquiry'

XML string:

<i>Unassigned</i><i>Assigned</i><i>Transferred</i><i>Accepted</i><i>Closed</i><i>Reactivated</i>

Now I would like to convert every 'i' node into a separate row. I've constructed the query below, but I can't get it working in the way that it returns all the rows...

select x.i.value('i[1]', 'varchar(30)')
from (
    select cast('<i>'+REPLACE(convert(varchar(max), list_items), CHAR(13) + CHAR(10),'</i><i>')+'</i>' as xml)
    from field
    where column_name='state' and table_name='sv_inquiry'
) x(i)

This will return

Unassigned

To be clear, when i change 'i[1]' into 'i[2]' it will return 'Assigned'. I've tried '.', this will return the whole string in a single record...

Rhys Jones · Accepted Answer · 2015-02-04 10:59:22Z

1

How about using the nodes method on an XML datatype.

declare @xml xml

set @xml = '<i>Unassigned</i><i>Assigned</i><i>Transferred</i><i>Accepted</i><i>Closed</i><i>Reactivated</i>'

select
    t.c.value('.', 'nvarchar(100)') as [Word]
from 
    @xml.nodes('/i') as t(c)

answered Feb 4, 2015 at 10:59

Rhys Jones

5,5361 gold badge25 silver badges44 bronze badges

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

2 Comments

Bas Over a year ago

Is it possible to put this in one statement. I tried calling the .nodes('/i') after the from, like this from (select ...).nodes('/i') x(i)

Andomar Over a year ago

@Bas: You can use cross apply for that, example in my answer. +1 for the nodes() function which is the central machinery for turning XML into rows.

Andomar · Accepted Answer · 2015-02-04 11:08:09Z

0

You can split a string into rows without XML, see for example the fnSplitString function at SQL Server Central.

Here's an example using the nodes() function of the xml type. I'm using a space as the delimiter because SQL Fiddle doesn't play well with line feeds:

select  node_column.value('.', 'varchar(max)')
from    (
        select  cast('<i>' + replace(list_items, ' ', '</i><i>') +
                    '</i>' as xml) xml_value
        from    field
        ) f
cross apply
        xml_value.nodes('/i') node_table(node_column);

Live example at SQL Fiddle.

answered Feb 4, 2015 at 11:08

Andomar

239k55 gold badges387 silver badges412 bronze badges

Collectives™ on Stack Overflow

SQL Server : xml string to rows

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