Hierchical data for xml

Hi
I am trying to build a query that will give me hierarchical data but am
having little success.
How can I change the query below to give me xml data in a hierchical format,
nesting multiple customer addresses under one customer id?
Thanks for your asistance
Regards
Habib
---
use adventureworks
select c.CustomerID, co.FirstName, co.LastName,
a.AddressID, pa.AddressLine1, pa.City, pa.PostalCode
from Sales.Customer c
join Person.Contact co
on c.CustomerID = co.ContactID
join Sales.CustomerAddress a
on c.CustomerID = a.CustomerID
join Person.Address pa
on a.AddressID = pa.AddressID
where c.CustomerID in( 254, 11532)
for xml pathUse a nested query
select c.CustomerID,
(select co.FirstName,
co.LastName,
a.AddressID,
pa.AddressLine1,
pa.City,
pa.PostalCode
from Person.Contact co
inner join Sales.CustomerAddress a on c.CustomerID =
a.CustomerID
inner join Person.Address pa on a.AddressID = pa.AddressID
where c.CustomerID = co.ContactID
for xml path,type)
from Sales.Customer c
where c.CustomerID in( 254, 11532)
for xml path|||Mark,
That was very helpful. Thanks.
The key was to use the Type directive in the nested query. I missed that
when reading BOL.
Regards
Habib
<markc600@.hotmail.com> wrote in message
news:1148646040.698060.201590@.i40g2000cwc.googlegroups.com...
> Use a nested query
> select c.CustomerID,
> (select co.FirstName,
> co.LastName,
> a.AddressID,
> pa.AddressLine1,
> pa.City,
> pa.PostalCode
> from Person.Contact co
> inner join Sales.CustomerAddress a on c.CustomerID =
> a.CustomerID
> inner join Person.Address pa on a.AddressID = pa.AddressID
> where c.CustomerID = co.ContactID
> for xml path,type)
> from Sales.Customer c
> where c.CustomerID in( 254, 11532)
> for xml path
>|||Again thanks for your help.
I have a nice little query as below:
How can I wrap this with two additional tags
<request>
<addOrUpdate>
<Customers>
..
</Customers>
</addOrUpdate>
</request>
--
select c.CustomerID, co.FirstName, co.LastName,
(select a.AddressID,
pa.AddressLine1,
pa.City,
pa.PostalCode
from Sales.CustomerAddress a
inner join Person.Address pa on a.AddressID = pa.AddressID
and c.CustomerID = a.CustomerID
for xml path ('address'),type)
from Sales.Customer c
join Person.Contact co
on c.CustomerID = co.ContactID
where c.CustomerID in( 254, 11532)
for xml path ('Customers')
www.DynExtra.com
A resource for the Microsoft Dynamics Community
Featuring FAQs, File Exchange and more
Current member count: 21
---
Share your knowledge. Add your favorite questions and answers
Help add questions to this site! We want Your input.
<markc600@.hotmail.com> wrote in message
news:1148646040.698060.201590@.i40g2000cwc.googlegroups.com...
> Use a nested query
> select c.CustomerID,
> (select co.FirstName,
> co.LastName,
> a.AddressID,
> pa.AddressLine1,
> pa.City,
> pa.PostalCode
> from Person.Contact co
> inner join Sales.CustomerAddress a on c.CustomerID =
> a.CustomerID
> inner join Person.Address pa on a.AddressID = pa.AddressID
> where c.CustomerID = co.ContactID
> for xml path,type)
> from Sales.Customer c
> where c.CustomerID in( 254, 11532)
> for xml path
>|||This should work
select (
select c.CustomerID, co.FirstName, co.LastName,
(select a.AddressID,
pa.AddressLine1,
pa.City,
pa.PostalCode
from Sales.CustomerAddress a
inner join Person.Address pa on a.AddressID = pa.AddressID
and c.CustomerID = a.CustomerID
for xml path ('address'),type)
from Sales.Customer c
join Person.Contact co
on c.CustomerID = co.ContactID
where c.CustomerID in( 254, 11532)
for xml path ('Customers') ,root('addOrUpdate'),type)
for xml path ('request')|||
Mark,
I discovered something similar when I was tinkering with the query.
select ( select ...
for XML PATH ('Customer'),type) as [addOrUpdate]
for xml path ('request'),type
But try as i might, I cant seem to get end tag to appear after each
</Customers>
I expect the difference is that the record is committed after each
</addOrUpdate>, so without it wrapping each record, the entire XML file is
either committed or entirely rolled back.
One might argue the relative merits of either approach but the specification
requires the end tags after each record.
How can I get that in?
Thanks for the assistance so far. It has been very helpful.
Regards
Habib
<markc600@.hotmail.com> wrote in message
news:1148715862.851001.69320@.j33g2000cwa.googlegroups.com...
> This should work
> select (
> select c.CustomerID, co.FirstName, co.LastName,
> (select a.AddressID,
> pa.AddressLine1,
> pa.City,
> pa.PostalCode
> from Sales.CustomerAddress a
> inner join Person.Address pa on a.AddressID = pa.AddressID
> and c.CustomerID = a.CustomerID
> for xml path ('address'),type)
> from Sales.Customer c
> join Person.Contact co
> on c.CustomerID = co.ContactID
> where c.CustomerID in( 254, 11532)
> for xml path ('Customers') ,root('addOrUpdate'),type)
> for xml path ('request')
>|||Is this what you're after?
select
(select c.CustomerID, co.FirstName, co.LastName,
(select a.AddressID,
pa.AddressLine1,
pa.City,
pa.PostalCode
from Sales.CustomerAddress a
inner join Person.Address pa on a.AddressID = pa.AddressID
and c.CustomerID = a.CustomerID
for xml path ('address'),type)
for xml path ('Customers'),type)
from Sales.Customer c
join Person.Contact co
on c.CustomerID = co.ContactID
where c.CustomerID in( 254, 11532)
for xml path ('addOrUpdate') ,root('request'),type|||Mark,
No, I should have been clearer with what I meant
I have pasted what I want below.
I have tried tinkering with the SQL statement but it seems impossible to me.
Thanks again and regards
Habib
<request>
<addOrUpdate>
<Customers>
<CustomerID>254</CustomerID>
<FirstName>Helen</FirstName>
<LastName>Dennis</LastName>
<address>
<AddressID>185</AddressID>
<AddressLine1>2681 Eagle Peak</AddressLine1>
<City>Bellevue</City>
<PostalCode>98004</PostalCode>
</address>
<address>
<AddressID>861</AddressID>
<AddressLine1>25915 140th Ave Ne</AddressLine1>
<City>Bellevue</City>
<PostalCode>98004</PostalCode>
</address>
</Customers>
</addOrUpdate>
</request>
<request>
<addOrUpdate>
<Customers>
<CustomerID>11532</CustomerID>
<FirstName>Madison</FirstName>
<LastName>White</LastName>
<address>
<AddressID>201</AddressID>
<AddressLine1>6202 Seeno St.</AddressLine1>
<City>Sammamish</City>
<PostalCode>98074</PostalCode>
</address>
<address>
<AddressID>20692</AddressID>
<AddressLine1>6437 Brookview Dr.</AddressLine1>
<City>Redmond</City>
<PostalCode>98052</PostalCode>
</address>
</Customers>
</addOrUpdate>
</request>
<markc600@.hotmail.com> wrote in message
news:1148762909.570472.310590@.38g2000cwa.googlegroups.com...
> Is this what you're after?
>
> select
> (select c.CustomerID, co.FirstName, co.LastName,
> (select a.AddressID,
> pa.AddressLine1,
> pa.City,
> pa.PostalCode
> from Sales.CustomerAddress a
> inner join Person.Address pa on a.AddressID = pa.AddressID
> and c.CustomerID = a.CustomerID
> for xml path ('address'),type)
> for xml path ('Customers'),type)
> from Sales.Customer c
> join Person.Contact co
> on c.CustomerID = co.ContactID
> where c.CustomerID in( 254, 11532)
> for xml path ('addOrUpdate') ,root('request'),type
>|||I believe this will work
select
(select
(select c.CustomerID, co.FirstName, co.LastName,
(select a.AddressID,
pa.AddressLine1,
pa.City,
pa.PostalCode
from Sales.CustomerAddress a
inner join Person.Address pa on a.AddressID = pa.AddressID
and c.CustomerID = a.CustomerID
for xml path ('address'),type)
for xml path ('Customers'),type)
for xml path ('addOrUpdate'),type)
from Sales.Customer c
join Person.Contact co
on c.CustomerID = co.ContactID
where c.CustomerID in( 254, 11532)
for xml path ('request')|||YeeHaw! That worked.
Thank you very much for your assistance. You are a genius.
I tried various other combinations such as adOrUpdate/Request and
@.addorUpdate but did not know think of using the FOR XML PATH before the
final from clause. Of course now it makes sense.
Regards
Habib
<markc600@.hotmail.com> wrote in message
news:1148799893.889247.42490@.38g2000cwa.googlegroups.com...
>I believe this will work
> select
> (select
> (select c.CustomerID, co.FirstName, co.LastName,
> (select a.AddressID,
> pa.AddressLine1,
> pa.City,
> pa.PostalCode
> from Sales.CustomerAddress a
> inner join Person.Address pa on a.AddressID = pa.AddressID
> and c.CustomerID = a.CustomerID
> for xml path ('address'),type)
> for xml path ('Customers'),type)
> for xml path ('addOrUpdate'),type)
> from Sales.Customer c
> join Person.Contact co
> on c.CustomerID = co.ContactID
> where c.CustomerID in( 254, 11532)
> for xml path ('request')
>