June 11, 2009 at 10:18 am
I am new to XML and the majority of what I have found so far to get where I am at is from piecing things together from the net. I have an xml document that provides currency rates for individual currency id's. I am currently able to query the currency id's but have not figured out how to provide the rates in the same recordset. Here is what I have so far:
--XML
--Query
SELECT
CONVERT(VARCHAR(10), DF.ProcDate, 101) AS ProcDate,
symbol.value('.', 'nvarchar(50)') as CurrencyID
FROM
xmh_DatafeedXML DF
CROSS APPLY
xmlcol.nodes('xe-datafeed/currency/csymbol') as t1(symbol)
WHERE
CONVERT(VARCHAR(10), ProcDate, 101) = '06/09/2009'
--Recordset
06/09/2009AUD
06/09/2009CAD
06/09/2009EUR
06/09/2009GBP
06/09/2009INR
06/09/2009JPY
06/09/2009SGD
I have tried using another CROSS APPLY to pull back the rates, but it doesn't seem to work. Any advice on how to make this happen. Any assistance would be greatly appreciated.
June 11, 2009 at 3:19 pm
Hi Jeff,
the reason is that you''re trying to get the "sister elements" of csymbol (meaning the elements at the same hierarchy). Instead of querying the csysmbol element you should use the currency node and select the child elements.
Since you provided excellent sample data it makes it really easy to work with! Thanx a lot for the effort!!
Example (I had to leave out some of your sample code since I don't know the content of columns.):
DECLARE @xml xml
SELECT @xml='AUDAustralia Dollars1.2629079821
CADCanada Dollars1.1141997385
EUREuro0.7179391831
GBPUnited Kingdom Pounds0.6215958868
INRIndia Rupees47.5000000000
JPYJapan Yen98.4717137208
SGDSingapore Dollars1.4548962974'
DECLARE @t TABLE (xmlcol XML)
INSERT INTO @t SELECT @xml
--Query
SELECT
--CONVERT(VARCHAR(10), DF.ProcDate, 101) AS ProcDate,
symbol.value('./csymbol[1]', 'nvarchar(50)') as CurrencyID,
symbol.value('./cname[1]', 'nvarchar(50)') as CurrencyName,
symbol.value('./crate[1]', 'nvarchar(50)') as CurrencyRate
FROM
@t xmltab
CROSS APPLY
xmlcol.nodes('/currency') as t1(symbol)
--WHERE
--CONVERT(VARCHAR(10), ProcDate, 101) = '06/09/2009'
/*
--Recordset
CurrencyIDCurrencyNameCurrencyRate
AUDAustralia Dollars1.2629079821
CADCanada Dollars1.1141997385
EUREuro0.7179391831
GBPUnited Kingdom Pounds0.6215958868
INRIndia Rupees47.5000000000
JPYJapan Yen98.4717137208
SGDSingapore Dollars1.4548962974
*/
June 11, 2009 at 3:34 pm
Lutz,
Thank you so much for the response. It works perfectly. I can now see how it all works for future type projects. Again thank you for the assistance.
June 12, 2009 at 9:11 am
Glad I could help!
If you have any future issues regarding SQL Server get back to this forum.
Considering the way you've provided the sample data I'm sure you'll get a real quick response!
June 13, 2009 at 1:50 pm
[slide in]
Congrats to Ten Centuries, Lutz!
[slide out]
June 13, 2009 at 2:12 pm
June 17, 2009 at 11:32 am
Since this request falls around the same query from above, I figured this would be the best place to start. If I need to start a new thread then please let me know.
I am having to now truncate out the last digit in rate values from my xml to work in system that I am importing it to. I have the syntax to do this, but the issue I am having is that the length of each rate is not always the same. So this means that I have to find the length of each rate and then do my Substring based on that length minus 1. I think that I am close, but continue to get a syntax error. Can anyone help me with what I may be missing. Or am I way off the mark here.
--SQL Code
SELECT
symbol.value('./csymbol[1]', 'nvarchar(50)') AS CurrencyID,
symbol.value('substring(string((./crate)[1]), 1, (symbol.value('string-length(string((./crate)[1]))', 'nvarchar(50)') - 1))', 'nvarchar(50)') AS CurrencyRate
FROM
xmh_DatafeedXML df
CROSS APPLY
xmlcol.nodes('xe-datafeed/currency') AS t1(symbol)
WHERE
CONVERT(VARCHAR(10), ProcDate, 101) = '06/16/2009'
--XML
AUD
Australia Dollars
1.2215090640
CAD
Canada Dollars
1.0836640868
EUR
Euro
0.6998959426
GBP
United Kingdom Pounds
0.6040694391
INR
India Rupees
47.0155000000
JPY
Japan Yen
95.4408895359
SGD
Singapore Dollars
1.4367009700
Any help would be appreciated.
Jeff
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply