Xquery Help Sql 2005

[CK]

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?

 

[Joseph Kesselman]

Like XPath and XSLT, XQuery is namespace-sensitive. To query an element
that is in a namespace (as these are, via the xmlns= default-namespace
declaration), you must tell XQuery what namespace it's in.

I believe that XQuery has a mechanism for specifying a default
namespace. (XSLT 1.0 didn't have that, but I believe XSLT 2.0 and XQuery
added it.) The alternative would be for your XQuery to use prefixes, and
for you to provide bindings for those prefixes to the proper namespace URIs.

I'm not sure how any of that would be accessed through SQL 2005, but
hopefully this will get you pointed in the right direction.

 

[CK]

Well here is the namespace.
http://schemas.adventure-works.com/DeliverySchedule

What do I do now?

 

[Joseph Kesselman]

Well here is the namespace.
http://schemas.adventure-works.com/DeliverySchedule

What do I do now?

You read your tool's documentation to understand how it wants to to
specify namespaces to be used in queries. As I said, I haven't used that
particular tool so I can't advise you beyond that generality.

 

[CK]

You have not used Sql server?
Well thank you for your help anyways.

 

[oXygen XML Editor]

You have to declare the namespace and prefix the elements from that
namespace like that:

SELECT
DeliveryList.value('
declare namespace
d="http://schemas.adventure-works.com/DeliverySchedule";
data((/deliveryList/delivery/d:Address)[1])','nvarchar(100)')
DeliveryAddress
FROM Sales.DeliverySchedule

Please make sure that the DeliverySchedule table was properly created:

CREATE TABLE DeliverySchedule (
....
DeliveryList XML(CONTENT DeliverySchema))

and the DeliverySchema was previously registered.

There is a good support for SQL Server 2005 XML features into our
oXygen XML editor (please check
http://www.oxygenxml.com/native_xml_databases.html)
You can edit and add XSD schemas to the SQL Server XSD repository,
define tables and run SQL/SQL/XML and XQuery interrogations using the
SQL Editor.

Stefan Vasile
<oXygen/> XML Editor, Schema Editor and XSLT Editor/Debugger
http://www.oxygenxml.com