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


SELECT FormatID,Name,Description,ParentID, v.query('../..') parentNode
,isnull(v.value('@TabFormat', 'varchar(100)'),'*') AS [MyFormat] 
,v.value('@Index', 'Int') AS [MyIndex] 
From Formats
CROSS APPLY Data.nodes('//SeqTabFmt') TempXML(v)

select * from formats