locked
Join with xml datatypee column in sql RRS feed

  • Question

  • User-1313211662 posted

    I have two table Table1 contains xml type column with value like

    <student>

    <row id="10"/>

    </student>

    and Table to contains id column

    I wanted to join these tables by id without cross apply 

    is any solution to direct join on xml column

    Tuesday, October 25, 2016 6:34 AM

All replies

  • User-2057865890 posted

    Hi Sangad,

    declare @friends table (id int, name varchar(50))
    insert @friends (id, name)
              select  2, 'Locke Lamorra'
    union all select  6, 'Calo Sanzo'
    union all select 10, 'Galdo Sanzo'
    union all select 14, 'Jean Tannen'
    
    declare @xml xml
    set @xml = '<student><row id="10"/></student>'
    
    select  f.name
    from    @xml.nodes('/student/row') as table_alias(column_alias)
    join    @friends f
    on      table_alias.column_alias.value('(/student/row/@id)[1]', 'int') = f.id
    

    Best Regards,

    Chris

    Tuesday, October 25, 2016 8:57 AM
  • User-1313211662 posted

    Thanks Chris,

    It works for me

    Tuesday, October 25, 2016 9:59 AM