Post reply

XML Query Help

  • June 24, 2010 at 9:31 am

    #1184573

    Did you get an error about ANSI padding? Not sure why but when I did

    SET ANSI_PADDING ON

    SELECT *

    FROM students

    WHERE experimentchemical.exist('/ATOM/Source/Identity/Location="SYD"') = 1 it seems to work. Location is not a parameter for identity but rather an element under it, so I took out the [ and add a "/" instead.

    More info here: http://sqlxml.org/faqs.aspx?faq=101

  • June 25, 2010 at 2:36 am

    #1184861

    Ray's approach won't give you the requested result since it will always return all rows. Just test it with rows holding an XML string without the Location element or with values other than "SYD" for a given Location element...

    You need to query the text attribute of the Location element.

    Furthermore, you have a typed XML document. Therefore, you need to declare the namespace(s) within your SELECT statement. The tricky part is, that xmlns="urn://valence.aero/schemas/airtransport/ATOM/300" identifies the default namespace, that is used for values without a given namespace prefix, whereas xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" identifies the (unused) namespace prefix "xsi".

    Side note: you can remove the xsi namespace declaration in the query below. It's just included to represent all namespaces defined in the XML document.

    ;

    WITH XMLNAMESPACES

    (

    DEFAULT 'urn://valence.aero/schemas/airtransport/ATOM/300',

    'http://www.w3.org/2001/XMLSchema-instance' AS xsi)

    SELECT s.*

    FROM students s

    WHERE experimentchemical.exist('//Location[text()="SYD"]') = 1


    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 2 posts - 1 through 3 (of 3 total)

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