sql server (alternate): Some XQuery/SQL Server 2005 help please -- sql server (alternate)

Some XQuery/SQL Server 2005 help please

CK
11/27/2006 7:57:23 PM

sql server (alternate): This query

SELECT DeliveryList.query('declare default element namespace
http://schemas.adventure-works.com/DeliverySchedule;
<DeliveryRoute>
<RouteNo>{ sql:column("D.DeliveryRoute") }</RouteNo>
for a$ in /DeliveryList/Delivery/Address
return
<Address>a$</Address>
</DeliveryRoute>')
as Result
FROM Sales.DeliverySchedule D

Returns this XML

<DeliveryRoute xmlns="http://schemas.adventure-works.com/DeliverySchedule">
<RouteNo>3</RouteNo>
for a$ in /DeliveryList/Delivery/Address
return
<Address>a$</Address></DeliveryRoute>

I need it to return this XML
<DeliveryRoute xmlns="http://schemas.adventure-works.com/DeliverySchedule>

<RouteNo<3>/RouteNo>

<Address>6126 North Sixth Street, Rockhampton</Address>

<Address>6445 Cashew Street, Rockhampton</Address>

</DeiveryRoute>

What am I doing wrong? Any ideas? All help is appreciated.

TIA, ~CK

Re: Some XQuery/SQL Server 2005 help please

CK
11/27/2006 8:03:58 PM

Here is the original XML Doc
<?xml version="1.0" ?>
- <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>

[quoted text, click to view]