Unnecessary CONVERTs?

  • 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

  • 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)

  • 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/

  • 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