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

Read data from XML Expand / Collapse
Author
Message
Posted Wednesday, December 19, 2012 3:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 1, 2014 12:44 AM
Points: 15, Visits: 67
Hi 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 example

if m=3,
I need to read value in this format

ID Currency C58 C62
LD122487292000 USD -5.5 20121031
LD122487292000 USD -2.02 20121111

We could say max of m=3 and max of m-1=2

Thanks and Best regards;
Post #1398277
Posted Wednesday, December 19, 2012 4:51 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 3:43 AM
Points: 515, Visits: 1,140
to get the data from m=2 just do
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)

instead of having "fixed" m you can declare a variable and use it.




If you need to work better, try working less...
Post #1398313
Posted Wednesday, December 19, 2012 6:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 1, 2014 12:44 AM
Points: 15, Visits: 67
Thank 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 record

Thank
Post #1398345
Posted Wednesday, December 19, 2012 8:42 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 3:43 AM
Points: 515, Visits: 1,140
try this (I think this is what you want..)
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 INT
select @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)





If you need to work better, try working less...
Post #1398463
Posted Friday, December 21, 2012 8:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 1, 2014 12:44 AM
Points: 15, Visits: 67
Hi Friend, thank you so much it is very helpful I can solve my current concern.
Post #1399462
Posted Friday, December 21, 2012 8:56 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 3:43 AM
Points: 515, Visits: 1,140
jason123 (12/21/2012)
Hi Friend, thank you so much it is very helpful I can solve my current concern.

glad to help..

Pedro




If you need to work better, try working less...
Post #1399465
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse