SELECT data from XML Column

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.

- Check XPATh queries in BOL

- e.g.
DECLARE @InvoiceNo int,
@XML XML,
@CompanyName VARCHAR(100),
@tva VARCHAR(100)
SET @XML=CONVERT(XML,@message_body)
SELECT @companyName=@XML.value(' declare namespace PD="http://wonderland.world/Broker/OpenBooking";
(/PD:BeginBooking/PD:travelAgency)[1]','VARCHAR(100)'),
@tva=@XML.value(' declare namespace PD="http://wonderland.world/Broker/OpenBooking";
(/PD:BeginBooking/PD:tva)[1]','VARCHAR(100)')

I thought XML was supposed to make life a lot easier?[<img src='/community/emoticons/emotion-2.gif' alt='' />][<img src='/community/emoticons/emotion-5.gif' alt='' />]

your example is difficult to understand <img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br />The table I am using is called ReservationDetails and the column is called HistoryData...<br /><br />Can you show me an example ?<br /><br />

lol Adriaan,<br /><br />I think that the guy who build the DB is trying to make MY life harder through XML <img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br />

Check the following...
SQL Server 2005 Books Online topic "XQuery Against the xml Data Type "

http://www.developer.com/db/article.php/3565996
http://msdn2.microsoft.com/en-us/library/ms345115.aspx
http://blogs.msdn.com/denisruc/archive/2007/01/11/mixed-content-part-1-of-many.aspx


MohammedU.
Moderator
SQL-Server-Performance.com

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