• 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">
     <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')

0
rolwingr
Asked:
rolwingr
1 Solution
 
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
 
rolwingrAuthor Commented:
Thanks I was able to get it from the link info.
0

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