Table to XML using TSQL with facet grouping and counts

Question

I have a requirement to turn a table into XML with counts for values.

e.g.

Table
Id , Type, AnotherType
1, This, Widget
2, This, Fimble
3, That, Widget

I want the output something like this. This needs to be dynamic so new columns or facets added would generate the this type of output.

<matrix name="Type">
 <facet name = "this" count=2>
   <id value = 1></id>
   <id value = 2></id>
 </facet>     
 <facet name = "that" count=1>
   <id value = 3></id>
 </facet>
</matrix>
<matrix name="AnotherType">
 <facet name = "Widget" count=2>
   <id value = 1></id>
   <id value = 3></id>
 </facet>
 <facet name = "Fimble" count=1>
   <id value = 2></id>
 </facet>
</matrix>

Is this possible?

If the columns/values change, I'd like a solution that handles that - e.g it will generate a new matrix and facts dependant on the data.

Answer 1

If you have SQL Server 2005 or later you can do something like this.

Answer 2

Please take a look here: CTE and Hierarchical XML Result. You can ignore XSLT part and stick with XML alone.

EDIT: Seems it's a bit more trick. Please take a look here: SQL XML output to populate a treeview as it uses a pure TSQL CTE instruction to generate your hierarchical XML.

Answer 3

You can do this but you need to apply UNPIVOT command and dynamic SQL. It is better when you divide this into steps:

1) Your table can have various columns. What you should to first is to get the list of columns. All the columns are listed in sys.columns table. The following piece of code will get the list of columns and save it as comma delimited list. However, first column is stored separatly (becuase of the further purpose).

DECLARE @firstColumn NVARCHAR(256)
DECLARE @columnList NVARCHAR(MAX)

SELECT @firstColumn = name 
    FROM sys.columns 
    WHERE object_id = OBJECT_ID(@tableName) 
        AND column_id = 1
SET @columnList = N''

SELECT @columnList = @columnList + name + N',' 
    FROM sys.columns 
    WHERE object_id = OBJECT_ID(@tableName) 
        AND column_id > 1

SET @columnList = LEFT(@columnList,LEN(@columnList)-1)

2) You want to use UNPIVOT (documentation here) command to make the structure more "programmer friendly". We need the list of columns to do that. It can be done using dynamic SQL (example here). The following command does the job:

DECLARE @cmd NVARCHAR(MAX)

SET @cmd = N'INSERT INTO #unPivoted
    SELECT id, Matrixes, Facets 
    FROM 
        (SELECT ' + @firstColumn + N',' + @columnList + N' FROM ' + @tableName + N') p
    UNPIVOT
        (Facets FOR Matrixes IN(' + @columnList + N')) AS unpvt
    ORDER BY Matrixes,Facets,id'

EXEC sp_executesql @cmd

In your example it will generate result like this:

id | Matrixes    | Facets
-------------------------
2  | AnotherType | Fimble
1  | AnotherType | Widget
3  | AnotherType | Widget
3  | Type        | That
1  | Type        | This
2  | Type        | This

3) The last thing is the actual query. It is enough to use FOR XML AUTO command. You should however make some nested queries to make the structure look like you want:

SELECT matrix.name, facet.name, facet.id_count AS count, id.id AS value 
FROM 
    (SELECT DISTINCT Matrixes AS name FROM #unPivoted) matrix
INNER JOIN 
    (SELECT Matrixes AS matrix_name, Facets AS name, COUNT(id) AS id_count FROM #unPivoted GROUP BY Matrixes, Facets) facet 
    ON matrix.name = facet.matrix_name 
INNER JOIN 
    (SELECT Facets AS facet_name, id FROM #unPivoted) id 
    ON facet.name = id.facet_name
ORDER BY matrix.name, facet.name, id.id
FOR XML AUTO

And the stored procedure. I left all the PRINTs and SELECTs for you to see how does it work (if the description is not enought).

CREATE PROCEDURE usp_tableToXML
    @tableName NVARCHAR(256)
AS
BEGIN

SET NOCOUNT ON;

-- 1. Get columns from the table
DECLARE @firstColumn NVARCHAR(256)
DECLARE @columnList NVARCHAR(MAX)

SELECT @firstColumn = name 
FROM sys.columns 
WHERE object_id = OBJECT_ID(@tableName) 
AND column_id = 1

SET @columnList = N''

SELECT @columnList = @columnList + name + N',' 
FROM sys.columns 
WHERE object_id = OBJECT_ID(@tableName) 
AND column_id > 1

SET @columnList = LEFT(@columnList,LEN(@columnList)-1)

PRINT @firstColumn
PRINT @columnList

-- 2. Unpivot the table
CREATE TABLE #unPivoted
(
    id INT,
    Matrixes VARCHAR(30),
    Facets VARCHAR(30)
)

DECLARE @cmd NVARCHAR(MAX)

SET @cmd = N'INSERT INTO #unPivoted
SELECT id, Matrixes, Facets 
FROM 
    (SELECT ' + @firstColumn + N',' + @columnList + N' FROM ' + @tableName + N') p
UNPIVOT
    (Facets FOR Matrixes IN(' + @columnList + N')) AS unpvt
ORDER BY Matrixes,Facets,id'

PRINT @cmd

EXEC sp_executesql @cmd

SELECT * FROM #unPivoted

-- 3. The query
SELECT matrix.name, facet.name, facet.id_count AS count, id.id AS value 
FROM 
    (SELECT DISTINCT Matrixes AS name FROM #unPivoted) matrix
INNER JOIN 
    (SELECT Matrixes AS matrix_name, Facets AS name, COUNT(id) AS id_count FROM #unPivoted GROUP BY Matrixes, Facets) facet 
    ON matrix.name = facet.matrix_name 
INNER JOIN 
    (SELECT Facets AS facet_name, id FROM #unPivoted) id 
    ON facet.name = id.facet_name
ORDER BY matrix.name, facet.name, id.id
FOR XML AUTO

END
GO

EXEC usp_tableToXML 'Types'

Btw, I guess that you made a little mistake in your XML structure. Shouldn't it be like this (facet tag should be closed after ids?):

<matrix name="AnotherType">
  <facet name="Fimble" count="1">
    <id value="2" />
  </facet>
  <facet name="Widget" count="2">
    <id value="1" />
    <id value="3" />
  </facet>
</matrix>
<matrix name="Type">
  <facet name="That" count="1">
    <id value="3" />
  </facet>
  <facet name="This" count="2">
    <id value="1" />
    <id value="2" />
  </facet>
</matrix>