SELECT * FROM (
SELECT FormatID,Name,ParentID
,isnull(v.value('Font[1]/@Family', 'varchar(100)'),'*') AS [MyFont] 
,v.value('@Index', 'Int') AS [MyIndex] 
From Formats
CROSS APPLY Data.nodes('//Step') TempXML(v)
) AS SRC
PIVOT( Max(MyFont) FOR MyIndex IN (
[0], [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],
[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],
[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],
[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],
[41],[42],[43],[44],[45],[46]
)) AS PVT1 Order by FormatID

SELECT * FROM (
SELECT FormatID,Name,ParentID
,isnull(v.value('Font[1]/@Size', 'varchar(100)'),'*') AS [MyFont] 
,v.value('@Index', 'Int') AS [MyIndex] 
From Formats
CROSS APPLY Data.nodes('//Step') TempXML(v)
) AS SRC
PIVOT( Max(MyFont) FOR MyIndex IN (
[0], [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],
[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],
[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],
[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],
[41],[42],[43],[44],[45],[46]
)) AS PVT1 Order by FormatID


SELECT * FROM (
SELECT FormatID,Name,ParentID
,isnull(v.value('Font[1]/@Style', 'varchar(100)'),'*') AS [MyFont] 
,v.value('@Index', 'Int') AS [MyIndex] 
From Formats
CROSS APPLY Data.nodes('//Step') TempXML(v)
) AS SRC
PIVOT( Max(MyFont) FOR MyIndex IN (
[0], [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],
[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],
[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],
[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],
[41],[42],[43],[44],[45],[46]
)) AS PVT1 Order by FormatID




SELECT v.value('@Index', 'Int') AS [MyIndex] 
,v.value('@Type', 'varchar(100)') AS [MyType] 
,v.value('@ParentType', 'varchar(100)') AS [MyParent] 
From Formats
CROSS APPLY Data.nodes('//Step') TempXML(v)
WHERE FormatID=1

SELECT MyFont,Count(*) HowMany FROM (
SELECT v.value('../@Family', 'varchar(100)') MyFont
From Formats
CROSS APPLY Data.nodes('//Step/Font') TempXML(v)) T1
group by MyFont
order by MyFont

SELECT MySize,Count(*) HowMany FROM (
SELECT v.value('../@Size', 'varchar(100)') MySize
From Formats
CROSS APPLY Data.nodes('//Step/Font') TempXML(v)) T1
group by MySize
order by MySize

SELECT MyStyle,Count(*) HowMany FROM (
SELECT v.value('../@Style', 'varchar(100)') MyStyle
From Formats
CROSS APPLY Data.nodes('//Step/Font') TempXML(v)) T1
group by MyStyle
order by MyStyle

SELECT MyFont,Count(*) HowMany FROM (
SELECT v.value('@Family', 'varchar(100)') MyFont
From Formats
CROSS APPLY Data.nodes('//Font') TempXML(v)) T1
group by MyFont
order by MyFont

SELECT MySize,Count(*) HowMany FROM (
SELECT v.value('@Size', 'varchar(100)') MySize
From Formats
CROSS APPLY Data.nodes('//Font') TempXML(v)) T1
group by MySize
order by MySize

SELECT MyStyle,Count(*) HowMany FROM (
SELECT v.value('@Style', 'varchar(100)') MyStyle
From Formats
CROSS APPLY Data.nodes('//Font') TempXML(v)) T1
group by MyStyle
order by MyStyle