I am trying to combine two select statements into one and I am not geting any luck with it. Can someone help out?
SELECT statement1
SELECT
XMLELEMENT( NAME “household”,
XMLATTRIBUTES( h.record_id, h.system_id, h.odbid, h.date_updated, h.date_added),
XMLELEMENT( NAME “name”,h.name),
XMLELEMENT( NAME “hsetype”,h.hsetype)
)
FROM ucd.dbo.tblrawhseholds h
SELECT statement2
SELECT
XMLELEMENT( NAME “member”,
XMLATTRIBUTES (m.record_id, m.date_added, m.date_updated),
XMLELEMENT( NAME “client_id”,m.client_id),
XMLELEMENT( NAME “date_entered”,m.date_entered),
XMLELEMENT( NAME “date_ended”,m.date_ended),
XMLELEMENT( NAME “head_of_household”, m.head_of_household),
XMLELEMENT( NAME “relationship”, m.relationship
)
)
FROM ucd.dbo.tblrawhsemembers m
INNER JOIN ucd.dbo.tblrawhseholds h
ON h.family_id = m.family_id
What I am trying to get is an XML document where a household will have the xml attributes record id, date_updated etc and also will have xml elements name, type and members. the element members also have some attributes and elements that I need to include in the document. I have no idea how to tweak the statement without getting any error. please help.
