Post reply

xquery for elements with specific attributes

  • July 7, 2010 at 2:23 am

    #387285

    declare @xml xml

    set @xml = '<U>

    <N id = "2" type ="good"/>

    <N id = "2" type ="bad"/>

    </U>'

    I know I can get get XML N elements with

    select @xml.query('/U/N')

    But how can I only select those where type = good.

    so I return just <N id = "2" type ="good"/>.

    Thanks for all help

    Jules

    www.sql-library.com[/url]

  • July 7, 2010 at 3:20 am

    #1189495

    select r.query('.')

    from @xml.nodes('/U/N[@id="2" and @ type ="good"]') as x(r)

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • July 7, 2010 at 4:23 am

    #1189509

    If the search criteria is dynamic (usnig a variable) see this blog post

    http://weblogs.sqlteam.com/peterl/archive/2008/09/01/Updated-XML-search-test-case-with-variables.aspx

    N 56°04'39.16"
    E 12°55'05.25"

  • July 7, 2010 at 5:41 am

    #1189534

    I have a very similar solution to Mark's.

    select

    row.value('@id', 'varchar(100)') as id,

    row.value('@type', 'varchar(100)') as type,

    row.query('.')

    from @xml.nodes('/U/N') as xmltable(row)

    where

    row.value('@type', 'varchar(100)') = 'good'

    I hope that helps,

    Eralper
    SQL Server and T-SQL Tutorials and Articles
    Microsoft Certification and Certification Exams

  • July 7, 2010 at 3:20 pm

    #1189893

    I'm not sure whether it's a good idea or not to ask a question using the "sliced salami approach"...

    Here's the related link with the other two questions regarding the very same subject.

    As you probably noticed by now each "slice" might result in a different approach. Wouldn't it be easier to describe the whole picture all at once?

    It would also help us that volunteer answering questions since we won't have to go back and forth with every new information...


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply