opnexml

<marketData>
<date>10-jan-2001</date>
<producedBy>person1</producedBy>
<SpotRates>
<SpotRate code="USDCAD">
<values>
<value type="BID">1.1276</value>
<value type="ASK">1.1277</value>
<value type="MID">1.127649997783</value>
</values>
</SpotRate>
<SpotRate code="EURUSD">
<values>
<value type="BID">1.3607</value>
<value type="ASK">1.3608</value>
<value type="MID">1.36075</value>
</values>
</SpotRate>
<SpotRate code="GBPUSD">
<values>
<value type="BID">2.0025</value>
<value type="ASK">2.0027</value>
<value type="MID">2.0026</value>
</values>
</SpotRate>
</SpotRates>
</marketData>

-
How can I modify the sql below so that I can also insert SpotRate code from the xml data above?

insert into tblSpotRates
(
SpotRateCode
BID,
ASK,
MID

)
select
?
Bid,
Ask,
Mid
FROM OPENXML (@.idoc, '/marketData/SpotRates/SpotRate/values',2)
WITH (
Bid varchar(50) 'value[1]',
Ask varchar(50) 'value[2]',
Mid varchar(50) 'value[3]'
)

use the following script..

Code Snippet

Declare @.XML as varchar(1000);

Declare @.idoc as int;

Select @.Xml='<marketData>

<date>10-jan-2001</date>

<producedBy>person1</producedBy>

<SpotRates>

<SpotRate code="USDCAD">

<values>

<value type="BID">1.1276</value>

<value type="ASK">1.1277</value>

<value type="MID">1.127649997783</value>

</values>

</SpotRate>

<SpotRate code="EURUSD">

<values>

<value type="BID">1.3607</value>

<value type="ASK">1.3608</value>

<value type="MID">1.36075</value>

</values>

</SpotRate>

<SpotRate code="GBPUSD">

<values>

<value type="BID">2.0025</value>

<value type="ASK">2.0027</value>

<value type="MID">2.0026</value>

</values>

</SpotRate>

</SpotRates>

</marketData>'

EXEC sp_xml_preparedocument @.idoc OUTPUT, @.xml

select

Code,

Bid,

Ask,

Mid

FROM OPENXML (@.idoc, '/marketData/SpotRates/SpotRate',1)

WITH (

codevarchar(100),

Bidvarchar(50) 'values/value[1]',

Askvarchar(50) 'values/value[2]',

Midvarchar(50) 'values/value[3]'

)