April 6, 2011 at 5:21 am
The following sample code produces exactly the result I want, but I can't help feeling that there must be a better way that doesn't use unnecessary CONVERTs between XML and VARCHAR. The reason they were introduced was because it seems you can't get top-level attributes.
declare @xmlData XML
set @xmlData = '
<registry>
<k name="level">
<k name="Test1">
<v name="one" value="1"/>
<v name="two" value="2"/>
</k>
<k name="Test2">
<v name="one" value="11"/>
<v name="two" value="22"/>
<v name="three" value="33"/>
<v name="four" value="44"/>
</k>
<k name="Test3">
<v name="one" value="111"/>
<v name="three" value="333"/>
</k>
</k>
</registry>
';
;with zz(quay,subx) as (
select
x.k.value('@name','varchar(100)') as quay,
-- This seems like a kludge!
convert(xml,'<k>'+convert(varchar(max),x.k.query('v'))+'</k>') as subx
from
@xmlData.nodes('//registry/k/k') as x(k)
)
select
quay,
subx.v.value('@name','varchar(100)') as nm,
subx.v.value('@value','varchar(100)') as vl
from
zz cross apply zz.subx.nodes('//k/v') as subx(v)
I also feel there must be a way to avoid using the CTE and the CROSS APPLY.
For info, this is the result:
quaynmvl
Test1one1
Test1two2
Test2one11
Test2two22
Test2three33
Test2four44
Test3one111
Test3three333
Thoughts anyone?
Derek
April 6, 2011 at 7:50 am
how about:
SELECTkdetail.value('@name','varchar(20)'),
vdetail.value('@name','varchar(20)'),
vdetail.value('@value','int')
FROM (SELECT @XMLDATA.query('/registry/k/k') as k1) as k
cross apply k.k1.nodes('k') as kd(kdetail)
cross apply kd.kdetail.nodes('v') as v (vdetail)
April 6, 2011 at 8:04 am
Here is one more way:
select
tbl.col.value('(../@name)[1]','varchar(10)'),
tbl.col.value('@name','varchar(10)'),
tbl.col.value('@value', 'int')
from @xmlData.nodes('registry/k/k/v') tbl (col)
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 7, 2011 at 2:58 am
Thanks for the suggestions.
I tried them both and, with my sample data, they both seemed much faster than my version. However, when I tried them on some 'real' data, where there are 7759 rows output and the 'value' field needs to be 'varchar(100)' rather than 'int', the result were different. Here is a typical test result...
me
(7759 row(s) affected)
SQL Server Execution Times:
CPU time = 4415 ms, elapsed time = 4499 ms.
adi
(7759 row(s) affected)
SQL Server Execution Times:
CPU time = 72072 ms, elapsed time = 72954 ms.
!2brite
(7759 row(s) affected)
SQL Server Execution Times:
CPU time = 609 ms, elapsed time = 708 ms.
I assume the '(../@name)' construct must have a high overhead as otherwise I'd have expected Adi's to be faster.
Derek
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply