InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 20:08:44 GMT20http://www.sqlservercentral.com/Forums/Topic1398277-391-1.aspx[quote][b]jason123 (12/21/2012)[/b][hr]Hi Friend, thank you so much it is very helpful I can solve my current concern.[/quote]glad to help..PedroFri, 21 Dec 2012 08:56:56 GMTPiManéhttp://www.sqlservercentral.com/Forums/Topic1398277-391-1.aspxHi Friend, thank you so much it is very helpful I can solve my current concern.Fri, 21 Dec 2012 08:53:17 GMTjason123http://www.sqlservercentral.com/Forums/Topic1398277-391-1.aspxtry this (I think this is what you want..)[code="sql"]declare @x xml = '<row id="LD122487292000" xml:space="preserve"><c1>USD</c1><c2>20120904</c2><c2 m="2">20120904</c2><c2 m="3">20121004</c2><c2 m="4">20121102</c2><c3>20120904</c3><c3 m="2">20120904</c3><c3 m="3">20121004</c3><c3 m="4">20121102</c3><c4>-250</c4><c4 m="2">150</c4><c4 m="3">95.18</c4><c4 m="4">-145.18</c4><c5 m="4" /><c6>-250</c6><c6 m="2">-100</c6><c6 m="3">-4.82</c6><c6 m="4">-150</c6><c7 m="4" /><c8 m="4" /><c11>-12.47</c11><c13>0</c13><c15>0</c15><c51>20120904</c51><c52>20140904</c52><c53>-132</c53><c57>-250</c57><c57 m="2">-250</c57><c57 m="3">-250</c57><c58>-4.95</c58><c58 m="2">-5.5</c58><c58 m="3">-2.02</c58><c60>26.4</c60><c60 m="2">26.4</c60><c60 m="3">26.4</c60><c61 m="3" /><c62>20120930</c62><c62 m="2">20121031</c62><c62 m="3">20121111</c62><c63>-4.9499999999998</c63><c63 m="2">-5.4999999999996</c63><c63 m="3">-2.0166666666665</c63><c68>0</c68><c80>20120904</c80><c162>0</c162><c170>20120904</c170><c172>4058.5</c172></row>'declare @m INTselect @m = MAX(ISNULl(t.c.value('@m', 'int'), 0)) from @x.nodes('/row/c62') t(c)Select t.c.value('./@id', 'nvarchar(100)'), t.c.value('(c1/text())[1]', 'NVARCHAR(10)'), t.c.value('(c58[@m=sql:variable("@m")]/text())[1]', 'nvarchar(10)'), t.c.value('(c62[@m=sql:variable("@m")]/text())[1]', 'nvarchar(10)') FROM @x.nodes('/row') t(c)[/code]Wed, 19 Dec 2012 08:42:16 GMTPiManéhttp://www.sqlservercentral.com/Forums/Topic1398277-391-1.aspxThank for your help, I could read it in fixed m , but i am researching how could I know max of m value because I need to take values from the last two nodes(c57 & c62)?m is changed from record to record, sometimes m=3, value of m I need to read is m=3 for first record and m=2 for second record. When m=2, it should be m=2 for first record and m=1 for second recordThankWed, 19 Dec 2012 06:08:39 GMTjason123http://www.sqlservercentral.com/Forums/Topic1398277-391-1.aspxto get the data from m=2 just do[code="sql"]Select t.c.value('./@id', 'nvarchar(100)'), t.c.value('(c1/text())[1]', 'NVARCHAR(10)'), t.c.value('(c58[@m="2"]/text())[1]', 'nvarchar(10)'), t.c.value('(c62[@m="2"]/text())[1]', 'nvarchar(10)') FROM @x.nodes('/row') t(c)[/code]instead of having "fixed" m you can declare a variable and use it.Wed, 19 Dec 2012 04:51:25 GMTPiManéhttp://www.sqlservercentral.com/Forums/Topic1398277-391-1.aspxHi Everyone,Please see xml format<row id="LD122487292000" xml:space="preserve"> <c1>USD</c1> <c2>20120904</c2> <c2 m="2">20120904</c2> <c2 m="3">20121004</c2> <c2 m="4">20121102</c2> <c3>20120904</c3> <c3 m="2">20120904</c3> <c3 m="3">20121004</c3> <c3 m="4">20121102</c3> <c4>-250</c4> <c4 m="2">150</c4> <c4 m="3">95.18</c4> <c4 m="4">-145.18</c4> <c5 m="4" /> <c6>-250</c6> <c6 m="2">-100</c6> <c6 m="3">-4.82</c6> <c6 m="4">-150</c6> <c7 m="4" /> <c8 m="4" /> <c11>-12.47</c11> <c13>0</c13> <c15>0</c15> <c51>20120904</c51> <c52>20140904</c52> <c53>-132</c53> <c57>-250</c57> <c57 m="2">-250</c57> <c57 m="3">-250</c57> <c58>-4.95</c58> <c58 m="2">-5.5</c58> <c58 m="3">-2.02</c58> <c60>26.4</c60> <c60 m="2">26.4</c60> <c60 m="3">26.4</c60> <c61 m="3" /> <c62>20120930</c62> <c62 m="2">20121031</c62> <c62 m="3">20121111</c62> <c63>-4.9499999999998</c63> <c63 m="2">-5.4999999999996</c63> <c63 m="3">-2.0166666666665</c63> <c68>0</c68> <c80>20120904</c80> <c162>0</c162> <c170>20120904</c170> <c172>4058.5</c172></row>You can see nodes c58 and c62, they have attribute m=2,m=3, m=4 and so on dependent on data input. I want to take the last two value of each node, for exampleif m=3,I need to read value in this formatID Currency C58 C62LD122487292000 USD -5.5 20121031LD122487292000 USD -2.02 20121111We could say max of m=3 and max of m-1=2Thanks and Best regards;Wed, 19 Dec 2012 03:51:50 GMTjason123