Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Looping Through XML Fields Expand / Collapse
Author
Message
Posted Monday, December 16, 2013 6:06 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 8, 2014 10:30 PM
Points: 32, Visits: 113
Hi All,

I want to extract the expected value from XMLField

<CountCheck>
<CT U="346" E="123" />
<CT U="345" E="123" />
<CT U="456" E="123" />
</CountCheck

The above value is in XML Field

How can I get the value '456' when the input given is 123
i.e. I want to loop through E in xmlField and find the last E value and display corresponding U value


Thanks in advance.
Post #1523497
Posted Monday, December 16, 2013 7:35 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:45 PM
Points: 1,786, Visits: 5,681
declare @xml xml = '<CountCheck>
<CT U="346" E="123" />
<CT U="345" E="123" />
<CT U="456" E="123" />
</CountCheck>'

declare @e_value int;
set @e_value = 123;

select nd.value('@U','int')
from @xml.nodes('/CountCheck/CT[@E=sql:variable("@e_value")][last()]') x(nd)



MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1523504
    Posted Monday, December 16, 2013 8:07 PM
    SSC Rookie

    SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

    Group: General Forum Members
    Last Login: Thursday, May 8, 2014 10:30 PM
    Points: 32, Visits: 113
    Thanks MM!!..

    This works fine... But only problem is ,xml is just a column in my table.
    Can you please tel me how can a i create a function for this , so that i can write as SELECT UDF (@EValue) FROM tablename WHERE fieldName = @Paramaeter
    Post #1523508
    Posted Tuesday, December 17, 2013 5:52 PM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 4:45 PM
    Points: 1,786, Visits: 5,681
    jeganbenitto.francis (12/16/2013)
    Thanks MM!!..

    This works fine... But only problem is ,xml is just a column in my table.
    Can you please tel me how can a i create a function for this , so that i can write as SELECT UDF (@EValue) FROM tablename WHERE fieldName = @Paramaeter


    Whether it is a column or a variable, the idea is the same, you just need to reference the column instead of the variable.

    I don't know what you are trying to achieve really, where did @Parameter come from and what is "fieldname"?

    This could be one way to create a function for this, but I am not sure what you are trying to do, so this may not be suitable.

    create function DoXmlGrab(@xml xml,@e_value int)
    returns table
    with schemabinding
    as
    return select nd.value('@U','int') as result
    from @xml.nodes('/CountCheck/CT[@E=sql:variable("@e_value")][last()]') x(nd)

    Then use it like this:

    declare @e_value int;
    set @e_value = 123;

    select result
    from myTable
    cross apply DoXmlGrab(xmlColumn,@e_value)



    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1523912
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse