SELECT data from XML Column | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

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 @[email protected](‘ declare namespace PD="http://wonderland.world/Broker/OpenBooking";
(/PD:BeginBooking/PD:travelAgency)[1]’,’VARCHAR(100)’),
@[email protected](‘ 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=’:D‘ />][<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
]]>