perrier000


I'm using union to build related xml structure with FOR XML EXPLICIT. To do so, i user 'ORDER BY key field' to relate multiple tables in multiple union statements. That's just to link related children data to the parent data. However, I also need to sort the entire xml by non-key fields. For example, I use id to relate data but I need to sort by name. In my code below, I'm relating data by [Listing!3!Id!element], [AdCampaign!11!Id!element], [AttributeType!15!Id!element]. How do I show my listings order by listing name in this case




Re: how to order by non-key fields in related union tables (For XML EXPLICIT)?

saurabhdotnet


The order destroys the parent - child relationship. The order is decided by the first data element in the XML Schema. Here Listing!Id is the first one so you can't see the sorting effect for listing_name.

Replace the First Data Element in the XML Schema with Listing_Name and XML will be sorted automatically by Listing_Name.

Remove the order by clause from the FOR XML query. And, for ordering by non-key fields, use Xquery to order the elements in the resultant XML resultset after this query.

Sample Code for the same:

Code Snippet

select [Xml]=@per.query('

<ListingServiceResponse xmlns="http://aaa.bbb.Schemas">

{

for $r in /ListingServiceResponse/Listings

order by (($r/Listing/Name)[1])

return $r

}

</ListingServiceResponse>

')

This will sort your resultset on Listing Name.

Hope this helps Smile

Thanks

Saurabh Verma

MVP | MCDBA | MCT.NET | MCSD.NET






Re: how to order by non-key fields in related union tables (For XML EXPLICIT)?

saurabhdotnet

To add more reasoning to this.

"In the early days of XML, imperative programming (navigation through the XML DOM) was all the rage. The XQuery language in general and XQuery inside the database in particular make it possible for the query engine writers to approach the task of optimizing queries against XML. The chances of success are good because these folks have 20 years or so of practical experience optimizing SQL queries against the relational data model. The SQL Server 2005 implementation of XQuery over the built-in XML data type holds the same promise of a declarative language, with optimization through a query engine. And the query engine that SQL Server 2005 XQuery uses is the one built-in to SQL Server. SQL Server 2005 XQuery uses the relational engine, with XQuery-specific enhancements. As an example, XQuery mandates that the results be returned in document order, even if you don't use "order by" in the query."

- quoted from http://msdn2.microsoft.com/en-us/library/ms345121.aspx

That's the reason - we provide data in whatever order, XQuery will return the results in document order.

Hope this helps

Thanks

Saurabh Verma

MVP | MCDBA | MCT.NET | MCSD.NET






Re: how to order by non-key fields in related union tables (For XML EXPLICIT)?

perrier000

I was looking for the solution not the reason as I already know the reason. I've been working on x-query for a while. Didn't help but thanks for your time.I was looking for the solution not the reason as I already know the reason. I've been working on x-query for a while. Didn't help but thanks for your time.



Re: how to order by non-key fields in related union tables (For XML EXPLICIT)?

saurabhdotnet

I had a detailed look at your structure. Use this xquery, it will work.

Code Snippet

select @per =

(

select * from

(

put your union select here

)

)

select [Xml]=@per.query('

<ListingServiceResponse xmlns="http://aaa.bbb.Schemas">

<Listings>

{

for $r in /ListingServiceResponse/Listings/Listing

order by (($r/Name)[1])

return $r

}

</Listings>

</ListingServiceResponse>

')





Re: how to order by non-key fields in related union tables (For XML EXPLICIT)?

Ashley Dass

Please may I know what is the data type of the @per variable I am also having the same issue.

Thanks





Re: how to order by non-key fields in related union tables (For XML EXPLICIT)?

saurabhdotnet

the datatype is XML;