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?
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?