SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Looping Through XML Fields


Looping Through XML Fields

Author
Message
jeganbenitto.francis
jeganbenitto.francis
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 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.
mister.magoo
mister.magoo
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10603 Visits: 7891

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


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • jeganbenitto.francis
    jeganbenitto.francis
    SSC-Enthusiastic
    SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

    Group: General Forum Members
    Points: 134 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
    mister.magoo
    mister.magoo
    SSChampion
    SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

    Group: General Forum Members
    Points: 10603 Visits: 7891
    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


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search