How to Convert XML Rows to Columns

Question

I have seen a few answers on how to convert rows to columns but they are rather specific to the questions and are difficult for me to transpose into my own solution.

The data starts out as a varchar, but I convert it to XML because I thought it would be easier to convert it to columns that way.

-- get xml
DECLARE @x XML

SET @x = '<ul><li>Gas grill rotisserie</li><li>Fits the Genesis E-300 gas grill</li><li>Fits the Genesis S-300 gas grill</li><li>Includes a heavy-duty electric motor</li><li>Counterbalance for smooth turning and less motor wear</li></ul>'

SELECT x.r.value('node()[1]','varchar(200)')
FROM @x.nodes('/ul/li') AS x(r)

This returns a results like the following; however, I now need to convert each row into a column.

I have tried variations using pivot and dynamic SQL, but haven't gotten very far. How can I convert each row to a column (when the number of rows will be unknown).

Ref. Convert Rows to columns using 'Pivot' in SQL Server Ref. How to convert row values to columns with dynamic columns count?

SQL is for ***structured*** data. That involves knowing the number of columns, their names and data types. The database engines rely on that. Trying to do what you've described breaks the SQL Paradigm. Perhaps you're working in the wrong language? Or, perhaps, there is a better pattern for trying to solve your actual problem? Tell us why you want to do this, what you're going to do with the data, and perhaps we can direct you. But, right now, it looks like you're trying to force yourself down a blind alley. — MatBailie, Oct 03 '18 at 14:27
While I totally agree this looks like sql might not be the best way to handle your data this is absolutely doable. You need to search for dynamic pivot or dynamic crosstab. It has been asked and answered hundreds of times. In fact the second link in your question has the details of exactly how to do this. — Sean Lange, Oct 03 '18 at 14:31

score 1 · Answer 1 · answered Oct 03 '18 at 15:45

I was SOreadytohelp myself this time :) ... The following query receives the HTML, converts it to XML, defines the column names and writes the dynamic SQL before executing it.

The end result is:

DECLARE @x XML,
 @limit int = 4,
 @ItemId NVARCHAR(10) = '11158',
 @cols AS NVARCHAR(MAX),
 @query  AS NVARCHAR(MAX)

-- get xml
SELECT @x = '<ul><li>Gas grill rotisserie</li><li>Fits the Genesis E-300 gas grill</li><li>Fits the Genesis S-300 gas grill</li><li>Includes a heavy-duty electric motor</li><li>Counterbalance for smooth turning and less motor wear</li></ul>'

-- convert rows to columns
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(name) 
                    from
                    (
                      SELECT top (@limit)
                        bar.value('local-name(.)','VARCHAR(12)') + cast(row_number() over(order by bar.value('./.','VARCHAR(10)') asc) as varchar(10)) as name,  
                        bar.value('./.','VARCHAR(255)') as value 
                        FROM
                        @x.nodes('/ul/*') AS foo(bar) 
                    ) d
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

-- create dynamic sql
set @query = '
            -- get xml
                DECLARE @x XML
                SELECT @x = ''<ul><li>Gas grill rotisserie</li><li>Fits the Genesis E-300 gas grill</li><li>Fits the Genesis S-300 gas grill</li><li>Includes a heavy-duty electric motor</li><li>Counterbalance for smooth turning and less motor wear</li></ul>''
            SELECT ' + @cols + ' 
             from 
             (
               SELECT
                bar.value(''local-name(.)'',''VARCHAR(12)'') + cast(row_number() over(order by bar.value(''./.'',''VARCHAR(10)'') asc) as varchar(10)) as name,  
                bar.value(''./.'',''VARCHAR(255)'') as value 
                FROM
                @x.nodes(''/ul/*'') AS foo(bar) 
            ) x
            pivot 
            (
                max(value)
                for name in (' + @cols + ')
            ) p '

execute sp_executesql @query;

How to Convert XML Rows to Columns

1 Answers1