Changing output order for xml explicit

This one shouldn't be complicated or unusual but it's got me stumped!
Here's an example query.
select 1 as Tag,
0 as Parent,
us_id as [Person!1!Person!hide],
'' as [Address!2!Address1!element],
'' as [Address!2!Postcode!element],
us_telephone as [Person!1!Telephone!element]
from tblUser
union
select 2 as Tag,
1 as Parent,
us_id as [Person!1!Person!hide],
us_addr1 as [Address!2!Address1!element],
us_postcode as [Address!2!Postcode!element],
us_telephone as [Person!1!Telephone!element]
from tblUser
order by [Person!1!Person!hide]
for xml explicit
This gives the output :-
<Person>
<Telephone>t1</Telephone>
<Address>
<Address1>a1</Address1>
<Postcode>p1</Postcode>
</Address>
</Person>
The output I require is :-
<Person>
<Address>
<Address1>a1</Address1>
<Postcode>p1</Postcode>
</Address>
<Telephone>t1</Telephone>
</Person>
i.e. put the telephone number after the address.
The application this is for is currently on SQLServer 2000, but can be
migrated to 2005 if that would help me get the data in the required format.
Thanks for any help.Hello maggi,
The order follows the order in your query have you tried the query below.
In addition you are populating the telephone number in the select for the
address element (Tag 2) even through it is populated on the main Person elem
ent
(Tag 1).
This is only required if you need to order by telephone number.
select 1 as Tag,
0 as Parent,
us_id as [Person!1!Person!hide],
us_telephone as [Person!1!Telephone!element]
'' as [Address!2!Address1!element],
'' as [Address!2!Postcode!element],
from tblUser
union
select 2 as Tag,
1 as Parent,
us_id as [Person!1!Person!hide],
us_addr1 as [Address!2!Address1!element],
us_postcode as [Address!2!Postcode!element],
null
from tblUser
order by [Person!1!Person!hide]
for xml explicit
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> This one shouldn't be complicated or unusual but it's got me stumped!
> Here's an example query.
> select 1 as Tag,
> 0 as Parent,
> us_id as [Person!1!Person!hide],
> '' as [Address!2!Address1!element],
> '' as [Address!2!Postcode!element],
> us_telephone as [Person!1!Telephone!element]
> from tblUser
> union
> select 2 as Tag,
> 1 as Parent,
> us_id as [Person!1!Person!hide],
> us_addr1 as [Address!2!Address1!element],
> us_postcode as [Address!2!Postcode!element],
> us_telephone as [Person!1!Telephone!element]
> from tblUser
> order by [Person!1!Person!hide]
> for xml explicit
> This gives the output :-
> <Person>
> <Telephone>t1</Telephone>
> <Address>
> <Address1>a1</Address1>
> <Postcode>p1</Postcode>
> </Address>
> </Person>
> The output I require is :-
> <Person>
> <Address>
> <Address1>a1</Address1>
> <Postcode>p1</Postcode>
> </Address>
> <Telephone>t1</Telephone>
> </Person>
> i.e. put the telephone number after the address.
> The application this is for is currently on SQLServer 2000, but can be
> migrated to 2005 if that would help me get the data in the required
> format.
> Thanks for any help.
>|||In 62959f1a501d68c92bc11929e988@.msnews.microsoft.com, Simon Sabin
<SimonSabin@.noemail.noemail> typed:

> The order follows the order in your query have you tried the query
> below. In addition you are populating the telephone number in the select
> for
> the address element (Tag 2) even through it is populated on the main
> Person element (Tag 1).
> This is only required if you need to order by telephone number.
> select 1 as Tag,
> 0 as Parent,
> us_id as [Person!1!Person!hide],
> us_telephone as [Person!1!Telephone!element]
> '' as [Address!2!Address1!element],
> '' as [Address!2!Postcode!element],
> from tblUser
> union
> select 2 as Tag,
> 1 as Parent,
> us_id as [Person!1!Person!hide],
> us_addr1 as [Address!2!Address1!element],
> us_postcode as [Address!2!Postcode!element],
> null
> from tblUser
> order by [Person!1!Person!hide]
> for xml explicit
Cheers Simon, but that still wouldn't sort the telephone number AFTER the
address details to give me
<Person>
<Address>
<Address1>a1</Address1>
<Postcode>p1</Postcode>
</Address>
<Telephone>t1</Telephone>
</Person>
I've given up on it in SQL Server 2000, and rewritten it using XML AUTO for
2005. I'd still be interested to know if anyone ever figures out a method
for doing it though.|||Try this...
select 1 as Tag,
NULL as Parent,
us_id as [Person!1!Person!hide],
NULL as [Address!2!Address1!element],
NULL as [Address!2!Postcode!element],
us_telephone as [Telephone!3]
from tblUser
union all
select 2 as Tag,
1 as Parent,
us_id as [Person!1!Person!hide],
us_addr1 as [Address!2!Address1!element],
us_postcode as [Address!2!Postcode!element],
us_telephone as [Telephone!3]
from tblUser
union all
select 3 as Tag,
1 as Parent,
us_id as [Person!1!Person!hide],
NULL as [Address!2!Address1!element],
NULL as [Address!2!Postcode!element],
us_telephone as [Telephone!3]
from tblUser
order by [Person!1!Person!hide]
for xml explicit
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--
"maggi" <maggi_v@.gazeta.pl.invalid> wrote in message
news:eshutp$b8e$1@.inews.gazeta.pl...
> In 62959f1a501d68c92bc11929e988@.msnews.microsoft.com, Simon Sabin
> <SimonSabin@.noemail.noemail> typed:
>
> Cheers Simon, but that still wouldn't sort the telephone number AFTER the
> address details to give me
> <Person>
> <Address>
> <Address1>a1</Address1>
> <Postcode>p1</Postcode>
> </Address>
> <Telephone>t1</Telephone>
> </Person>
> I've given up on it in SQL Server 2000, and rewritten it using XML AUTO
> for 2005. I'd still be interested to know if anyone ever figures out a
> method for doing it though.
>|||In uWrpmZEYHHA.992@.TK2MSFTNGP02.phx.gbl, Peter W. DeBetta
<debettap@.hotmail.com> typed:

> Try this...
> select 1 as Tag,
> NULL as Parent,
> us_id as [Person!1!Person!hide],
> NULL as [Address!2!Address1!element],
> NULL as [Address!2!Postcode!element],
> us_telephone as [Telephone!3]
> from tblUser
> union all
> select 2 as Tag,
> 1 as Parent,
> us_id as [Person!1!Person!hide],
> us_addr1 as [Address!2!Address1!element],
> us_postcode as [Address!2!Postcode!element],
> us_telephone as [Telephone!3]
> from tblUser
> union all
> select 3 as Tag,
> 1 as Parent,
> us_id as [Person!1!Person!hide],
> NULL as [Address!2!Address1!element],
> NULL as [Address!2!Postcode!element],
> us_telephone as [Telephone!3]
> from tblUser
> order by [Person!1!Person!hide]
> for xml explicit
That's the one!
Thank you very much, Peter.