March 24, 2010 at 2:58 pm
Hello, I'm trying to pivot the following type of document get a listing of disk volume information. A sample document looks like this (I can't alter the document as it's third-party generated).
<ADKMessage>
<c n="freekb" tn="3" t="1269017107034">
<v>5.9032724E7</v>
<v>5.28566688E8</v>
</c>
<c n="usedbytes" tn="3" t="1269017107034">
<v>8.5711499264E10</v>
<v>5.7624068096E10</v>
</c>
<c n="usedkb" tn="3" t="1269017107034">
<v>8.3702636E7</v>
<v>5.6273504E7</v>
</c>
<c n="_errorcode" tn="2" t="1269017107034" />
<c n="totalmb" tn="3" t="1269017107034">
<v>139390.0</v>
<v>571133.0</v>
</c>
<c n="totalkb" tn="3" t="1269017107034">
<v>1.4273536E8</v>
<v>5.84840192E8</v>
</c>
<c n="freepct" tn="3" t="1269017107034">
<v>41.3581638074826</v>
<v>90.37796909826608</v>
</c>
<c n="name" tn="1" t="1269017107034" kc="true">
<v>C:</v>
<v>D:</v>
</c>
<c n="_executetime" tn="2" t="1269017107034">
<v>157</v>
</c>
<c n="freebytes" tn="3" t="1269017107034">
<v>6.0449509E10</v>
<v>5.41252289E11</v>
</c>
<c n="totalbytes" tn="3" t="1269017107034">
<v>1.46161009E11</v>
<v>5.9887636E11</v>
</c>
<c n="freemb" tn="3" t="1269017107034">
<v>57649.14453125</v>
<v>516178.40625</v>
</c>
<c n="usedpct" tn="3" t="1269017107034">
<v>58.6418361925174</v>
<v>9.622030901733922</v>
</c>
<c n="_errortext" tn="9" t="1269017107034" />
<c n="_exit" tn="2" t="1269017107034">
<v>0</v>
</c>
<c n="volume" tn="1" t="1269017107034">
<v>\\?\Volume{2f989d14-761c-11de-b235-806e6f6e6963}\</v>
<v>\\?\Volume{201d7855-77b1-11de-896d-001517bcca37}\</v>
</c>
<c n="usedmb" tn="3" t="1269017107034">
<v>81740.85546875</v>
<v>54954.59375</v>
</c>
</ADKMessage>
Each <c> record represents a logical disk property while the <v> elements are the actual values for each of the disks.
I'm trying to use the drive letter (found in the record where the n attribute=name) as the fixed column in a pivot table, the remaining attributes as the pivot columns and the V values as the data values (using MAX as the aggregate). I've tried with OPENXML in conjuction with RANK, PARTITION and ROW_NUMBER(), but fail as there is no distinct record # to use. The V elements should be the correct place for them but they don't have them.
Is there a function where I can generate a row or element # in the XQUERY call that would be used to be the fixed column for the outer pivot call.
My best effort is this...
SELECT
RowNum,
[Name],
[FreeMb],
[UsedMb],
[TotalMB]
FROM
(SELECT
ROW_NUMBER() OVER(PARTITION BY N ORDER BY V DESC) as RowNum,
N,
V
FROM
OPENXML (@iDoc,'/ADKMessage/c/v',8)
WITH (
N varchar(30) '../@n',
v varchar(30) '.') Playback
WHERE
Playback.N IN ('name','freemb','usedmb','totalmb')) PDB
PIVOT (MAX(v) FOR N IN (name,freemb,usedmb,totalmb)) AS PDBT
Any help is appreciated!
Thank you,
Matt
March 24, 2010 at 4:09 pm
Hi Matt,
I'd use XQuery instead of OPENXML in combination with PIVOT and UNPIVOT.
Something like the following:
DECLARE @xml XML
SET @xml='<ADKMessage>
<c n="totalmb" tn="3" t="1269017107034">
<v>139390.0</v>
<v>571133.0</v>
</c>
<c n="name" tn="1" t="1269017107034" kc="true">
<v>C:</v>
<v>D:</v>
</c>
<c n="freemb" tn="3" t="1269017107034">
<v>57649.14453125</v>
<v>516178.40625</v>
</c>
<c n="usedmb" tn="3" t="1269017107034">
<v>81740.85546875</v>
<v>54954.59375</v>
</c>
</ADKMessage>'
; WITH cte AS
(
SELECT
v.value('@n[1]','varchar(30)') AS item,
v.value('v[1]','varchar(30)') v1,
v.value('v[2]','varchar(30)') v2
FROM @xml.nodes('ADKMessage') T(c)
CROSS APPLY t.c.nodes('c') U(v)
WHERE v.value('@n[1]','varchar(30)') IN ('name','freemb','usedmb','totalmb')
)
,
cte2 AS
(
SELECT item, Drive, val
FROM
(SELECT item,v1,v2
FROM cte) p
UNPIVOT
(val FOR Drive IN
(v1,v2)
)AS unpvt
)
SELECT [name], [totalmb], [freemb],[usedmb]
FROM
(SELECT item,Drive,val
FROM cte2) p
PIVOT
(
MAX (val)
FOR item IN
( [name],[totalmb], [freemb],[usedmb] )
) AS pvt
/*
nametotalmbfreembusedmb
C:139390.057649.1445312581740.85546875
D:571133.0516178.4062554954.59375
*/
Side note: it is assumed that the "v" elements are ordered consistant within each "c" node. Otherwise the results will be "a little bit messy"... 😉
March 25, 2010 at 10:18 am
Lutz,
Thanks for the quick suggestion using CTEs! One follow-up: is there a way to pre-determine the number of V nodes under each C node so define the correct number of V values for each of the two CTEs? I was thinking you can have the position # for each of the V nodes as one of he columns in the first CTE. You are correct that the order of he V values is consistent so that disk 1's values are always the first V node, Disk 2 has the second V node, etc.
Thanks again,
Matt
March 25, 2010 at 11:38 am
Hi Matt,
I googled and found the following link: http://beyondrelational.com/blogs/jacob/archive/2008/08/21/xquery-lab-23-retrieving-values-and-position-of-elements.aspx
I applied it to your scenario resulting in the following code.
But when comparing execution plans for both solutions this new version seems to take a lot longer...
You might want to use the original version expanding it to cover a few more drives. Might be more efficient.
; WITH cte AS
(
SELECT
p.number AS Position,
c.value('@n[1]','VARCHAR(10)') AS Item,
x.value('.','VARCHAR(10)') AS Val
FROM
master..spt_values p
CROSS APPLY @xml.nodes('/ADKMessage/c') T(c)
CROSS APPLY c.nodes('v[position()=sql:column("number")]') n(x)
WHERE p.type = 'p'
)
SELECT [name], [totalmb], [freemb],[usedmb]
FROM
( SELECT Position,item,val
FROM cte) p
PIVOT
(
MAX (val)
FOR item IN
( [name],[totalmb], [freemb],[usedmb] )
) AS pvt
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply