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 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply