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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy