Solved

Simple example of Xquery select doesn't work

Posted on 2010-11-11
2
307 Views
Last Modified: 2012-06-22
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
0
Comment
Question by:cpertuz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 

Accepted Solution

by:
cpertuz earned 0 total points
ID: 34116767
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34116798
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

0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question