SQL Server Performance

xml datatyope field

Discussion in 'SQL Server 2005 General Developer Questions' started by arkiboys, Nov 25, 2008.

  1. arkiboys Member


    How is it possible to alter this query so that for each value inside the xml field, I get one row.

    i.e
    declare @myTable table(ProfileID int, Characteristic xml)

    insert @myTable

    select 1, '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>Programmes</value></stringList>'
    UNION ALL
    select 2, '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>Programmes</value></stringList>'
    UNION ALL
    select 3, '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>VOn</value></stringList>'
    UNION ALL
    select 4, '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0">
    <value>Securities</value><value>VOn</value><value>Notes</value>
    </stringList>'

    select
    convert(nvarchar(max), Characteristic.query('data(/)')) Characteristic,
    convert(nvarchar(max), SecurityType.query('data(/)')) SecurityType
    from
    myTable


    Retrieved RESULTS
    ProfileID Characteristic
    1 Programmes
    2 Programmes
    3 VOn
    4 SecuritiesVOnNotes
    ...
    ...

    Correct RESULTS shouold be
    ProfileID Characteristic
    1 Programmes
    2 Programmes
    3 VOn
    4 Securities
    4 VOn
    4 Notes
    ...
    ...

    Thank you

Share This Page