• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 379
  • Last Modified:

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">

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

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

1 Solution
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:

rolwingrAuthor Commented:
Thanks I was able to get it from the link info.

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now