選擇語句以檢索 xml 輸出，如下所示

有人可以為我提供一些線索或解決方案來檢索如下記錄集嗎？

注意：我閱讀了 msdn 文件，但除了脫髮之外，我什麼也沒有 :(

只是假設認為我有 2 個表通過 Rid 欄位連接

表 1 列：

Rid, UserName, Hash

表 2 列：

Rid, Phone, City, Email

Table1並Table2通過Rid柱連接。

我想使用 xml auto 或 xml explicit 或您在 SQL Server 2005 Express 中獲得的任何 xml 操作來獲得 xml 輸出。

預期輸出：

<UserDetails>
   <Account>
       <UserName>
       </UserName>
       <Hash>
       </Hash>
   </Account>
   <Personal>
       <Phone>
       </Phone>
       <City>
       </City>
   </Personal>
</UserDetails>

@matt 請查看我在下面創建的程序。當您在開始時使用程式碼執行儲存過程時，您將知道我面臨的問題

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

試試這個：

SELECT 
   t1.UserName AS "Account/UserName",
   t1.Hash AS "Account/Hash",
   t2.Phone AS "Personal/Phone",
   t2.City AS "Personal/City"
FROM table1 t1

   INNER JOIN table2 t2
       ON t1.rid = t2.rid

FOR XML PATH ('UserDetails');

我添加了第二個程式碼範例以回應您的評論。我不知道這是否可行，或者它是否是完成您想要的最佳方式。請告訴我。

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

       FOR XML PATH('Personals')
   )
FROM table1 t1

   INNER JOIN table2 t2
       ON t1.rid = t2.rid

FOR XML PATH ('UserDetails');

引用自：https://dba.stackexchange.com/questions/2007