for xml path: two attributes, two tables, one node

I want to create an sql query that will return an xml result that contains two attributes in a single node that come from two different tables.  

This is what I want the result to look like:
----------
<plan id="1871" garage="left">
     <region>
</plan>

----------
here's what I've tried but it returns two separate results:

SELECT planTitle as '@id' from
dbo.fpDetails
where planId = 1871
select garage as '@garage' from dbo.parcel_plans
for xml path ('region')

rolwingrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dan_masonCommented:
The first thing to do (not essential but makes life easier) would be to compile a view that contains all the information you need. Or a CTE, which is a construct like this:

With MyView AS
(SELECT planTitle, garage
FROM dbo.fpDetails
LEFT JOIN dbo.parcel_plans ON fpDetails.planID=parcel_plans.planID
WHERE planID=1871
)

SELECT planTitle,garage FROM MyView

The XML you posted isn't in the correct format. What should be in the region node?

Assuming you had a view or CTE called MyView (and assuming planTitle is supposed to be the same value as planID) this statement:

SELECT planTitle as '@id', garage as '@garage'
from MyView
where planID= 1871
for xml path ('plan')

Would yield this result:

<plan id="1871" garage="left" />

If  for example you wanted to nest plan within region (not what you have stated, I know) you would use this:

SELECT planTitle as '@id', garage as '@garage'
from MyView
where planID= 1871
for xml path ('plan'), ROOT ('region')

For a good overview of more advanced output that can be achieved using FOR XML PATH, including nested nodes and using more than one table as the input, I recommend this link:

http://www.bizcoder.com/index.php/2010/01/18/for-xml-path-and-cqrs/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rolwingrAuthor Commented:
Thanks I was able to get it from the link info.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.