dewit.john (10/15/2009)

---

I have tackled this problem before. I retrieve the XML data directly from the ECB site, within a web application written in C#. The XML data is given as a string when calling the strored procedure. The XML data is then converted to XML again in the SP.

I have used this approach in the SP:

set @xmlData = @xmlString

-- N.B. Not testing the XML against an XSD !

declare @ECBRates table ( ident int IDENTITY(1,1) NOT NULL

, dt nvarchar(20)

, currency nvarchar(4)

, rate money )

;WITH XMLNAMESPACES (DEFAULT 'http://www.ecb.int/vocabulary/2002-08-01/eurofxref')

insert into @ECBRates (dt, currency, rate)

SELECT

tab.col.value('../@time', 'nvarchar(20)') As Date,

tab.col.value('./@currency', 'nvarchar(4)') As Currency,

tab.col.value('./@rate','float') As Rate

FROM @xmlData.nodes('//Cube[not(*)]') As tab(col)

ORDER BY 2,1 desc

Now I have the data in a table I can process it the normal T-SQL way.

I forgot to mention that I often call stored procedures form C#. Passing massive amounts of data in the form of XML to the database means just 1 call, 1 connection. The .Net overhead is reduced this way, i.e. compared to sifting through the XML and doing multiple inserts (= multiple conections). Also transaction processing within a SP is so much easier than doing it in the C# .Net environment.