Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
 Posted - 2007-04-20 : 08:59:03
|
| Hi,Using the xml below, could you please correct the sql to retrieve the values?Thanks<marketData> <date>19-Apr-2007 17:08:55</date> <Rates> <Rate code="USDCAD"> <values> <value type="BID">1.1276</value> <value type="ASK">1.1277</value> <value type="MID">1.127649997783</value> </values> </Rate> <Rate code="EURUSD"> <values> <value type="BID">1.3607</value> <value type="ASK">1.3608</value> <value type="MID">1.36075</value> </values> </Rate></Rates>insert into tblData ( Bid, Mid, Ask ) select Bid, Mid, Ask, FROM OPENXML (@idoc, '/marketData/Rates/Rate/values/value',2) WITH ( Bid varchar(50), Mid varchar(50), Ask varchar(50) ) |
|
|
cvraghu
Posting Yak Master
187 Posts |
Posted - 2007-04-20 : 13:59:35
|
| The problem is because, you are using element centric mapping (2) and the default mapping in WITH clause (BID,ASK,MID) does not map to element ('value') inside 'values'1. Easier solution would be to modify the source xml as below - <marketData><date>19-Apr-2007 17:08:55</date> <Rates><Rate code="USDCAD"><values><BID>1.1276</BID> <ASK>1.1277</ASK> <MID>1.127649997783</MID> </values></Rate><Rate code="EURUSD"><values><BID>1.3607</BID> <ASK>1.3608</ASK> <MID>1.36075</MID> </values></Rate></Rates>select Bid,Mid,Ask, FROM OPENXML (@idoc, '/marketData/Rates/Rate/values/value',2)WITH ( BID varchar(50),MID varchar(50),ASK varchar(50))Remember to use the same case for elements inside WITH clause. 2. The complex solution would be to retain the same xml and use patterns in WITH clause - something like this. You can research further and fix this - select Bid,Mid,AskFROM OPENXML (@idoc, '/marketData/Rates/Rate/values',2)WITH ( Bid varchar(50) '/value[@type="BID"]/',Mid varchar(50) '/value[@type="MID"]/',Ask varchar(50) '/value[@type="ASK"]/') |
 |
|
|
|
|
|
|
|
openxml