Some XQuery/SQL Server 2005 help please

C

CK

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
 
C

CK

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>
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,904
Latest member
HealthyVisionsCBDPrice

Latest Threads

Top