Posted by CK on 11/22/06 17:34

I have the following XML in an XML column in a SQL 2005 Database.
<DeliveryList xmlns="http://schemas.adventure-works.com/DeliverySchedule">
<Delivery SalesOrderID="43659">
<CustomerName>Steve Schmidt</CustomerName>
<Address>6126 North Sixth Street, Rockhampton</Address>
</Delivery>
<Delivery SalesOrderID="43660">
<CustomerName>Tony Lopez</CustomerName>
<Address>6445 Cashew Street, Rockhampton</Address>
</Delivery>
</DeliveryList>

I need to query that column using the value method to retreive the address
of the first delivery. I come up with this,
SELECT
DeliveryList.value('data((/DeliveryList/Delivery/Address)[1])','nvarchar(100)')
DeliveryAddress
FROM Sales.DeliverySchedule
but it tells me
XQuery [Sales.DeliverySchedule.DeliveryList.value()]: There is no element
named 'DeliveryList'
I have tried every permutation of the path and I can not seem to get it to
work? Any ideas?

[Back to original message]