Post reply

XML Query Nodes - Sequence ID

  • August 8, 2014 at 10:56 am

    #310560

    Hey,

    I have a situation that can be summarized like this :

    DECLARE @XML XML = '<X C="0" I="1"><E D="CODE1" A="0" /><E D="CODE2" A="0.03" /><E D="CODE2" A="0.04" /></X>'

    SELECT

    E.value('@D','varchar(MAX)') AS Code,

    E.value('@A','varchar(MAX)') AS Rate

    FROM @XML.nodes('./X/E') AS T(E)

    The order of appearance is of capital importance as the rates do apply on the previous ones.

    Is there a way to generate a sequential ID based on the order of appearance in the XML string ?

    In this case, I want :

    1 CODE1 0

    2 CODE2 0.03

    3 CODE2 0.04

    I though of using a temp table with Identity column...

    But it's not the best way for my need.

    I have multiple lines each with a XML String.

    The ROW_NUMBER() windowed function needs a ORDER BY clause that I can't provide.

    Any idea ?

    Thanks,

    Vince

  • August 8, 2014 at 11:06 am

    #1736505

    Vince Poirier (8/8/2014)

    The ROW_NUMBER() windowed function needs a ORDER BY clause that I can't provide.

    Does this help your concern?

    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • August 8, 2014 at 11:08 am

    #1736506

    Semms like this blog[/url] describes exactly what you're looking for.


    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]

  • August 8, 2014 at 11:22 am

    #1736509

    Thanks for the fast answers !!

    The temp table would work but I want to prevent DDL as much as I can.

    The ROW_NUMBER() OVER (order by (SELECT NULL)) is awesome !!

    Thanks again !

    Vince

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

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