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
Table2 are connected via the
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.
<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