RE: XML data retrieval – SQLServerCentral

SSCrazy

Points: 2649

February 11, 2014 at 10:30 am

You are interested in pivoting the values for all nodes positioned at specific number. If we extract the text an enumerate the rows then the numbers greater 4, 5 and 6 correspond to (Hostname, DBInstanceName, DatabaseName) and you have a block every 13 rows.

We have to identify the blocks "(rn - 1) / 13" and pivot the data.

SET NOCOUNT ON;

USE tempdb;

GO

DECLARE @x xml = N'<?xml version="1.0"?>

<?mso-application progid="Excel.Sheet"?>

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"

xmlns:o="urn:schemas-microsoft-com:office:office"

xmlns:x="urn:schemas-microsoft-com:office:excel"

xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

xmlns:html="http://www.w3.org/TR/REC-html40">

<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">

<Author>Author_of_XML</Author>

<LastAuthor>Author_of_XML</LastAuthor>

<Version>1.00</Version>

</DocumentProperties>

<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">

<DownloadComponents/>

<LocationOfComponents HRef="/"/>

</OfficeDocumentSettings>

<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">

<WindowHeight>13020</WindowHeight>

<WindowWidth>28800</WindowWidth>

<WindowTopX>0</WindowTopX>

<WindowTopY>0</WindowTopY>

<ProtectStructure>False</ProtectStructure>

<ProtectWindows>False</ProtectWindows>

</ExcelWorkbook>

<Styles>

<Style ss:ID="Default" ss:Name="Normal">

<Alignment ss:Vertical="Bottom"/>

<Borders/>

<Font ss:FontName="Arial"/>

<Interior ss:Color="gray" ss:Pattern="Solid"/>

<NumberFormat/>

<Protection/>

</Style>

<Style ss:ID="s63" ss:Name="TableHeader">

<Borders>

<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"

ss:Color="#990000"/>

<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"

ss:Color="#990000"/>

<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"

ss:Color="#990000"/>

<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"

ss:Color="#990000"/>

</Borders>

<Font ss:FontName="Arial" ss:Color="#000000" ss:Bold="1"/>

<Interior ss:Color="#EADEC2" ss:Pattern="Solid"/>

</Style>

<Style ss:ID="s64" ss:Name="TableValues">

<Borders>

<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"

ss:Color="#000000"/>

<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"

ss:Color="#000000"/>

<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"

ss:Color="#000000"/>

<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"

ss:Color="#000000"/>

</Borders>

<Font ss:FontName="Arial"/>

<Interior ss:Color="#F9F9F9" ss:Pattern="Solid"/>

</Style>

<Style ss:ID="s62" ss:Name="TopHeader">

<Alignment ss:Vertical="Center"/>

<Borders>

<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"

ss:Color="#FFFFFF"/>

<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"

ss:Color="#FFFFFF"/>

</Borders>

<Font ss:FontName="Arial" ss:Color="#FFFFFF" ss:Bold="1"/>

<Interior ss:Color="#990000" ss:Pattern="Solid"/>

</Style>

<Style ss:ID="m206143808" ss:Parent="s62">

<Alignment ss:Vertical="Center"/>

<Borders>

<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"

ss:Color="#FFFFFF"/>

<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"

ss:Color="#FFFFFF"/>

</Borders>

<Font ss:FontName="Arial" ss:Color="#FFFFFF" ss:Bold="1"/>

<Interior ss:Color="#990000" ss:Pattern="Solid"/>

</Style>

<Style ss:ID="m206143828" ss:Parent="s62">

<Alignment ss:Vertical="Center"/>

<Borders>

<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"

ss:Color="#FFFFFF"/>

<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"

ss:Color="#FFFFFF"/>

</Borders>

<Font ss:FontName="Arial" ss:Color="#FFFFFF" ss:Bold="1"/>

<Interior ss:Color="#990000" ss:Pattern="Solid"/>

</Style>

</Styles>

<Worksheet ss:Name="DatabaseInfo">

<Table ss:ExpandedColumnCount="15" x:FullColumns="1" x:FullRows="1">

<ss:Column ss:AutoFitWidth="1" ss:Width="60"/>

<ss:Column ss:AutoFitWidth="1" ss:Width="72"/>

<ss:Column ss:AutoFitWidth="1" ss:Width="126"/>

<ss:Column ss:AutoFitWidth="1" ss:Width="108"/>

<ss:Column ss:AutoFitWidth="1" ss:Width="54"/>

<ss:Column ss:AutoFitWidth="1" ss:Width="81"/>

<ss:Column ss:AutoFitWidth="1" ss:Width="72"/>

<ss:Column ss:AutoFitWidth="1" ss:Width="63"/>

<ss:Column ss:AutoFitWidth="1" ss:Width="90"/>

<ss:Column ss:AutoFitWidth="1" ss:Width="81"/>

<ss:Column ss:AutoFitWidth="1" ss:Width="108"/>

<Row ss:AutoFitHeight="0" ss:Height="20.0625" ss:StyleID="s62">

<Cell ss:MergeAcross="14" ss:StyleID="m206143808"><Data ss:Type="String">Database Info</Data></Cell>

</Row>

<Row ss:AutoFitHeight="0" ss:Height="20.0625" ss:StyleID="s62">

<Cell ss:MergeAcross="14" ss:StyleID="m206143828"><Data ss:Type="String">Generated by ...... on ......</Data></Cell>

</Row>

<Row ss:StyleID="s63">

<Cell><Data ss:Type="String">DatabaseID</Data></Cell>

<Cell><Data ss:Type="String">Hostname</Data></Cell>

<Cell><Data ss:Type="String">DBInstanceName</Data></Cell>

<Cell><Data ss:Type="String">DatabaseName</Data></Cell>

<Cell><Data ss:Type="String">Status</Data></Cell>

<Cell><Data ss:Type="String">Collation</Data></Cell>

<Cell><Data ss:Type="String">Recovery</Data></Cell>

<Cell><Data ss:Type="String">TotalMBs</Data></Cell>

<Cell><Data ss:Type="String">UsedMBs</Data></Cell>

<Cell><Data ss:Type="String">FreeMBs</Data></Cell>

<Cell><Data ss:Type="String">LogSizeMBs</Data></Cell>

<Cell><Data ss:Type="String">LogUsage%</Data></Cell>

<Cell><Data ss:Type="String">LastScanTime</Data></Cell>

</Row>

<Row ss:StyleID="s64">

<Cell><Data ss:Type="Number">12</Data></Cell>

<Cell><Data ss:Type="String">Hostname1</Data></Cell>

<Cell><Data ss:Type="String">Instancename1</Data></Cell>

<Cell><Data ss:Type="String">database1</Data></Cell>

<Cell><Data ss:Type="String">ONLINE</Data></Cell>

<Cell><Data ss:Type="String">Latin1_General_CI_AS</Data></Cell>

<Cell><Data ss:Type="String">SIMPLE</Data></Cell>

<Cell><Data ss:Type="String">4.44</Data></Cell>

<Cell><Data ss:Type="String">4.06</Data></Cell>

<Cell><Data ss:Type="String">0.38</Data></Cell>

<Cell><Data ss:Type="String">1.49</Data></Cell>

<Cell><Data ss:Type="String">45.55</Data></Cell>

<Cell><Data ss:Type="String">10/02/2012 07:43:26</Data></Cell>

</Row>

<Row ss:StyleID="s64">

<Cell><Data ss:Type="Number">13</Data></Cell>

<Cell><Data ss:Type="String">Hostname1</Data></Cell>

<Cell><Data ss:Type="String">Instancename1</Data></Cell>

<Cell><Data ss:Type="String">Database2</Data></Cell>

<Cell><Data ss:Type="String">ONLINE</Data></Cell>

<Cell><Data ss:Type="String">Latin1_General_CI_AS</Data></Cell>

<Cell><Data ss:Type="String">SIMPLE</Data></Cell>

<Cell><Data ss:Type="String">406.63</Data></Cell>

<Cell><Data ss:Type="String">10.5</Data></Cell>

<Cell><Data ss:Type="String">396.13</Data></Cell>

<Cell><Data ss:Type="String">611.12</Data></Cell>

<Cell><Data ss:Type="String">13.42</Data></Cell>

<Cell><Data ss:Type="String">10/02/2012 07:43:26</Data></Cell>

</Row>

<Row ss:StyleID="s64">

<Cell><Data ss:Type="Number">14</Data></Cell>

<Cell><Data ss:Type="String">Hostname2</Data></Cell>

<Cell><Data ss:Type="String">Instancename2</Data></Cell>

<Cell><Data ss:Type="String">database1</Data></Cell>

<Cell><Data ss:Type="String">ONLINE</Data></Cell>

<Cell><Data ss:Type="String">Latin1_General_CI_AS</Data></Cell>

<Cell><Data ss:Type="String">FULL</Data></Cell>

<Cell><Data ss:Type="String">2.19</Data></Cell>

<Cell><Data ss:Type="String">1.38</Data></Cell>

<Cell><Data ss:Type="String">0.81</Data></Cell>

<Cell><Data ss:Type="String">2.49</Data></Cell>

<Cell><Data ss:Type="String">37.93</Data></Cell>

<Cell><Data ss:Type="String">10/02/2012 07:43:26</Data></Cell>

</Row>

<Row ss:StyleID="s64">

<Cell><Data ss:Type="Number">14</Data></Cell>

<Cell><Data ss:Type="String">Hostname2</Data></Cell>

<Cell><Data ss:Type="String">Instancename2</Data></Cell>

<Cell><Data ss:Type="String">database2</Data></Cell>

<Cell><Data ss:Type="String">ONLINE</Data></Cell>

<Cell><Data ss:Type="String">Latin1_General_CI_AS</Data></Cell>

<Cell><Data ss:Type="String">SIMPLE</Data></Cell>

<Cell><Data ss:Type="String">984.38</Data></Cell>

<Cell><Data ss:Type="String">922.94</Data></Cell>

<Cell><Data ss:Type="String">61.44</Data></Cell>

<Cell><Data ss:Type="String">19.62</Data></Cell>

<Cell><Data ss:Type="String">37</Data></Cell>

<Cell><Data ss:Type="String">10/02/2012 07:43:26</Data></Cell>

</Row>

<Row ss:StyleID="s63">

<Cell><Data ss:Type="String"></Data></Cell>

</Row>

</Table>

<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">

<ProtectObjects>False</ProtectObjects>

<ProtectScenarios>False</ProtectScenarios>

</WorksheetOptions>

</Worksheet>

</Workbook>';

WITH XMLNAMESPACES (

DEFAULT 'urn:schemas-microsoft-com:office:spreadsheet',

'urn:schemas-microsoft-com:office:spreadsheet' AS ss

)

, C1 AS (

SELECT

DENSE_RANK() OVER(PARTITION BY n1.t ORDER BY n1.t) AS TID,

ROW_NUMBER() OVER(PARTITION BY n1.t ORDER BY n2.d) AS rn,

n2.d.value('text()[1]', 'varchar(128)') AS val

FROM

@x.nodes('Workbook/Worksheet/Table') AS n1(t)

CROSS APPLY

n1.t.nodes('Row/Cell/Data') AS n2(d)

)

, C2 AS (

SELECT

TID,

(rn - 1) / 13 AS RID,

rn % 13 AS CID,

val

FROM

C1

WHERE

rn > 6

AND rn % 13 IN (4, 5, 6)

)

SELECT

TID,

RID,

[4] AS [Hostname],

[5] AS [DBInstanceName],

[6] AS [DatabaseName]

FROM

C2

PIVOT

(

MAX(val)

FOR CID IN ([4], [5], [6])

) AS P;

GO