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


Read data from XML


Read data from XML

Author
Message
jason123
jason123
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 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;
PiMané
PiMané
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1484 Visits: 1347
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...
jason123
jason123
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 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
PiMané
PiMané
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1484 Visits: 1347
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...
jason123
jason123
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 67
Hi Friend, thank you so much it is very helpful I can solve my current concern.
PiMané
PiMané
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1484 Visits: 1347
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...
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