SQL Server Performance

XML QUERY

Discussion in 'ALL SQL SERVER QUESTIONS' started by cbabino71, Feb 10, 2012.

  1. cbabino71 New Member

  2. Jon M Member

    How about using a UNION?

    select
    col.value('(ADDRESS/PHONES/TYPE)[1]', 'VARCHAR(40)' ) AS TYPEOFPHONE,
    col.value('(ADDRESS/PHONES/NUMBER)[1]', 'VARCHAR(40)' ) AS NUMBER
    FROM @xMLVAR.nodes('/RECORDS/RECORD') AS Feed(Col)
    where col.value('(CUSTOMERINFO/ID)[1]', 'tinyint') = 1
    UNION
    select
    col.value('(ADDRESS/PHONES/TYPE)[2]', 'VARCHAR(40)' ) AS TYPEOFPHONE,
    col.value('(ADDRESS/PHONES/NUMBER)[2]', 'VARCHAR(40)' ) AS NUMBER
    FROM @xMLVAR.nodes('/RECORDS/RECORD') AS Feed(Col)
    where col.value('(CUSTOMERINFO/ID)[1]', 'tinyint') = 1
  3. cbabino71 New Member

    Thank you Jon M , I appricate your quick response.
    I am going to use this query on a much larger xml where some customers may have 1, 2 , 3 or more phones. Any way to return results for all the typeofphone and number for <PHONES> section
    thx
    conan
  4. FrankKalis Moderator

    Is it just me, or where has to original question gone to which Jon replied?

Share This Page