Link to home
Create AccountLog in
Avatar of cpertuz
cpertuz

asked on

Simple example of Xquery select doesn't work

Good Afternoon Experts,

I'm using the xml data type for the first time , and I don't see where is the error in the following example:
-------------
declare @myDoc xml
declare @ProdID varchar(20)
set @myDoc = '<processRequest xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" source="RequestBusinessServiceBooking" destination="" propertyKey="10157" name="" GUID="5-5" vendorID="eS">
  <input xmlns="http://www.yourdomain.com">
<Bookings>
      <Booking>
        <BookingDetails key="96363.10149" name="" secondaryName="" abbreviation="" bookingTypeKey="2.10149.2" bookingType="GRP" arriveDate="2010-11-05" departDate="2010-11-05" status="prospect" decisonDate="2010-10-30" cutoffDate="2010-10-16" blockCode="" compRooms="0" commissionPer="0" statusDate="2010-10-16" createDate="2010-10-16" pickupComplete="false">
          <BookedBy key="280547.10149" name="Jessica Alba" initials="JFO" email="jalba@yourdomain.com" />
          <MarketSegment key="2.10149" name="Social" abrv="SOCL" />
        </BookingDetails>
      </Booking>
    </Bookings>
  </input>
</processRequest>'

set @ProdID =  @myDoc.value('(/processRequest/input/Bookings/Booking/BookingDetails/@bookingType)[1]', 'varchar(20)' )
select @ProdID
---------------------

the returned value is NULL

Thanks in advance for your help
ASKER CERTIFIED SOLUTION
Avatar of cpertuz
cpertuz

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of cyberkiwi
Your xml is not suitable at all for lesson #1!!
It includes namespaces which can be tricky
declare @myDoc xml
declare @ProdID varchar(20)
set @myDoc = '<processRequest xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" source="RequestBusinessServiceBooking" destination="" propertyKey="10157" name="" GUID="5-5" vendorID="eS">
  <input xmlns="http://www.yourdomain.com">
<Bookings>
      <Booking>
        <BookingDetails key="96363.10149" name="" secondaryName="" abbreviation="" bookingTypeKey="2.10149.2" bookingType="GRP" arriveDate="2010-11-05" departDate="2010-11-05" status="prospect" decisonDate="2010-10-30" cutoffDate="2010-10-16" blockCode="" compRooms="0" commissionPer="0" statusDate="2010-10-16" createDate="2010-10-16" pickupComplete="false">
          <BookedBy key="280547.10149" name="Jessica Alba" initials="JFO" email="jalba@yourdomain.com" />
          <MarketSegment key="2.10149" name="Social" abrv="SOCL" />
        </BookingDetails>
      </Booking>
    </Bookings>
  </input>
</processRequest>'

;with XMLNAMESPACES ('http://www.yourdomain.com' as i)
select @ProdID =  @myDoc.value('(/processRequest/i:input/i:Bookings/i:Booking/i:BookingDetails/@bookingType)[1]', 'varchar(20)' )
select @ProdID

Open in new window