87 lines
3.4 KiB
SQL
87 lines
3.4 KiB
SQL
/*
|
|
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
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|