SELECT data from XML Column - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SELECT data from XML Column

Author

Topic

tedmanowar
Starting Member

31 Posts

Posted - 2007-03-16 : 08:45:11

Hello,

I need to extract some information from XML fields in SQL 2005.

The column contains data of this format.

<row RefDate="2006-12-05T00:00:00" DayName="Tue" BaseRate="100.00" DayPrice="90.00" BaseRateCommissionPercent="20.00" CommissionB2B="10.00" CommissionB2C="10.00" PriceBeforeCommission="152.000000" FinalPrice="167.200000" /><row RefDate="2006-12-06T00:00:00" DayName="Wed" BaseRate="100.00" DayPrice="90.00" BaseRateCommissionPercent="20.00" CommissionB2B="10.00" CommissionB2C="10.00" PriceBeforeCommission="152.000000" FinalPrice="167.200000" />

Is it possible to extract the values of FinalPrice and PriceBeforeCommission ?

Thanks in advance,

TedManowar.

TedManowar

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-16 : 13:13:36

Have a look at the xml value query in this example
http://sqlteam.com/forums/topic.asp?TOPIC_ID=80451

tedmanowar
Starting Member

31 Posts

Posted - 2007-03-19 : 03:48:48

Thanks a lot...

I found exactly what I was looking for.... :)

TedManowar

tedmanowar
Starting Member

31 Posts

Posted - 2007-03-19 : 06:30:09

Hello,

I found something that i could use in those urls that you send me.

The query I made is this:

SELECT H.HotelName, SUM(CONVERT(decimal(10, 2), R.HistoryData.value('(/row/@PriceBeforeCommission)[1]', 'nvarchar(MAX)')))
AS [Price Before Commission], SUM(CONVERT(decimal(10, 2), R.HistoryData.value('(/row/@FinalPrice)[1]', 'nvarchar(MAX)'))) AS [Final Price],
SUM(CONVERT(decimal(10, 2), R.HistoryData.value('(/row/@FinalPrice)[1]', 'nvarchar(MAX)'))) - SUM(CONVERT(decimal(10, 2),
R.HistoryData.value('(/row/@PriceBeforeCommission)[1]', 'nvarchar(MAX)'))) AS Commission
FROM ReservationDetails AS R INNER JOIN
Hotels AS H ON R.FK_HotelID = H.HotelID
GROUP BY H.HotelName

However, there are more than 1 elements (in some rows) in the HistoryData column.

Is there a way for me to loop through them and add those to the sum as well???

Thanks in advance.

TedManowar

TedManowar

tedmanowar
Starting Member

31 Posts

Posted - 2007-03-20 : 08:38:48

For anyone who's stuck like I was, I managed to solve the above problem by the use of Dynamic SQL:

DECLARE @SQL NVARCHAR(4000)

SET @SQL = N'SET @p1 = (SELECT CONVERT(decimal(10,2), HistoryData.value(''(/row/@PriceBeforeCommission)['+ cast(@i as varchar(1000)) + ']'', ''nvarchar(MAX)'')) FROM ReservationDetails
WHERE fk_HotelID=' + cast(@HotelID as varchar(10)) + ' AND fk_ReservationID=' + cast(@ReservationID as varchar(10)) + 'AND ReservationDetailID=' + cast(@ReservationDetailID as varchar(10)) + ')'

EXEC dbo.sp_ExecuteSQL @SQL, N'@p1 DECIMAL(10,2) OUTPUT', @PriceBeforeCommission OUTPUT

The only way to do this is through a Stored Procedure, because the 2 parameters of the .value function MUST be string literals.
If you try to insert a parameter in between the [], it simply WON'T fly

.

TedManowar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources