SQL Server Performance

SELECT data from XML Column

Discussion in 'SQL Server 2005 General Developer Questions' started by TedManowar, Mar 16, 2007.

  1. TedManowar New Member

    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.
  2. alzdba Member

    - 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)')
  3. Adriaan New Member

    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=';)' />]
  4. TedManowar New Member

    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 />
  5. TedManowar New Member

    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 />
  6. MohammedU New Member

  7. TedManowar New Member

    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

Share This Page