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
Microsoft SQL Server 2005

Avatar of undefined
Last Comment
cyberkiwi

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
cpertuz

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
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

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck