Could someone provide me some clues or solution to retrieve record sets like below?
Note: I read the msdn documentation but leads me no where but hair loss 🙁
Just for the assumption think i have 2 tables connected via Rid field
Table 1 columns:
Rid, UserName, Hash
Table 2 columns:
Rid, Phone, City, Email
Table1
and Table2
are connected via the Rid
column.
I would like to have a xml output using xml auto, or xml explicit or which ever xml operations you got in SQL Server 2005 Express.
Output expected:
<UserDetails>
<Account>
<UserName>
</UserName>
<Hash>
</Hash>
</Account>
<Personal>
<Phone>
</Phone>
<City>
</City>
</Personal>
</UserDetails>
@matt Please look into the procedure I created below. When you execute the stored procedure with code at start you will know the problem I face
stack_getusers '<Request Type="GetUsers" CRUD="R">
<UserDetails>
<Rid></Rid>
</UserDetails>
</Request>'
CREATE PROCEDURE [dbo].[stack_getusers]
@doc NTEXT
AS
DECLARE @idoc INT
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT
t1.UserName AS "Account/UserName",
t1.Hash AS "Account/Hash",
(
SELECT t2.Phone AS "Personal/Phone",
t2.City AS "Personal/City"
FROM table1 t2
INNER JOIN table2 t3 ON t2.rid = t3.rid WHERE t2.rid = t1.rid AND (xml.Rid = '' OR t1.rid = xml.Rid)
FOR XML PATH('Personals')
)
FROM table1 t1
INNER JOIN table2 t2 ON t1.rid = t2.rid
OPENXML (@idoc,'/Request/Users',2)
WITH (Rid int) as xml
where (xml.Rid = '' OR t1.rid = xml.Rid)
FOR XML PATH ('UserDetails');
EXEC sp_xml_removedocument @idoc
Best Answer
Give this a try:
I have added a second code sample in response to your comment. I have no idea if this will work, or if it is even the best way to accomplish what you want it to. Please let me know.