1 <Companies>
2 <Company id="1" name="Company A">
3 <Address>
4 <Street>101 A St.Street>
5 <City>HaysCity>
6 <State>KansasState>
7 <Zip>67601Zip>
8 Address>
9 <Contacts>
10 <Contact id="1" name="Bob Black">
11 <ContactInfo email="bb@companyA.com" phone="(123) 456-7890" />
12 <Login uid="bb" pwd="bb7" />
13 Contact>
14 <Contact id="2" name="Bob Brown">
15 <ContactInfo email="bbn@companyA.com" phone="(123) 456-7891" />
16 <Login uid="bn" pwd="bn1" />
17 Contact>
18 <Contact id="3" name="Bob White">
19 <ContactInfo email="bw@companyA.com" phone="(123) 456-7892" />
20 <Login uid="bw" pwd="bw2" />
21 Contact>
22 Contacts>
23 Company>
24 Companies>
select
Company.Cmp_id "@id",
Company.[Name] "@name",
Company.Address "Address/Street",
Company.City "Address/City",
Company.State "Address/State",
Company.Zip "Address/Zip",
(
select
Contact.Cnt_id "@id",
Contact.[Name] "@name",
Contact.Email "ContactInfo/@email",
Contact.Phone "ContactInfo/@phone",
Contact.UserName "Login/@uid",
Contact.Password "Login/@pwd"
from
Contact
where
Contact.Cmp_id = @CompanyId
order by
Contact.[Name]
for xml path ('Contact'), root('Contacts'), type
)
from
Company
where
Company.Cmp_id = @CompanyId
for xml path ('Company'), root('Companies'), type
No comments:
Post a Comment