/*
SELECT max(v.value('@Index', 'varchar(100)')) AS [MaxIndex] From Formats 
CROSS APPLY  Data.nodes('//DocStyle') TempXML(v)

SELECT FormatID, Name,v.value('@Index', 'varchar(100)') AS [Index] From Formats 
CROSS APPLY  Data.nodes('//CheckOffHeader') TempXML(v)

SELECT * FROM (SELECT FormatID,Name,v.value('@Index', 'varchar(100)') AS [MyIndex] From Formats
CROSS APPLY Data.nodes('//CheckOff') TempXML(v)) AS SRC
PIVOT( COUNT(MyIndex) 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],[47],[48],[49],[50]
)) AS PVT Order by FormatID

*/
SELECT * FROM (SELECT FormatID,Name,ParentID,v.value('@Index', 'varchar(100)') AS [MyIndex] From Formats
CROSS APPLY Data.nodes('//DocStyle') TempXML(v)) AS SRC
PIVOT( COUNT(MyIndex) FOR MyIndex IN (
[0], [1], [2], [3], [4], [5], [6], [7], [8], [9]
)) AS PVT Order by FormatID

SELECT * FROM (SELECT FormatID,Name,ParentID,v.value('@Index', 'varchar(100)') AS [MyIndex] From Formats
CROSS APPLY Data.nodes('//PageStyle') TempXML(v)) AS SRC
PIVOT( COUNT(MyIndex) FOR MyIndex IN (
[0], [1], [2], [3], [4], [5], [6], [7], [8], [9]
)) AS PVT Order by FormatID

SELECT * FROM (SELECT FormatID,Name,ParentID,v.value('@Index', 'varchar(100)') AS [MyIndex] From Formats
CROSS APPLY Data.nodes('//RightCheckOffBox') TempXML(v)) AS SRC
PIVOT( COUNT(MyIndex) FOR MyIndex IN (
[0], [1], [2], [3]
)) AS PVT Order by FormatID

SELECT FormatID, Name,ParentID,v.value('@Index', 'varchar(100)') AS [Index] From Formats 
CROSS APPLY  Data.nodes('//CheckOffHeader') TempXML(v)

SELECT * FROM (SELECT FormatID,Name,ParentID,v.value('@Index', 'varchar(100)') AS [MyIndex] From Formats
CROSS APPLY Data.nodes('//MetaSection') TempXML(v)) AS SRC
PIVOT( COUNT(MyIndex) FOR MyIndex IN (
[0], [1], [2], [3]
)) AS PVT Order by FormatID


SELECT * FROM (SELECT FormatID,Name,ParentID,v.value('@Index', 'varchar(100)') AS [MyIndex] From Formats
CROSS APPLY Data.nodes('//CheckOff') TempXML(v)) AS SRC
PIVOT( COUNT(MyIndex) FOR MyIndex IN (
[0],[1],[2],[3],[4],[5],[6],[7],[8],[9]
)) AS PVT Order by FormatID

SELECT * FROM (SELECT FormatID,Name,ParentID,v.value('@Index', 'varchar(100)') AS [MyIndex] From Formats
CROSS APPLY Data.nodes('//Step') TempXML(v)) AS SRC
PIVOT( COUNT(MyIndex) 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 PVT Order by FormatID

SELECT * FROM (SELECT FormatID,Name,ParentID,v.value('@Index', 'varchar(100)') AS [MyIndex] From Formats
CROSS APPLY Data.nodes('//SeqTabFmt') TempXML(v)) AS SRC
PIVOT( COUNT(MyIndex) FOR MyIndex IN
([0],[1],[2],[3],[4],[5],[6],[7],[8])
) AS PVT Order by FormatID

SELECT * FROM (SELECT FormatID,Name,ParentID,v.value('@Index', 'varchar(100)') AS [MyIndex] From Formats
CROSS APPLY Data.nodes('//Box') TempXML(v)) AS SRC
PIVOT( COUNT(MyIndex) FOR MyIndex IN
([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10])
) AS PVT Order by FormatID

SELECT * FROM (SELECT FormatID,Name,ParentID,v.value('@Index', 'varchar(100)') AS [MyIndex] From Formats
CROSS APPLY Data.nodes('//TransTypes') TempXML(v)) AS SRC
PIVOT( COUNT(MyIndex) FOR MyIndex IN
([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14])
) AS PVT Order by FormatID